sql statement syntax help 1

[mattl72]

Hi all

I'm having problem with a difficult SQL statement I am trying to create a recordset on. I get a "compile error: Expect expression" when I try to create it. Can someone help me with the syntax?

Here it is:

"SELECT [Item No] FROM [Bin Master] WHERE (([Bin Master].[Primary]) = True) and (([Bin Master].[Bin Loc]) = " & "'" & Trim([Forms]![Bin Master Form]![Location]) & "'" & &quot" and (([Bin Master].[Item No]) = " & "'" & Trim([Forms]![Bin Master Form]![ITEM_NO]) & "'" & &quot"

I have traced it down to the fact that I have/need to two double quotes in the string. Is there anyway to get this to work? I have a couple of other forms that need a similar statement so any help would go a long way.

Thanks in advance,
Matt L

 

[DanJR]

the string probably truncates after the end of line bit:

Trim([Forms]![Bin Master Form]![Location]) & "'" & &quot"

Thus,

Try this:

"SELECT [Item No] FROM [Bin Master] WHERE (([Bin Master].[Primary]) = True) and (([Bin Master].[Bin Loc]) = '" & Trim([Forms]![Bin Master Form]![Location]) & "') and (([Bin Master].[Item No]) = '" & Trim([Forms]![Bin Master Form]![ITEM_NO]) & "')"


Try this format to reference vba strings:

strSQL = "...(Where [myField] = '" & strField & "')"

rather than,

strSQL = "...(Where [myField] = " & "'" & strField & "'" & &quot"

...less code = less probability of accidental mistakes..

Chers,
Dan