Query Criteria

  • Thread starter Thread starter Jasper Recto
  • Start date Start date
J

Jasper Recto

I have a query that grabs the date from a form and uses that as a filter
criteria.

I would also like to use a another field from that form, such as the vendor
name, to use as a filter criteria.

However, If the user does not enter in a vendor name than I would like it to
run the query for all vendors. If the user enters in the vendor, I want it
just for that vendor.

Is it possible to setup a query so that if a field is blank, it will
continue with the query. If it is not blank, than it will use that value
for the query.

Any ideas?

Thanks,
Jasper
 
Jasper,

One way os to enter the critewria like this:
[Forms]![YourForm]![Vendor] Or [Forms]![YourForm]![Vendor] Is Null
 
Something like:

Like "*" & [Forms]![frmParameter2]![txtParameter] & "*"

There are some risks though. For example if you have a vendor named "ABLE"
and another one named "CAIN and ABLE", both would be returned if you typed in
just ABLE.
 
Steve,

I tried this but it gave me a syntax error so I put it like this:
[Forms]![YourForm]![Vendor] Or Is Null([Forms]![YourForm]![Vendor])

This ran well if I put a Vendor in the criteria. However, if I left the
Vendor out, it ran but there was no data.

Any ideas?

Thanks,
Jasper

Steve Schapel said:
Jasper,

One way os to enter the critewria like this:
[Forms]![YourForm]![Vendor] Or [Forms]![YourForm]![Vendor] Is Null

--
Steve Schapel, Microsoft Access MVP

Jasper said:
I have a query that grabs the date from a form and uses that as a filter
criteria.

I would also like to use a another field from that form, such as the
vendor name, to use as a filter criteria.

However, If the user does not enter in a vendor name than I would like it
to run the query for all vendors. If the user enters in the vendor, I
want it just for that vendor.

Is it possible to setup a query so that if a field is blank, it will
continue with the query. If it is not blank, than it will use that value
for the query.

Any ideas?

Thanks,
Jasper
Click to expand...
Click to expand...
 
No, it has to be

[Forms]![YourForm]![Vendor] Or ([Forms]![YourForm]![Vendor] IS NULL)

Note that what Steve suggested would be what you'd type into the Criteria
cell in the query builder.

If you're looking at the SQL, you'll see something like:

WHERE (MyField = [Forms]![YourForm]![Vendor]) Or
([Forms]![YourForm]![Vendor] IS NULL)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Jasper Recto said:
Steve,

I tried this but it gave me a syntax error so I put it like this:
[Forms]![YourForm]![Vendor] Or Is Null([Forms]![YourForm]![Vendor])

This ran well if I put a Vendor in the criteria. However, if I left the
Vendor out, it ran but there was no data.

Any ideas?

Thanks,
Jasper

Steve Schapel said:
Jasper,

One way os to enter the critewria like this:
[Forms]![YourForm]![Vendor] Or [Forms]![YourForm]![Vendor] Is Null

--
Steve Schapel, Microsoft Access MVP

Jasper said:
I have a query that grabs the date from a form and uses that as a filter
criteria.

I would also like to use a another field from that form, such as the
vendor name, to use as a filter criteria.

However, If the user does not enter in a vendor name than I would like
it to run the query for all vendors. If the user enters in the vendor,
I want it just for that vendor.

Is it possible to setup a query so that if a field is blank, it will
continue with the query. If it is not blank, than it will use that
value for the query.

Any ideas?

Thanks,
Jasper
Click to expand...
Click to expand...
Click to expand...
 
Back
Top