Parameter query not returning all results

  • Thread starter Thread starter thedrumdoctor
  • Start date Start date
T

thedrumdoctor

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?
 
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.
 
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 said:
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 said:
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?
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Yes, that works well. Thanks again.

Dale Fye said:
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 said:
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 said:
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.



:

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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top