syntax error at run time

[Jeannie M]

This code fails at run time with the error shown following the code.
Dim strX as String
strX = [Forms]![Reports Dialog]![Combo62]
MsgBox strX
intNumJudges = DMax("NumJudges", "Fees", "[Event] = " & strX)

Syntax error (missing operator) in query expression ’[Event] = Best
Appaering’.

This code works fine WHY the error?
Dim strX As String
strX = [Forms]![Reports Dialog]![Combo62]
MsgBox strX
intNumJudges = DMax("NumJudges", "Fees", "[Event] = [Forms]![Reports
Dialog]![Combo62]")

I have other much more complex code that works and I have compared several
samples such as the one below but I still can’t see the problem. Am I
loosing my mind??

Public Function fncAgeGroup(strField As String, strTable As String,
strCriteria As String, intFormAge As Integer)
On Error GoTo Err_fncAgeGroup

fncAgeGroup = DLookup(strField, strTable, _
strCriteria _
& " AND (Min<=" & intFormAge & ")" _
& " AND (Max>=" & intFormAge & ")")

Any help appreciated

 

[Marshall Barton]

This code fails at run time with the error shown following the code.
Dim strX as String
strX = [Forms]![Reports Dialog]![Combo62]
MsgBox strX
intNumJudges = DMax("NumJudges", "Fees", "[Event] = " & strX)

Syntax error (missing operator) in query expression ’[Event] = Best
Appaering’.

This code works fine WHY the error?
Dim strX As String
strX = [Forms]![Reports Dialog]![Combo62]
MsgBox strX
intNumJudges = DMax("NumJudges", "Fees", "[Event] = [Forms]![Reports
Dialog]![Combo62]")

When you are concatenating a value into an SQL statement,
you must delimit the value according to the field's data
type (nothing for number fields, # for dates and " or ' for
text:

DMax("NumJudges", "Fees", "[Event] = """ & strX & """ ")

Since the quotes are part of a quoted string, they need to
be doubled.

Alternatively:

DMax("NumJudges", "Fees", "[Event] = '" & strX & "' ")

will also work depending on whether strX contains a ' or "

The reason why the form/control reference worked is that
Access helps out by resolving the reference at the time the
comparison is done. You could (and probably should) use:

DMax("NumJudges", "Fees", "[Event] = '" & [Forms]![Reports
Dialog]![Combo62] & "' ")

 

[Jeannie M]

I don't know what you mean by - quotes are part of a quoted string. there
are no quotes around Best Appearing in either the table field or when I
display it with the MsgBox.
--
Jeannie

This code fails at run time with the error shown following the code.
Dim strX as String
strX = [Forms]![Reports Dialog]![Combo62]
MsgBox strX
intNumJudges = DMax("NumJudges", "Fees", "[Event] = " & strX)

Syntax error (missing operator) in query expression ’[Event] = Best
Appaering’.

This code works fine WHY the error?
Dim strX As String
strX = [Forms]![Reports Dialog]![Combo62]
MsgBox strX
intNumJudges = DMax("NumJudges", "Fees", "[Event] = [Forms]![Reports
Dialog]![Combo62]")

When you are concatenating a value into an SQL statement,
you must delimit the value according to the field's data
type (nothing for number fields, # for dates and " or ' for
text:

DMax("NumJudges", "Fees", "[Event] = """ & strX & """ ")

Since the quotes are part of a quoted string, they need to
be doubled.

Alternatively:

DMax("NumJudges", "Fees", "[Event] = '" & strX & "' ")

will also work depending on whether strX contains a ' or "

The reason why the form/control reference worked is that
Access helps out by resolving the reference at the time the
comparison is done. You could (and probably should) use:

DMax("NumJudges", "Fees", "[Event] = '" & [Forms]![Reports
Dialog]![Combo62] & "' ")

 

[Douglas J. Steele]

Marsh is trying to tell you that when you're building a string, in order to
get quotes into that string, you need to double them up.

In other words,

strX = "Marsh says ""This is what I'm trying to tell you"""

will result in strX containing the following (including the double quotes):

Marsh says "This is what I'm trying to tell you"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I don't know what you mean by - quotes are part of a quoted string. there
are no quotes around Best Appearing in either the table field or when I
display it with the MsgBox.
--
Jeannie

This code fails at run time with the error shown following the code.
Dim strX as String
strX = [Forms]![Reports Dialog]![Combo62]
MsgBox strX
intNumJudges = DMax("NumJudges", "Fees", "[Event] = " & strX)

Syntax error (missing operator) in query expression '[Event] = Best
Appaering'.

This code works fine WHY the error?
Dim strX As String
strX = [Forms]![Reports Dialog]![Combo62]
MsgBox strX
intNumJudges = DMax("NumJudges", "Fees", "[Event] = [Forms]![Reports
Dialog]![Combo62]")

When you are concatenating a value into an SQL statement,
you must delimit the value according to the field's data
type (nothing for number fields, # for dates and " or ' for
text:

DMax("NumJudges", "Fees", "[Event] = """ & strX & """ ")

Since the quotes are part of a quoted string, they need to
be doubled.

Alternatively:

DMax("NumJudges", "Fees", "[Event] = '" & strX & "' ")

will also work depending on whether strX contains a ' or "

The reason why the form/control reference worked is that
Access helps out by resolving the reference at the time the
comparison is done. You could (and probably should) use:

DMax("NumJudges", "Fees", "[Event] = '" & [Forms]![Reports
Dialog]![Combo62] & "' ")