Where clause in SQL statement problem

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

re: Where clause in SQL statement problem

On Wed, 9 Feb 2005 00:04:09 -0500, Giganews wrote:
[color=blue]
> 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[/color]

WHERE tblStores.[STORENAME] = '" & [Forms]![frmStoreForm]![STORENAME]
& "'"

Flor clarity, the quotes are like this:
= ' " & [Forms]![frmStoreForm]![STORENAME] & " ' "

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

re: Where clause in SQL statement problem

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:[color=blue]
> 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
>
>[/color]

re: Where clause in SQL statement problem

Giganews wrote:
[color=blue]
>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 "[/color]


You didn't delimit it properly:

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

re: Where clause in SQL statement problem

Thanks for all the help. Problem solved.

Regards,
Mark



"Giganews" <hereinde@yahoo.com> wrote in message
news:WYKdncZ1Y-RPBJTfRVn-pA@comcast.com...[color=blue]
>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
>[/color]