hi guys,

I have statement that looks like this:

SET outSlip = currDbase.OpenRecordSet ("'SELECT * FROM
STORE
                      WHERE STORE.store =' + varTemp + ")

varTemp is a variable that is being changed in a for loop
above. I am just having problems embedding the variable
into my SQL statement with OpenRecordSet. I am getting
runtime errors. I tried different combinations on the
quotations without any effect.

Any help would be appreciated.

Thanks

Adwait

The single quotes (') you've put around the bulk of the SQL statement are
incorrect.  You may need quotes around the value of varTemp, depending on
whether STORE.store is a text field or a number.  If a number, the statement
would probably look like this:

    SET outSlip = currDbase.OpenRecordSet ( _
        "SELECT * FROM STORE WHERE STORE.store = " & varTemp)

If STORE.store is a text field, it would probably look like this:

    SET outSlip = currDbase.OpenRecordSet ( _
        "SELECT * FROM STORE WHERE STORE.store = " & _
            Chr$(34) & varTemp & Chr$(34))

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(to reply via e-mail, remove NOSPAM from address)

Quotemarks are tricky. The one that's causing your problem appears to
be the opening ' before the SELECT statement.

If Store is a Text field, and varTemp contains "WalMart", you want the
string to end up being

SELECT * FROM STORE WHERE STORE.store = 'WalMart'

But... this will get you in trouble if the store name is Larry's
Thrift Mart, since the apostrophe in Larry's will be taken as a
terminating quote! You would do better to delimit the parameter with "
instead - you can include a " in a string delimited by " by using two
consecutive " marks:

strSQL = "SELECT * FROM STORE WHERE STORE.Store = """ & varTemp & """"
<you can now view strSQL in the Debug window to see if it makes sense>
SET outSlip = currDbase.OpenRecordSet(strSQL)

                  John W. Vinson[MVP]    
    Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Try

SET outSlip = currDbase.OpenRecordSet ("SELECT * FROM
STORE WHERE STORE.store ='" + varTemp + ';")

Slight error there, Tony.

You left out a double quote after varTemp:

SET outSlip = currDbase.OpenRecordSet ("SELECT * FROM
STORE WHERE STORE.store ='" & varTemp & "'")

(You should always use & rather than + to concatenate strings, and the
ending semi-colon isn't really required)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele