SQL Within VBA

[Mark_e_y]

I have a search form which searches a table for employee records. To keep it simply I am trying to get it to work only on the surname textbox at the moment. To do this I have a global string that has the 'WHERE' part of the SQL statement stored in it. It goes something like this:

Set rcd = db.OpenRecordset("SELECT * " & _
"FROM tblEmployees " & _
"WHERE " & gstrEmployeeSearch & ";")

rcd is the recordset variable and db is obviously the database variable.

When the search is performed the variable value at this stage is simply:

gstrEmployeeSearch = "[LastName] Like " _
& Chr$(34) & Me![txtLastName] & "*" & Chr(34)

The error message I am receiving is 'Type Mismatch' Unfortunately the help facility wasn't much help! I hope one of you lot will be!!

Thanks all

Mark

 

[schwarz]

if you are working with SQL server I would suggest changing

gstrEmployeeSearch = "[LastName] Like " _
& Chr$(34) & Me![txtLastName] & "*" & Chr(34)

into

gstrEmployeeSearch = "[LastName] Like ' " _
& Me![txtLastName] & "*' "

Hope this works !

[This message has been edited by schwarz (edited 09-09-2000).]

[This message has been edited by schwarz (edited 09-09-2000).]

 

[Mark_e_y]

Thanks for your help,

Can you tell me why the single apostrophe changes things?? Is it similar to enclosing the full lot on brackets?

 

[accesswatch]

Thanks Pat for the correction

Mark_e_y Please ignore my previous answers
I have deleted these due to the fact they were incorrect.

Regards
Trevor from www.accesswatch.co.uk

(Trust But verify)

 

[Mark_e_y]

Thank you once again for all your help...especially you Pat.

Strangely enough I eventually solved the problem, not by changing the sytax, but by changing the MS DAO Object Library version from 3.8 to 3.51. (I'm not sure if this is the best fix but I'm using v2000 to build the DB and it will eventually run on v97 which uses DAO O.L. 3.5)

The code as is above worked very well indeed (until I got to searching between date values but thats something else!).

Does anyone know if there is a syntax diffrence between the two differnt DAO Library versions? Or could it possibly be a 'bug'?

Any suggestions or feedback is greatly appreciated.

Cheers

Mark

[This message has been edited by Mark_e_y (edited 09-16-2000).]