Multiple criteria for where statement

Sketchin

Registered User.
Local time
Yesterday, 20:42
Joined
Dec 20, 2011
Messages
580
Hi All,

I am creating a report that has a filter based on 3 separate listboxes. The user has the option of choosing one or more filter criteria from each listbox. The trouble I am having is if the user only chooses one filter, I need to adjust my filter string. This is the code that generates the filter:

Code:
    strWhereFinal = strWhere1 & "AND " & strWhere2 & "AND " & strWhere3

You can see that if strwhere1 is NULL, the string will start with "AND [ApplicationStatus] = 1" and will obviously cause a failure.

Is there a simple way to build this string based on the number of search strings that are not null?
 
Just place it in 3 IF statements:

Code:
If Nz(strWhere1,"")<>"" Then strWhereFinal = strWhereFinal & strWhere1 & "AND "
If Nz(strWhere2,"")<>"" Then strWhereFinal = strWhereFinal & strWhere2 & "AND "
If Nz(strWhere3,"")<>"" Then strWhereFinal = strWhereFinal & strWhere3
 
The problem with this construct is that if there is no strWhere3 there is a hanging 'AND' which will cause a stuff up.


Code:
If Nz(strWhere1,"")<>"" Then strWhereFinal = strWhereFinal & strWhere1 & " AND "
If Nz(strWhere2,"")<>"" Then strWhereFinal = strWhereFinal & strWhere2 & " AND "
If Nz(strWhere3,"")<>"" Then strWhereFinal = strWhereFinal & strWhere3 & " AND "]
If len(strWhereFinal >5) then strWhereFinal = left(strWhereFinal , len(strWhereFinal )-5

PS I have appended a space before 'AND'
 

Users who are viewing this thread

Back
Top Bottom