Like

[DB via AccessMonster.com]

I am trying to use Like to find said field that contains the words "order"
and "to". I have tried several variations of the below. The SQL executes as
the first example in a query but when I put it into VBA and try to set it as
the recordsource to a form I get Compile Error: sytax error. I have seen in
various posts that you have to concat the wildcards as strings but I don't
beleive that applies in my case since the wildcards are a part of the string.
I tried a variation of concating the wildcards anyway in option 2.

1. Like "*TO*" And (tblHouse.[Consignee Client Name]) Like "*ORDER*"
2. Like "*" & "TO" & "*" And (tblHouse.[Consignee Client Name]) Like "*" &
"ORDER" & "*"


Here is the whole recordsource SQL statement.

Me.RecordSource = "SELECT tblHouseFinalScriptInfo.FileNumber, tblHouse.
[Consignee Client Name]" & _
"FROM tblHouseFinalScriptInfo LEFT JOIN tblHouse ON tblHouseFinalScriptInfo.
FileNumber = tblHouse.[File Number]" & _
"GROUP BY tblHouseFinalScriptInfo.FileNumber, tblHouse.[Consignee Client Name]
, tblHouseFinalScriptInfo.RevClientNo, tblHouseFinalScriptInfo.BillClientNo,
tblHouseFinalScriptInfo.ConClientNo" & _
"HAVING (((tblHouse.[Consignee Client Name]) Like "*" & "TO" & "*" And
(tblHouse.[Consignee Client Name]) Like "*" & "ORDER" & "*") AND (
(tblHouseFinalScriptInfo.RevClientNo) Is Null) AND ((tblHouseFinalScriptInfo.
BillClientNo) Is Null) AND ((tblHouseFinalScriptInfo.ConClientNo) Is Null));"



Any help would be greatly appreciated. Thank You.

 

[Douglas J. Steele]

"HAVING (((tblHouse.[Consignee Client Name]) Like '*TO*' And
(tblHouse.[Consignee Client Name]) Like '*ORDER*') AND (

What I've done is put single quotes around '*TO*' and '*ORDER*'. You could
also put double quotes around, but since you're in a string already, you
need to put two double quotes where you want one to appear in the string:

"HAVING (((tblHouse.[Consignee Client Name]) Like ""*TO*"" And
(tblHouse.[Consignee Client Name]) Like ""*ORDER*"") AND (

 

[John W. Vinson]

I am trying to use Like to find said field that contains the words "order"
and "to".

Just note that the string "Toronto" contains "to" (twice).

Finding a *WORD* "to" in a string is non trivial - you need to allow for the
possiblity that it's at the beginning (LIKE "to*"), in the middle set off by
blanks (LIKE "* to *",) or at the end (LIKE "* to"). The same applies to
"order" but it's less likely that the word order will be embedded in some
other word.

That said... see the other responses to correct your overly complex syntax.

 

[David W. Fenton]

"HAVING (((tblHouse.[Consignee Client Name]) Like '*TO*' And
(tblHouse.[Consignee Client Name]) Like '*ORDER*') AND (

Uh, those look like WHERE clauses. HAVING should be used only on
summarized values (SUM, MAX, FIRST, etc.).

 

[Douglas J. Steele]

"HAVING (((tblHouse.[Consignee Client Name]) Like '*TO*' And
(tblHouse.[Consignee Client Name]) Like '*ORDER*') AND (

Uh, those look like WHERE clauses. HAVING should be used only on
summarized values (SUM, MAX, FIRST, etc.).

You're right. That's what happens when you only look at the problem section
of the query, not its intent.