Use wildcard or value from form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I have a query that uses in the criteria section, text boxes from a form
text box names on the form values are:
Name
Date From
Date To

I want the query to use either the value as per the form
or
if the text box is null i.e. no value entered by the user, then to use a
wildcard i.e. bring everything back

e.g.
If date from is 1Sep05 and Date to is 30Sep05 and Name = SMITH
then the query should bring back all data between dates 1Sep05 and 30Sep05
for people with a name of SMITH

If the Name text box is null on the form, then I want the query to bring
back ALL names between the date range

Getting data back using the date range and when there is a value inthe name
field works fine but I cannot get any data back when the text box is null

Any ideas how to get around this???
 
Try this

Where [Name] Like nz(forms![FormName]![TextBoxName],"*")

Or

Where [Name] Like iif(forms![FormName]![TextBoxName] is null or
forms![FormName]![TextBoxName] = "","*", forms![FormName]![TextBoxName]

)
 
Doesnt quite work - using the nz function OR the iif statements do not bring
back data where there was a null in the underlying table

It however gave me an idea which worked

I have simply created another field on the query that uses the value from
the form
Expr1: nz([Name],"")

and then in the criteria field uses

nz(forms![FormName]![TextBoxName],"") & "*"

Works like a treat

Thanks for the hints to get me pointing in the right direction




Ofer said:
Try this

Where [Name] Like nz(forms![FormName]![TextBoxName],"*")

Or

Where [Name] Like iif(forms![FormName]![TextBoxName] is null or
forms![FormName]![TextBoxName] = "","*", forms![FormName]![TextBoxName]

)
--
I hope that helped
Good luck


axle said:
Hi

I have a query that uses in the criteria section, text boxes from a form
text box names on the form values are:
Name
Date From
Date To

I want the query to use either the value as per the form
or
if the text box is null i.e. no value entered by the user, then to use a
wildcard i.e. bring everything back

e.g.
If date from is 1Sep05 and Date to is 30Sep05 and Name = SMITH
then the query should bring back all data between dates 1Sep05 and 30Sep05
for people with a name of SMITH

If the Name text box is null on the form, then I want the query to bring
back ALL names between the date range

Getting data back using the date range and when there is a value inthe name
field works fine but I cannot get any data back when the text box is null

Any ideas how to get around this???
Click to expand...
Click to expand...
 
Back
Top