Syntax error in query expression

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I created a command button that allows the users to go to the general ledger
form for a specific check ID #. It works for records that beginnings with an
alphabet in the pub ID field but doesn't work in the records that beginning
with a numerial in the pub ID field.
This is the error message I receive: Syntax error (missing operator) in
query expression '[CheckID]=4CLJ'.

Here is the code:

Private Sub Command198_Click()
On Error GoTo Err_Command198_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "General Ledger"

stLinkCriteria = "[CheckID]=" & Me![PubID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command198_Click:
Exit Sub

Err_Command198_Click:
MsgBox Err.Description
Resume Exit_Command198_Click

End Sub

Can someone tell me what I need to change in order for this command to work?

I've tried the following from another person's question:

stLinkCriteria = "[CheckID]=" & "'" & Replace(Me![PubID],"'","'") & "'"

and also

stLinkCriteria = "[CheckID]=""" & Me![PubID] & """"

and the result was this error message: The OpenForm action was cancelled.

Can someone help me with this?
 
Carmen said:
I've tried the following from another person's question:

stLinkCriteria = "[CheckID]=" & "'" & Replace(Me![PubID],"'","'") & "'"

and also

stLinkCriteria = "[CheckID]=""" & Me![PubID] & """"

and the result was this error message: The OpenForm action was cancelled.

Can someone help me with this?
Click to expand...

It's much easier to use single quotes to delimit a string value when
building the string using concatenation in code, because they can be
embedded inside the quoted string literal. Enter the line like this:

stLinkCriteria = "[CheckID]='" & Me![PubID] & "'"

That is, after the = sign, type an apostrophe before the double quote, then
at the end of the line type another apostrophe surrounded by double quotes.
Taking your example ID of 4CLJ, when Access interprets this string, it looks
like:

[CheckID]='4CLJ'
 
Stuart:
I've tried your advice and I'm still getting the error message: The
OpenForm action was cancelled.

Stuart McCall said:
Carmen said:
I've tried the following from another person's question:

stLinkCriteria = "[CheckID]=" & "'" & Replace(Me![PubID],"'","'") & "'"

and also

stLinkCriteria = "[CheckID]=""" & Me![PubID] & """"

and the result was this error message: The OpenForm action was cancelled.

Can someone help me with this?
Click to expand...

It's much easier to use single quotes to delimit a string value when
building the string using concatenation in code, because they can be
embedded inside the quoted string literal. Enter the line like this:

stLinkCriteria = "[CheckID]='" & Me![PubID] & "'"

That is, after the = sign, type an apostrophe before the double quote, then
at the end of the line type another apostrophe surrounded by double quotes.
Taking your example ID of 4CLJ, when Access interprets this string, it looks
like:

[CheckID]='4CLJ'
Click to expand...
 
Carmen said:
Stuart:
I've tried your advice and I'm still getting the error message: The
OpenForm action was cancelled.

Stuart McCall said:
Carmen said:
I've tried the following from another person's question:

stLinkCriteria = "[CheckID]=" & "'" & Replace(Me![PubID],"'","'") & "'"

and also

stLinkCriteria = "[CheckID]=""" & Me![PubID] & """"

and the result was this error message: The OpenForm action was
cancelled.

Can someone help me with this?
Click to expand...

It's much easier to use single quotes to delimit a string value when
building the string using concatenation in code, because they can be
embedded inside the quoted string literal. Enter the line like this:

stLinkCriteria = "[CheckID]='" & Me![PubID] & "'"

That is, after the = sign, type an apostrophe before the double quote,
then
at the end of the line type another apostrophe surrounded by double
quotes.
Taking your example ID of 4CLJ, when Access interprets this string, it
looks
like:

[CheckID]='4CLJ'
Click to expand...
Click to expand...

In that case the problem lies elsewhere, most likely in the Load event of
your 'General Ledger' form.
 
Hi Carmen,

the field CheckID exists in the recordsource of the form you're trying to
open?


Carmen said:
Stuart:
I've tried your advice and I'm still getting the error message: The
OpenForm action was cancelled.

Stuart McCall said:
Carmen said:
I've tried the following from another person's question:

stLinkCriteria = "[CheckID]=" & "'" & Replace(Me![PubID],"'","'") & "'"

and also

stLinkCriteria = "[CheckID]=""" & Me![PubID] & """"

and the result was this error message: The OpenForm action was cancelled.

Can someone help me with this?
Click to expand...

It's much easier to use single quotes to delimit a string value when
building the string using concatenation in code, because they can be
embedded inside the quoted string literal. Enter the line like this:

stLinkCriteria = "[CheckID]='" & Me![PubID] & "'"

That is, after the = sign, type an apostrophe before the double quote, then
at the end of the line type another apostrophe surrounded by double quotes.
Taking your example ID of 4CLJ, when Access interprets this string, it looks
like:

[CheckID]='4CLJ'
Click to expand...
Click to expand...
 
It's actually a subform in my main contact form.

Paolo said:
Hi Carmen,

the field CheckID exists in the recordsource of the form you're trying to
open?


Carmen said:
Stuart:
I've tried your advice and I'm still getting the error message: The
OpenForm action was cancelled.

Stuart McCall said:
I've tried the following from another person's question:

stLinkCriteria = "[CheckID]=" & "'" & Replace(Me![PubID],"'","'") & "'"

and also

stLinkCriteria = "[CheckID]=""" & Me![PubID] & """"

and the result was this error message: The OpenForm action was cancelled.

Can someone help me with this?

It's much easier to use single quotes to delimit a string value when
building the string using concatenation in code, because they can be
embedded inside the quoted string literal. Enter the line like this:

stLinkCriteria = "[CheckID]='" & Me![PubID] & "'"

That is, after the = sign, type an apostrophe before the double quote, then
at the end of the line type another apostrophe surrounded by double quotes.
Taking your example ID of 4CLJ, when Access interprets this string, it looks
like:

[CheckID]='4CLJ'
Click to expand...
Click to expand...
Click to expand...
 
The general ledger is actually a subform in my main contact form. Could that
be the reason why it doesn't work?

Stuart McCall said:
Carmen said:
Stuart:
I've tried your advice and I'm still getting the error message: The
OpenForm action was cancelled.

Stuart McCall said:
I've tried the following from another person's question:

stLinkCriteria = "[CheckID]=" & "'" & Replace(Me![PubID],"'","'") & "'"

and also

stLinkCriteria = "[CheckID]=""" & Me![PubID] & """"

and the result was this error message: The OpenForm action was
cancelled.

Can someone help me with this?

It's much easier to use single quotes to delimit a string value when
building the string using concatenation in code, because they can be
embedded inside the quoted string literal. Enter the line like this:

stLinkCriteria = "[CheckID]='" & Me![PubID] & "'"

That is, after the = sign, type an apostrophe before the double quote,
then
at the end of the line type another apostrophe surrounded by double
quotes.
Taking your example ID of 4CLJ, when Access interprets this string, it
looks
like:

[CheckID]='4CLJ'
Click to expand...
Click to expand...

In that case the problem lies elsewhere, most likely in the Load event of
your 'General Ledger' form.
Click to expand...
 
Carmen said:
The general ledger is actually a subform in my main contact form. Could
that
be the reason why it doesn't work?
Click to expand...

Very possible. You're attempting to open a form that is already open. AFAIK
Access sometimes allows this by opening a new instance, but this is not
happening in your case. Is there any code in 'General Ledger's Open or Load
events?
 
Back
Top