Comma in query expression

  • Thread starter injanib via AccessMonster.com
  • Start date

I

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'.
 
Ad

Advertisements

J

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
..
 
F

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
 
Ad

Advertisements

I

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top