Where clause in SQL statement problem

#1

I have an Access 97 database were I am opening a recordset in a module
referencing a control on a form. For some reason when the code executes I am
getting an error message stating missing operator and I am not sure why. The
code is as follows:

Public Sub Module1()
Dim rst as recordset

Set rst = CurrentDb.OpenRecordset("SELECT * From tblStores WHERE
tblStores.[STORENAME] = " & [Forms]![frmStoreForm]![STORENAME] & " ")

[STORENAME] is a text field which is why I delimited it with "

Am I missing something?

Any help would be appreciated.

Regards,
Mark

#2

I believe it will work if you add these single quotes as shown:

Set rst = CurrentDb.OpenRecordset("SELECT * From tblStores WHERE
tblStores.[STORENAME] = '" & [Forms]![frmStoreForm]![STORENAME] & "'")

Regards,
Mike Parks

Giganews wrote:

Quote:

I have an Access 97 database were I am opening a recordset in a module
referencing a control on a form. For some reason when the code executes I am
getting an error message stating missing operator and I am not sure why. The
code is as follows:

Public Sub Module1()
Dim rst as recordset

Set rst = CurrentDb.OpenRecordset("SELECT * From tblStores WHERE
tblStores.[STORENAME] = " & [Forms]![frmStoreForm]![STORENAME] & " ")

[STORENAME] is a text field which is why I delimited it with "

Am I missing something?

Any help would be appreciated.

Regards,
Mark

#3

Giganews wrote:

Quote:

I have an Access 97 database were I am opening a recordset in a module
referencing a control on a form. For some reason when the code executes I am
getting an error message stating missing operator and I am not sure why. The
code is as follows:

Public Sub Module1()
Dim rst as recordset

Set rst = CurrentDb.OpenRecordset("SELECT * From tblStores WHERE
tblStores.[STORENAME] = " & [Forms]![frmStoreForm]![STORENAME] & " ")

[STORENAME] is a text field which is why I delimited it with "

You didn't delimit it properly:

.. . . ORENAME] = """ & [Forms]![frmStoreForm]![STORENAME] &
"""")
--
Marsh
MVP [MS Access]

#4

Thanks for all the help. Problem solved.

Regards,
Mark

"Giganews" <hereinde (AT) yahoo (DOT) com> wrote

Quote:

I have an Access 97 database were I am opening a recordset in a module
referencing a control on a form. For some reason when the code executes I
am getting an error message stating missing operator and I am not sure why.
The code is as follows:

Public Sub Module1()
Dim rst as recordset

Set rst = CurrentDb.OpenRecordset("SELECT * From tblStores WHERE
tblStores.[STORENAME] = " & [Forms]![frmStoreForm]![STORENAME] & " ")

[STORENAME] is a text field which is why I delimited it with "

Am I missing something?

Any help would be appreciated.

Regards,
Mark

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Where clause in SQL statement problem

comp.database.ms-access comp.database.ms-access

Where clause in SQL statement problem - 02-08-2005 , 11:04 PM

Re: Where clause in SQL statement problem - 02-09-2005 , 12:05 AM

Re: Where clause in SQL statement problem - 02-09-2005 , 12:09 AM

Re: Where clause in SQL statement problem - 02-10-2005 , 10:42 PM