Help with Where Clause please.

[tmccork]

I am trying to open a form and have it's where clause filter by [size] and [color] from the current form. I cannot seem to get it to work. I get the error.

Syntax Error (Missing Operator) in query expression '[Size]=11 oz. And [Color]=Blue'.

"11 oz." and "Blue" are refference the correct record but it is not working.

My code looks like this.

On Error GoTo Err_Command15_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "MugInfo-Tbl"

stLinkCriteria = "[Size]=" & Me![Size] & " AND [Color]=" & "" & Me![Color] & ""

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command15_Click:
Exit Sub

Err_Command15_Click:
MsgBox Err.Description
Resume Exit_Command15_Click

End Sub


Can someone give me some advise?
Thanks
Terry

 

[KenReay]

Hi

The way you written the expression:

stLinkCriteria = "[Size]=" & Me![Size] & " AND [Color]=" & "" & Me![Color] & ""

implies that [Size] is numeric (ie no quotes), and {Color] is text (ie has quotes).

But the error message:

Syntax Error (Missing Operator) in query expression '[Size]=11 oz. And [Color]=Blue'.

clearly shows none numeric date in Me![Size] (ie 11 oz.),

could this be your problem?



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[tmccork]

I tried removing the quotes, the form opened but does not bring up the record. If I try to filter just by one field it works fine,

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

My string that opens a blank form looks like this

stLinkCriteria = [Size] = " & Me![Size] & " And [Color] = " & "" & Me![Color] & """

Do you have any other ideas. Thanks for your input.
Terry

 

[JohnLowell]

Try this:

stLinkCriteria = "[Size] = " & chr(34) & Me![Size] & chr(34) & " And [Color] = " & chr(34) & Me![Color] & chr(34)

the chr(34) is an Ascii quote. You need to enclose each column criteria with quotes.

Let me know if this helps.

 

[camidon]

Try this one:

stLinkCriteria = "[Size] = '" & " Me![size]" & "'" & " AND [Color] = '" & " Me![Color]" & "'"

The strings have to be enclosed in single quotes.

 

[cmmrfrds]

When building a string in code and it is not working correctly it is a good idea to view the result of the string.

stLinkCriteria = [Size] = " & Me![Size] & " And [Color] = " & "" & Me![Color] & """

MsgBox stLinkCriteria

To make it more readable you can use chr(34) in place of the double quotes and less apt to make a mistake.

stLinkCriteria = " [Size] = " & chr(34) & Me![Size] & chr(34) & " And [Color] = " & chr(34) & Me![Color] & chr(34)

 

[tmccork]

Thanks everybody, I used the chr(34) and it works.
I appreciate the help.
Terry