trying to use wildcards in a DOCMD.RUNSQL, please help

[commanderbob]

Hi

I'm tring to write a statment that undersatnds that if a *(or another wildcard charater) is in a box on the form (or in a variable) that it should use it as a wildcard and not a bit of text... (dose that make sence??)

So, the code below works fine if the variables t, o, s are values selected from combo's on the form. However if a combo is not needed for a search then the value is NULL. I was having some problems writing IF statments with NULL values so got around this by replacing the NULL with a * which I thought might be useful later on. So how do I get the SQL doit SQL command to use the right syntax for a wildcard?

***************************************************************************

Dim t
Dim o
Dim s
Dim doit As String


t = txttype
o = txtocc
s = txtsize

'DoCmd.RunSQL "drop table form_temp" make this an if exist then

doit = "select * into form_temp from main where main.type = '" & t & "' or main.likely_occupier = '" & o & "'"

DoCmd.RunSQL doit

*************************************************************

Many thanks Guys

 

[cheerio]

You could try using the LIKE operator in your where clause.

So if the user types Ken into a first name search criteria your SQL will be WHERE firstname LIKE "Ken"

If they type * you get WHERE firstname LIKE "*"

If they type Ken* you get WHERE firstname LIKE "Ken*" which will find Ken and Kenneth.