select query with WHERE contains some word 2

[davyre]

Hi,

I need to write a query that selects records from TblOrder where the field OrderDesc contains word "Automatic".

selectAutoOrder = "SELECT * FROM TblOrder WHERE OrderDesc LIKE * Automatic * "

it says syntax error (missing operator). Anyone can help? Thnaks

 

[dhookom]

selectAutoOrder = "SELECT * FROM TblOrder WHERE OrderDesc LIKE ""*Automatic*"" "

Duane
Hook'D on Access
MS Access MVP

 

[davyre]

thanks, it worked. But then if i use the code

counter = autoOrderRS.RecordCount
MsgBox (counter)

It shows wrong value, i.e in my table there are 6 records containing Automatic word, while the msgbox only show 1.

how do I change the code

selectAutoOrder = "SELECT * FROM TblOrder WHERE OrderDesc LIKE ""*Automatic*"" "

to select * from TblOrder WHERE OrderDesc LIKE (someVariable) ? So instead of using string "Automatic", I'd like to use a variable contain value "Automatic". Can you help? Thanks

 

[PHV]

selectAutoOrder = "SELECT * FROM TblOrder WHERE OrderDesc LIKE '*" & someVariable  & "*'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

Getting a correct record count often requires code lilke:

    autoOrderRS.MoveLast

Duane
Hook'D on Access
MS Access MVP

 

[davyre]

thanks all, great help!