Comma in query expression

[injanib via AccessMonster.com]

I have a txt field where I enter names in this format: "last
name"+comma+space+"first name"
(Brady, Tom)

the after update event runs a DCount procedure. I get the Syntax error (comma)
in query expression.
I believe that it is the format of the textfield, but I don't know how to fix
it. Here is the code line where it occurs.

if DCount("*", "Main", "[Name]=" & me.Recipient) <1 Then

The errors is:

Syntax error (comma) in query expression '[Name]=Brady, Tom'.

 

[John Spencer]

You need to include text delimiters in the DCount statement if you are going
to use a text string.

DCount("*","Main","[Name]= '" & Me.Recipient & "'")
(Adding spaces to show clarity - remove them to make it work)
DCount("*","Main","[Name]= ' " & Me.Recipient & " ' ")

OR
DCount("*","Main","[Name]= """& Me.Recipient & """")
That's 3 quotes and 4 quotes in the criteria portion

OR
DCount("*","Main","[Name]= " & Chr(34) & Me.Recipient & Chr(34) )

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[fredg]

You need to include text delimiters in the DCount statement if you are going
to use a text string.

DCount("*","Main","[Name]= '" & Me.Recipient & "'")
(Adding spaces to show clarity - remove them to make it work)
DCount("*","Main","[Name]= ' " & Me.Recipient & " ' ")

OR
DCount("*","Main","[Name]= """& Me.Recipient & """")
That's 3 quotes and 4 quotes in the criteria portion

OR
DCount("*","Main","[Name]= " & Chr(34) & Me.Recipient & Chr(34) )

In addition to what John has written, Name is a reserved
Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html

 

[injanib via AccessMonster.com]

Thanks to both of you. I actually came across a problem where I used the Name
word as feild name in another data base and learned that it is a reserved
word after encountering errors. I should have known better when using it
again in this databse.

You need to include text delimiters in the DCount statement if you are going
to use a text string.

[quoted text clipped - 9 lines]

OR
DCount("*","Main","[Name]= " & Chr(34) & Me.Recipient & Chr(34) )

In addition to what John has written, Name is a reserved
Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html