Missing operator Error

  • Thread starter Thread starter hntsignif
  • Start date Start date
H

hntsignif

Why would this code:

------------------------------------------------------------
Private Sub DateCheckButton_Click()
On Error GoTo Err_DateCheckButton_Click

Dim stDocName As String
Dim stLinkCriteria As String

stLinkCriteria = "[SEASONS]=' " & Me.SEASON & " ' And [DevCodeA]=' " &
Me.DEVCode & " ' And [MarketingName]=' " & Me.MARKETINGNAME & " ' "
stDocName = "MilestoneDates"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_DateCheckButton_Click:
Exit Sub

Err_DateCheckButton_Click:
MsgBox Err.Description
Resume Exit_DateCheckButton_Click

End Sub
------------------------------------------------------------

Give me this error:

-----------------------------------------------------------
Syntax error (missing operator) in query expression '[SEASONS]='SP11' And
[DevCodeA]='E72410' And [MarketingName]='Womens Training No Show".
-----------------------------------------------------------

As you can see, the correct information is drawing into the error but the
form is not opening. It was working before.

Help. This is frustrating me!
 
Why would this code:

------------------------------------------------------------
Private Sub DateCheckButton_Click()
On Error GoTo Err_DateCheckButton_Click

Dim stDocName As String
Dim stLinkCriteria As String

stLinkCriteria = "[SEASONS]=' " & Me.SEASON & " ' And [DevCodeA]=' " &
Me.DEVCode & " ' And [MarketingName]=' " & Me.MARKETINGNAME & " ' "
stDocName = "MilestoneDates"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_DateCheckButton_Click:
Exit Sub

Err_DateCheckButton_Click:
MsgBox Err.Description
Resume Exit_DateCheckButton_Click

End Sub
------------------------------------------------------------

Give me this error:

-----------------------------------------------------------
Syntax error (missing operator) in query expression '[SEASONS]='SP11' And
[DevCodeA]='E72410' And [MarketingName]='Womens Training No Show".
-----------------------------------------------------------

As you can see, the correct information is drawing into the error but the
form is not opening. It was working before.

Help. This is frustrating me!
Click to expand...

It is possible that the data in MarketingName is actually the (more
grammatical) string

Women's Training No Show

Because if so, the apostrophe is being taken as a string terminator. If
there's any chance that a string will contain an apostrophe, consider using "
as a string delimiter rather than '; to do so in a string delimited by
doublequotes, use a double doublequote:

stLinkCriteria = "[SEASONS]='" & Me.SEASON & "' And [DevCodeA]='" &
Me.DEVCode & "' And [MarketingName]=""" & Me.MARKETINGNAME & """"
stDocName = "MilestoneDates"
DoCmd.OpenForm stDocName, , , stLinkCriteria

As posted, though, I'm not sure what would be causing this error!
 
Does the actual string that you posted:
'Womens Training No Show".

contain a ' in Womens? If yes, then your code is seeing that ' as the end of
the string, and the characters following it are the cause of the error.

Try this:

stLinkCriteria = "[SEASONS]=' " & Me.SEASON & " ' And [DevCodeA]=' " &
Me.DEVCode & " ' And [MarketingName]=' " & Replace(Me.MARKETINGNAME, "'",
"''") & " ' "

This doubles up the ' characters that might be in MARKETINGNAME, and then
the code will see a single ' embedded within the string.

Also, do you mean to introduce spaces at the beginning and end of the
concatenated strings? That is what your posted code does.
 
Hello Ken,

Yes, the contents of the field MARKETINGNAME could contain a '. I tried
your code and it is pulling up the form, but it is empty every time.

I just put the extra spaces in the posting so it was easier to see the "s
and 's. The are not in the actual string.

Holly
 
If the form is coming up blank, then one of the conditions in the criteria
string is not true (your criteria string requires all three conditions to be
met in order to return records), or the criteria is not applicable to the
form's recordsource query.

Try taking out two of the criteria, and keep just one. Does the form show
records? Then add one more criterion to the string, and test that. At some
point, you'll probably find out when the criteria no longer return records.

--

Ken Snell
http://www.accessmvp.com/KDSnell/


hntsignif said:
Hello Ken,

Yes, the contents of the field MARKETINGNAME could contain a '. I tried
your code and it is pulling up the form, but it is empty every time.

I just put the extra spaces in the posting so it was easier to see the "s
and 's. The are not in the actual string.

Holly

Ken Snell said:
Does the actual string that you posted:
'Womens Training No Show".

contain a ' in Womens? If yes, then your code is seeing that ' as the end
of
the string, and the characters following it are the cause of the error.

Try this:

stLinkCriteria = "[SEASONS]=' " & Me.SEASON & " ' And [DevCodeA]=' "
&
Me.DEVCode & " ' And [MarketingName]=' " & Replace(Me.MARKETINGNAME, "'",
"''") & " ' "

This doubles up the ' characters that might be in MARKETINGNAME, and then
the code will see a single ' embedded within the string.

Also, do you mean to introduce spaces at the beginning and end of the
concatenated strings? That is what your posted code does.
Click to expand...
Click to expand...
 
Back
Top