Web Development

Ask something
Discussion Locked

ACCESS VBA SELECT

0 helpful
9 replies

by yatst

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have four of these open recordsets in my code. For some reason this one keeps throwing a syntax error and/or a runtime error that Iam missing an operand. Any help would be appreciated I've spent hours trying to debug this.

Set ShpCstcnn = CurrentProject.Connection
AmtRst.Open "Select * from tblStdShipCost Where ShpCstCode = " & ShpCd, ShpCstcnn

9 replies

by sl-campbell

If ShpCstCode is a text or date field then try:

AmtRst.Open "Select * from tblStdShipCost Where ShpCstCode = '" & ShpCd & "'", ShpCstcnn

by itpreneur

AmtRst.Open "Select * from tblStdShipCost Where ShpCstCode = " & ShpCd & ShpCstcnn

just replace comma with &

regards,

melvin

by bob sellman

If you combine the other two answers you get the right result.

Assuming ShpCstCode is a text field, you must surround the value represented by the variables with single quotes ('). So your statement should read:

AmtRst.Open "Select * from tblStdShipCost Where ShpCstCode = '" & ShpCd & ShpCstcnn & "'"

You might also be sure that there are no extraneous leading/trailing spaces in ShpCd and in ShpCstcnn. If that is also a problem, use Trim(ShpCd) and Trim(ShpCstcnn).

by madestroitsolutions

First of all, make sure you are assigning a valid connection object. Second, make sure a value is being passed on ShpCD. Maybe the variable is not receiving any value, and you end up with an incomplete SQL Statement, which would throw a missing operand error. For readibility purposes, you should assign the SQL statement to a variable, and then pass the variable to the OPEN command. It will make your life simpler. If ShpCstCode in the database is text, char, or varchar, depending on the database, then you must put it inside ['] single quotes. If none of this helps, then check the recordset CommandType, maybe its set to other than "Text". As for some of the other answers you received before, ShpCstcnn is a connection object, not another string he wants to add to the SQL Statement. The syntax for OPEN takes a connection as the second parameter, which is why he is putting a comma before it.

Ex:

Set ShpCstcnn = CurrentProject.Connection
Sql1 = "Select * from tblStdShipCost Where ShpCstCode = '" & ShpCd & "'"
AmtRst.Open Sql1, ShpCstcnn

Good Luck!

This discussion is closed to new replies.