RE: Parameter query not returning all results
- From: Dale Fye <dale.fye@xxxxxxxxxx>
- Date: Tue, 21 Oct 2008 04:20:01 -0700
In that case, I think you can get by with that syntax for the [MPC] field only.
--
HTH
Dale
Don''t forget to rate the post if it was helpful!
email address is invalid
Please reply to newsgroup only.
"thedrumdoctor" wrote:
Many thanks, that does make sense. Just to clarify, does the statement:.
NULL OR LIKE
Have to precede every parameter query criteria, or just the fields that it's
possible to contain Null values? For instance, the MPC field is the only
field in the table that is allowed to be empty, all other fields have to have
something in them, even if it's a separating dash.
"Dale Fye" wrote:
If a field contains null values, evaluating it using something like the
following will not return the records that contain NULLs in that field. This
is because a NULL value is not "Like" anything.
[MPC] Like "*" & [Please enter part of the MPC] * "*"
To get whay you want, you will need to modify your criteria to:
NULL or Like [Please enter ...] & "*"
--
HTH
Dale
Don''t forget to rate the post if it was helpful!
email address is invalid
Please reply to newsgroup only.
"thedrumdoctor" wrote:
I have a stock table that I need to run a query on to see if a product
actually exists. The table contains the following fields:
StockNumber (PK)
ManufacturersName
ModelName
ModelVariation
ModelType
Finish
Size
Category
MPC
Discontinued
UnitCost
The query runs against the following fields:
ManufacturersName
ModelName
ModelVariation
ModelType
Finish
Size
Category
MPC
The query criteria on the fields are as follows:
ManufacturersName
Like [please enter a Manufacturer name] & "*"
ModelName
Like [Please enter a model name] & "*"
Size
Like [Please enter a size (if applicable)] & "*"
Category
Like [Please enter a category (if known)] & "*"
MPC
Like "*" & [Please enter part of the MPC if you want to] & "*"
It’s not always necessary to ask for criteria on every field but I was
reckoning that optional queries on 5 fields would be good for honing down
certain products. However, when choose to leave some of the parameter
criteria blank I do get some unexpected results.
Take the manufacturer’s name ‘Pearl’ for instance. If I run a query on the
manufacturer’s name alone, ignoring all the other parameters, I get 169
records returned for the name Pearl.
However, if I create a new parameter query on the ManufacturersName field
only using the Like [please enter a Manufacturer name] & "*" criteria and
enter ‘Pearl’, I get 207 results returned.
On the parameter query that has multiple criteria, I was under the
impression that it wouldn’t matter if criteria was left blank, but obviously
I am wrong. Should I be making use of the ‘Null’ value somewhere to make my
results more accurate if I don’t want to make use of all the criteria on the
multiple parameter query?
- Follow-Ups:
- RE: Parameter query not returning all results
- From: thedrumdoctor
- RE: Parameter query not returning all results
- References:
- Parameter query not returning all results
- From: thedrumdoctor
- RE: Parameter query not returning all results
- From: Dale Fye
- RE: Parameter query not returning all results
- From: thedrumdoctor
- Parameter query not returning all results
- Prev by Date: Date Evaluation
- Next by Date: Re: DMin Built-in function in a Query
- Previous by thread: RE: Parameter query not returning all results
- Next by thread: RE: Parameter query not returning all results
- Index(es):
Relevant Pages
|
