Using wildcards with between workaround

[rushforth]

Hi,

I have a query where I need to pull up all records between two numbers based on unbound text boxes. I also need to make it open enough so if one box is left blank then all records up to or below the entered amount are shown. Similarly if both boxes are blank then all records are shown.

Here is my logic so far:

BETWEEN "*" & [Forms]![Filters]![Text1] & "*" AND "*" & [Forms]![Filters]![Text2] & "*"

I know you cant use wildcards with BETWEEN but is there a workaround?

Thanks for any help.

James

[ajetrumpet]

there really isn't a workaround to this. what you can do though, (one option), is to hide another box on the form that you're opening the qry from, and on the action that opens the qry, give the hidden boxes the appropriate values if one of the regular inputs are null. for instance:

where myfield BETWEEN 

[Forms]![Filters]![Text1sub] 

AND 

[Forms]![Filters]![Text2sub]

on the form, one could put behind the query event:

if isnull(me.text1) and isnull(me.text2) then
   me.text1sub = 0
   me.text2sub = 100000000000
elseif isnull(me.text1) then
   me.text1sub = 0
elseif isnull(me.text2) then
   me.text2sub = 100000000000
else
end if

docmd.openquery "QUERY"

the purpose of the really long number there is to give a ridiculous upper bound that no record could ever exceed.

[rushforth]

Thanks! I used two hidden unbound text boxes on the form with =IIf([Text2] Is Null,"100000000",[Text2]) etc...

This way it ensures if a box is populated then emptied the hidden box always shows "10000000" if null.

Cheers for the idea though.

James