Parameter Query Question

[Guest]

Hi There,

I have a simple data base for icecream that contains several products (5)
and several flavours (7). These are in seperate colums in the QBE grid along
with other criteria that does not need sorting. There are many different
conbinations of products and flavours in stock.

I want to write a parameter query that prompts me to enter the product name
and then prompts me to enter the flavour.

This is simple enough I know but the catch is that I want the query to show
"all" products or "all" flavours if the prompt is left blank and "entered"
over.

This way I can quickly sort by all products with a defined flavour or search
for a defined product in all flavours or just hit enter twice and all
products and all flavours will be returned.

This is linked to a report and this is a great way to quickly respond to
customer stock enquiries or send out specific stock sheets to customers
without having to write a specific parameter quesry each time I search.

Can any one help?:

Thanks
JohnG

 

[Wolfgang Kais]

Hello John.

Hi There,

I have a simple data base for icecream that contains several products
(5) and several flavours (7). These are in seperate colums in the QBE
grid along with other criteria that does not need sorting. There are
many different conbinations of products and flavours in stock.

I want to write a parameter query that prompts me to enter the product
name and then prompts me to enter the flavour.

This is simple enough I know but the catch is that I want the query to
show "all" products or "all" flavours if the prompt is left blank and
"entered" over.

This way I can quickly sort by all products with a defined flavour or
search for a defined product in all flavours or just hit enter twice
and all products and all flavours will be returned.

This is linked to a report and this is a great way to quickly respond
to customer stock enquiries or send out specific stock sheets to
customers without having to write a specific parameter quesr
each time I search.

Can any one help?:

How about something like this:
PARAMETERS [Enter product] Text ( 255 ), [Enter flavour] Text ( 255 );
SELECT products.product, flavours.flavour
FROM products INNER JOIN flavours ON products.id = flavours.productid
WHERE (((products.product=[Enter product]) OR ([Enter product] IS NULL))
AND ((flavours.flavour=[Enter flavour]) OR [Enter flavour] IS NULL));

 

[Allen Browne]

Wolfgang, your answer should work, but there is a bug in Access that means
it will fail.

In essence, if you declare a parameter of type Text and don't supply any
value, JET fails to recognise it as Null. Details:
Parameter of type Text is evaluated wrongly
at:
http://allenbrowne.com/bug-13.html

JohnG, try Wolfgang's suggestion without the PARAMETERS line, i.e. start
from the SELECT.

For an alternative approach, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello John.

Hi There,

I have a simple data base for icecream that contains several products
(5) and several flavours (7). These are in seperate colums in the QBE
grid along with other criteria that does not need sorting. There are
many different conbinations of products and flavours in stock.

I want to write a parameter query that prompts me to enter the product
name and then prompts me to enter the flavour.

This is simple enough I know but the catch is that I want the query to
show "all" products or "all" flavours if the prompt is left blank and
"entered" over.

This way I can quickly sort by all products with a defined flavour or
search for a defined product in all flavours or just hit enter twice
and all products and all flavours will be returned.

This is linked to a report and this is a great way to quickly respond
to customer stock enquiries or send out specific stock sheets to
customers without having to write a specific parameter quesr
each time I search.

Can any one help?:

How about something like this:
PARAMETERS [Enter product] Text ( 255 ), [Enter flavour] Text ( 255 );
SELECT products.product, flavours.flavour
FROM products INNER JOIN flavours ON products.id = flavours.productid
WHERE (((products.product=[Enter product]) OR ([Enter product] IS NULL))
AND ((flavours.flavour=[Enter flavour]) OR [Enter flavour] IS NULL));

 

[ajetrumpet]

This is simple enough I know but the catch is that I want the query to show "all" products or "all" flavours if the prompt is left blank and "entered" over.

Try this in your query....

SELECT ()
FROM ()
WHERE (product field name=[product parameter] or [product parameter] is null) AND
(flavour field name=[flavour parameter] or [flavour parameter] is null);

