SQL wildcard question

[Sophie]

hi

Is there a way to use a wildcard character like * in SQL?

Here is a snippet of my code:

str_a = "...... WHERE ((Q1.score = Q2.score) AND (Q2.TestID = "
str_b = ")) ORDER BY ...... "
strSQL = str_a & cboTest & str_b
Forms!frmAwards.RecordSource = strSQL

cboTest has values of 1, 2, 3... and I have added a UNION to include *
representing <All>. I currently use If,Else statements to use the code below
if cboTest = "*", and the above code otherwise.

str_a = "...... WHERE ((Q1.score = Q2.score)"
str_b = ") ORDER BY ...... "
strSQL = str_a & str_b
Forms!frmAwards.RecordSource = strSQL

The actual code is much more complicated than this, with other comboboxes to
consider as well. The code would be much cleaner if I could use the *
wildcard in SQL. How can I do this?

Sophie

 

[Allen Browne]

You can use a wildcard (* in Access, or % in SQL Server), but the problem is
that it doesn't match all records. Any record where the field is Null will
be excluded when you use the wildcard.

It looks like you are building the WHERE clause in code, so the simplest
solution might be to simply omit that part of the WHERE clause rather than
using a wildcard at all. That will be more efficient, and solves the problem
with nulls.

So:
If Me.cboText <> "<All>" Then
str_a = ... 'whatever
End If

The string does need the right delimiters for the data type. If building
strings like that is new for you, grab this example:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

 

[Dale Fye]

I think your code should look like:

str_a = "...... WHERE ((Q1.score = Q2.score) "
if me.cboTest <> "*" Then
str_a = str_a & " AND (Q2.TestID = " & me.cboTest & ")"
endif
str_b = ") ORDER BY ...... "
strSQL = str_a & str_b
Forms!frmAwards.RecordSource = strSQL

This basically does what Allen recommends by ignoring the "*" value in the
combo box.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[Sophie]

I was so close to this, but couldn't get the parentheses right. Thanks for
your code - it worked perfectly. Thanks aslo to Allen's tip re Nulls - that
was a big help in several places of my application.