Parameter query not returning all results

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?
 
D

Dale Fye

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.
 
T

thedrumdoctor

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...
 
D

Dale Fye

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...
 
T

thedrumdoctor

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Parameter Query w/ search form 3
Pls Help - "between" Criterea returning no results 11
Aggregate calculations on calculated query fields? 6
Access 2003 query criteria issue.... 3
Use In() Expression as Query criteria with guts from Function 1
Parameter Query No Results - Needs to Auto Create Record 4
Has Hierarchy the Parameter between years? 5
Question about criteria 2

Top