When parameters are entered as criteria in the query grid view they are connected by the AND operator by default. If you add the OR operator to it, you can get all the records when no parameters are specified. I believe the "Nz" function does this as well, although that is not its purpose... =)

 

[Wolfgang Kais]

Hello Allen.

Wolfgang, your answer should work, but there is a bug in Access that
means it will fail.

In essence, if you declare a parameter of type Text and don't supply
any value, JET fails to recognise it as Null. Details:
Parameter of type Text is evaluated wrongly at:
http://allenbrowne.com/bug-13.html

The bug you found does not apply to my sample query, because the bug
seems to only apply when retrieving a parameter from a form. When
leaving the "Enter Parameter Value" box blank, everything's fine.

JohnG, try Wolfgang's suggestion without the PARAMETERS line, i.e. start
from the SELECT.

For an alternative approach, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello John.

Hi There,

I have a simple data base for icecream that contains several products
(5) and several flavours (7). These are in seperate colums in the QBE
grid along with other criteria that does not need sorting. There are
many different conbinations of products and flavours in stock.

I want to write a parameter query that prompts me to enter the product
name and then prompts me to enter the flavour.

This is simple enough I know but the catch is that I want the query to
show "all" products or "all" flavours if the prompt is left blank and
"entered" over.

This way I can quickly sort by all products with a defined flavour or
search for a defined product in all flavours or just hit enter twice
and all products and all flavours will be returned.

This is linked to a report and this is a great way to quickly respond
to customer stock enquiries or send out specific stock sheets to
customers without having to write a specific parameter quesr
each time I search.

Can any one help?:

How about something like this:
PARAMETERS [Enter product] Text ( 255 ), [Enter flavour] Text ( 255 );
SELECT products.product, flavours.flavour
FROM products INNER JOIN flavours ON products.id = flavours.productid
WHERE (((products.product=[Enter product]) OR ([Enter product] IS NULL))
AND ((flavours.flavour=[Enter flavour]) OR [Enter flavour] IS NULL));

 

[Allen Browne]

Wolfgang, you are right. Thanks.

The bug applies only when JET derives the value for the parameter from the
Expression Service.

I will modify the article on the bug.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello Allen.

Wolfgang, your answer should work, but there is a bug in Access that
means it will fail.

In essence, if you declare a parameter of type Text and don't supply
any value, JET fails to recognise it as Null. Details:
Parameter of type Text is evaluated wrongly at:
http://allenbrowne.com/bug-13.html

The bug you found does not apply to my sample query, because the bug
seems to only apply when retrieving a parameter from a form. When
leaving the "Enter Parameter Value" box blank, everything's fine.

JohnG, try Wolfgang's suggestion without the PARAMETERS line, i.e. start
from the SELECT.

For an alternative approach, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

Hello John.

:
Hi There,

I have a simple data base for icecream that contains several products
(5) and several flavours (7). These are in seperate colums in the QBE
grid along with other criteria that does not need sorting. There are
many different conbinations of products and flavours in stock.

I want to write a parameter query that prompts me to enter the product
name and then prompts me to enter the flavour.

This is simple enough I know but the catch is that I want the query to
show "all" products or "all" flavours if the prompt is left blank and
"entered" over.

This way I can quickly sort by all products with a defined flavour or
search for a defined product in all flavours or just hit enter twice
and all products and all flavours will be returned.

This is linked to a report and this is a great way to quickly respond
to customer stock enquiries or send out specific stock sheets to
customers without having to write a specific parameter quesr
each time I search.

Can any one help?:
How about something like this:
PARAMETERS [Enter product] Text ( 255 ), [Enter flavour] Text ( 255 );
SELECT products.product, flavours.flavour
FROM products INNER JOIN flavours ON products.id = flavours.productid
WHERE (((products.product=[Enter product]) OR ([Enter product] IS NULL))
AND ((flavours.flavour=[Enter flavour]) OR [Enter flavour] IS NULL));