syntax error in string

[dhoover via AccessMonster.com]

Can anyone tell me what's wrong with the syntax in this? I've tried single
quotes, double quotes and a combo of both but I still get an syntax error
message.


stLinkCriteria = "[CustomerID]=" & Me![CustomerID]

 

[Douglas J. Steele]

What's the data type of CustomerID? If it's text, try:

stLinkCriteria = "[CustomerID]=""" & Me![CustomerID] & """"

Thats' 3 double quotes before, and 4 double quotes after.

 

[dhoover via AccessMonster.com]

No go, I get "openform action was cancelled". This is the entire piece of
code which is tied to a command button. CustomerID is number but it's linked
to a text field, so the results actually show as text rather than numbers:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Exception Contact Info Form"

stLinkCriteria = "[CustomerID]=""" & Me![CustomerID] & """"

DoCmd.OpenForm stDocName, , , stLinkCriteria

What's the data type of CustomerID? If it's text, try:

stLinkCriteria = "[CustomerID]=""" & Me![CustomerID] & """"

Thats' 3 double quotes before, and 4 double quotes after.

Can anyone tell me what's wrong with the syntax in this? I've tried
single
quotes, double quotes and a combo of both but I still get an syntax error
message.

stLinkCriteria = "[CustomerID]=" & Me![CustomerID]

 

[Douglas J. Steele]

So what's in Me!CustomerId: text or number? What do you see if you put
MsgBox Me![CustomerID] before the stLinkCriteria = line?

It sounds as though you may be using that misguided misfeature, the Lookup
Field. See http://www.mvps.org/access/lookupfields.htm at "The Access Web"
for some of the reasons why that's not a good idea.



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

No go, I get "openform action was cancelled". This is the entire piece of
code which is tied to a command button. CustomerID is number but it's
linked
to a text field, so the results actually show as text rather than numbers:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Exception Contact Info Form"

stLinkCriteria = "[CustomerID]=""" & Me![CustomerID] & """"

DoCmd.OpenForm stDocName, , , stLinkCriteria

What's the data type of CustomerID? If it's text, try:

stLinkCriteria = "[CustomerID]=""" & Me![CustomerID] & """"

Thats' 3 double quotes before, and 4 double quotes after.

Can anyone tell me what's wrong with the syntax in this? I've tried
single
quotes, double quotes and a combo of both but I still get an syntax
error
message.

stLinkCriteria = "[CustomerID]=" & Me![CustomerID]

 

[dhoover via AccessMonster.com]

The msg box shows a number.

So what's in Me!CustomerId: text or number? What do you see if you put
MsgBox Me![CustomerID] before the stLinkCriteria = line?

It sounds as though you may be using that misguided misfeature, the Lookup
Field. See http://www.mvps.org/access/lookupfields.htm at "The Access Web"
for some of the reasons why that's not a good idea.

No go, I get "openform action was cancelled". This is the entire piece of
code which is tied to a command button. CustomerID is number but it's

[quoted text clipped - 23 lines]

stLinkCriteria = "[CustomerID]=" & Me![CustomerID]

 

[Douglas J. Steele]

Okay, so the original

stLinkCriteria = "[CustomerID]=" & Me![CustomerID]

is correct.

Is that the line on which the error occurs, or is it on the line where you
use stLinkCriteria?

How is stLinkCriteria declared?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The msg box shows a number.

So what's in Me!CustomerId: text or number? What do you see if you put
MsgBox Me![CustomerID] before the stLinkCriteria = line?

It sounds as though you may be using that misguided misfeature, the Lookup
Field. See http://www.mvps.org/access/lookupfields.htm at "The Access Web"
for some of the reasons why that's not a good idea.

No go, I get "openform action was cancelled". This is the entire piece
of
code which is tied to a command button. CustomerID is number but it's

[quoted text clipped - 23 lines]

stLinkCriteria = "[CustomerID]=" & Me![CustomerID]

 

[dhoover via AccessMonster.com]

The error occurs at stLinkCriteria in the piece below. stLinkCriteria is
declared as a string

DoCmd.OpenForm stDocName, , , stLinkCriteria

Thanks!

Okay, so the original

stLinkCriteria = "[CustomerID]=" & Me![CustomerID]

is correct.

Is that the line on which the error occurs, or is it on the line where you
use stLinkCriteria?

How is stLinkCriteria declared?

The msg box shows a number.

[quoted text clipped - 11 lines]

stLinkCriteria = "[CustomerID]=" & Me![CustomerID]

 

[Douglas J. Steele]

Does stDocName contain the name of a form in your application?

Is CustomerId a field in the RecordSource of the form pointed to by
stDocName?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The error occurs at stLinkCriteria in the piece below. stLinkCriteria is
declared as a string

DoCmd.OpenForm stDocName, , , stLinkCriteria

Thanks!

Okay, so the original

stLinkCriteria = "[CustomerID]=" & Me![CustomerID]

is correct.

Is that the line on which the error occurs, or is it on the line where you
use stLinkCriteria?

How is stLinkCriteria declared?

The msg box shows a number.

[quoted text clipped - 11 lines]

stLinkCriteria = "[CustomerID]=" & Me![CustomerID]

 

[dhoover via AccessMonster.com]

Yes and Yes

Does stDocName contain the name of a form in your application?

Is CustomerId a field in the RecordSource of the form pointed to by
stDocName?

The error occurs at stLinkCriteria in the piece below. stLinkCriteria is
declared as a string

[quoted text clipped - 19 lines]

stLinkCriteria = "[CustomerID]=" & Me![CustomerID]

 

[Douglas J. Steele]

Can you open the form without passing a criteria?

What code is there in the Open and Load events of the form you're opening?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Yes and Yes

Does stDocName contain the name of a form in your application?

Is CustomerId a field in the RecordSource of the form pointed to by
stDocName?

The error occurs at stLinkCriteria in the piece below. stLinkCriteria
is
declared as a string

[quoted text clipped - 19 lines]

stLinkCriteria = "[CustomerID]=" & Me![CustomerID]

 

[dhoover via AccessMonster.com]

I could open the form without passing the criteria, but it would defeat the
purpose. It's likely that the people using this won't bother to make sure
they are on the correct client before updating their information.

There is no code in the open and load events on the form

Can you open the form without passing a criteria?

What code is there in the Open and Load events of the form you're opening?

Yes and Yes

[quoted text clipped - 9 lines]

stLinkCriteria = "[CustomerID]=" & Me![CustomerID]

 

[Douglas J. Steele]

I was asking about whether you could open the form without a criteria
strictly to determine whether the problem was with the form or with the
calling of the form. It sounds as though it's only with the calling of the
form.

I'm finding it difficult to keep track of what we've already discussed due
to how much trimming of previous posts has been done.

What is your current code, and what's the exact error message? Make sure
your code snippet includes the declaration of the variables, the assignment
of values to them, and the actual call of the OpenForm method.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I could open the form without passing the criteria, but it would defeat the
purpose. It's likely that the people using this won't bother to make sure
they are on the correct client before updating their information.

There is no code in the open and load events on the form

Can you open the form without passing a criteria?

What code is there in the Open and Load events of the form you're opening?

Yes and Yes

[quoted text clipped - 9 lines]

stLinkCriteria = "[CustomerID]=" & Me![CustomerID]

 

[dhoover via AccessMonster.com]

Private Sub Command4_Click()
On Error GoTo Err_Command4_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Exception Contact Info Form"


stLinkCriteria = "[Customer]=" & Me![Customer]

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command4_Click:
Exit Sub

Err_Command4_Click:
MsgBox Err.Description
Resume Exit_Command4_Click

End Sub



The error is occuring at DoCmd.OpenForm stDocName, , , stLinkCriteria,
however Me![Customer] shows as null when moused over. The error message
shows: Syntax error (missing operator) in query expression '[Customer]='.

If I take out the criteria, the form will open with no problems. Thanks!

I was asking about whether you could open the form without a criteria
strictly to determine whether the problem was with the form or with the
calling of the form. It sounds as though it's only with the calling of the
form.

I'm finding it difficult to keep track of what we've already discussed due
to how much trimming of previous posts has been done.

What is your current code, and what's the exact error message? Make sure
your code snippet includes the declaration of the variables, the assignment
of values to them, and the actual call of the OpenForm method.

I could open the form without passing the criteria, but it would defeat the
purpose. It's likely that the people using this won't bother to make sure

[quoted text clipped - 11 lines]

stLinkCriteria = "[CustomerID]=" & Me![CustomerID]

 

[Douglas J. Steele]

First, I thought we'd determined that Customer was a text field in your
table. It doesn't matter whether or not it's a numeric value, if it's being
stored in a text field, you need to enclose the value in quotes:

stLinkCriteria = "[Customer]='" & Me![Customer] & "'"

I also thought we'd put a message box in there along the lines of MsgBox
Me![Customer] that actually returned a value, so I'm puzzled by your comment
that "Me![Customer] shows as null when moused over".

If sometimes the box has a value and sometimes it doesn't, you need to check
and change how you're opening the form:

Private Sub Command4_Click()
On Error GoTo Err_Command4_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Exception Contact Info Form"

If IsNull(Me![Customer]) = True Then
stLinkCriteria = vbNullString
Else
stLinkCriteria = "[Customer]='" & Me![Customer] & "'"
End If

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command4_Click:
Exit Sub

Err_Command4_Click:
MsgBox Err.Description
Resume Exit_Command4_Click

End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Private Sub Command4_Click()
On Error GoTo Err_Command4_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Exception Contact Info Form"


stLinkCriteria = "[Customer]=" & Me![Customer]

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command4_Click:
Exit Sub

Err_Command4_Click:
MsgBox Err.Description
Resume Exit_Command4_Click

End Sub



The error is occuring at DoCmd.OpenForm stDocName, , , stLinkCriteria,
however . The error message
shows: Syntax error (missing operator) in query expression '[Customer]='.

If I take out the criteria, the form will open with no problems. Thanks!

I was asking about whether you could open the form without a criteria
strictly to determine whether the problem was with the form or with the
calling of the form. It sounds as though it's only with the calling of the
form.

I'm finding it difficult to keep track of what we've already discussed due
to how much trimming of previous posts has been done.

What is your current code, and what's the exact error message? Make sure
your code snippet includes the declaration of the variables, the
assignment
of values to them, and the actual call of the OpenForm method.

I could open the form without passing the criteria, but it would defeat
the
purpose. It's likely that the people using this won't bother to make
sure

[quoted text clipped - 11 lines]

stLinkCriteria = "[CustomerID]=" & Me![CustomerID]