Query Criteria

[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

 

[Steve Schapel]

Jasper,

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

 

[Jerry Whittle]

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.

 

[Jasper Recto]

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

Jasper,

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

--
Steve Schapel, Microsoft Access MVP

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

 

[Douglas J. Steele]

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!)

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

Jasper,

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

--
Steve Schapel, Microsoft Access MVP

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