You can simplify the code by initializing strWhere = "WHERE 1 = 1 ". Then each additional sub-clause can be "AND .... ".
You don't even have to test for the sub-clause as a nullString.

Therefore you get:

subStr1 = "AND [Column1] = '" + value1 + "' "
subStr2 = ""
subStr3 = "AND [Column3] = '" + value3 + "' "
subStr4 = "AND [Column4] = '" + value4 + "' "

strWhere = "WHERE 1 = 1 " + subStr1 + subStr2 + subStr3 + subStr4

Give it a try!

Rand

Nice One!

How would you go go about loading each of the SubStr's? Would you use an IF statement?

Mike

Mike
EchoVue.com

You would only need the If statement to differentiate between when a criteria was null or not.

It's been about a year since I've worked with Access -- almost all SQL Server now.

Since this is Access you can substitute "TRUE" for "1 = 1"

strWhere = "WHERE TRUE " & subStr1 & subStr2 & subStr3 & subStr4

value1, value2, etc. are simple variables.


Since this is Access, you may need to substitute doubled double-quotes for the single quotes as:

subStr1 = "AND [Column1] = """ + value1 + """ "


Rand

Thanks!

I am thinking instead of determining if a field is blank, you could do..

subStr1 = " AND Field1 LIKE '*' & '" & txtOne & "' & '*'"



Mike
EchoVue.com

I'd be concerned about false positive results with that type of LIKE statement.

LIKE "*cat*"

would find not only "cat", but also "catherine", "concatenate", and "polecat"

Rand

Also I have not tried LIKE "**", which may or may not work.

Rand

It should come out '*' & '' & '*' which would return all values.

As far as it finding all words derived from the 'root' criteria - that could defnitely be a problem - but it could be good as well, I guess it just depends how you want to search.

Mike
EchoVue.com

Hey guys,

not to change the subject but one of my criteria is a date range.
I was having an "operator error" I think i got that fixed but once its inserted into the querry it brings up no results any ideas?

This is what I have:

If txtStartdate <> vbNullString Then
        If Len(strWhere) > 0 Then
            strWhere = strWhere & " AND [Recording Date] Between " & txtStartdate & " And " & txtVEndDate & ""
        Else
            strWhere = strWhere & " [Recording Date] Between " & txtStartdate & " And " & txtVEndDate & ""
        End If
    End If

When it runs by itself or with the other txtBoxes it returns no results.

-Dale

LIKE "**" causes no problems, I used it for an app a while back.

Dates are interesting animals in SQL - You would need to suurond them in #'s

strWhere & " [Recording Date] Between #" & txtStartdate & "# And #" & txtVEndDate & "#"

That should work,

Mike

Mike
EchoVue.com