Multiple Filtering Problem with wildcards

[JamesBBB]

Hi All

I am having a problem with multiple filtering, which I cannot fathom, any help you be very gratefully received.

Basically I have a form where a user can select up to 4 filter criteria from 4 text boxes(Each text box is for a specific field), he can select just one, or any number of filters up to four and leave the others blank

However if any of the filter parameters are left blank the records displayed are none.

The code I use is as follows: (Ive just used 2 text boxes for this example)

THis works for a sinlge Filter

str1 = [text1.value]
me.filter - "Staff Number" = '" & str1 & "'
me.filteron = true


This works for a double filter(as long as they are both completed)
str1 = [text1.value]
str2 = [text2.value]

me.filter - "Staff Number" = '" & str1 & "' and [Leave Type] = '" & str2 & "'"
me.filteron = true






However, If I leave text2.value empty, becuase I want to look at one staff number and all types of leave, then no records are returned, which is correct, but, If I detect that nothing has been typed in text2.value (Leave) , I then set str2 to = "*", which I thought would mean all records for this field, but it doesnt work and still returns no records.

if isnUll(str1) or str1 = "" then
str1 = "*"
end if


if isnUll(str2) or str2 = "" then
str2 = "*"
end if



Can anyone please help me with this, I am at a loss.

Many thanks

James

 

[VRoscioli]

You want to compare the values using Like instead of =. A string is only = to * if it is "*". Like allows for wildcards.


-V