see all in a combo box

[Guest]

I have a form where the user can select a Product from a combo box, a site
from another combo box, then they have to enter a beginning and ending date
in two other fields. This form passes the data to a query then produces the
records asked for. Everything works great. The user now wants the ability to
select all products at a specfic site or a specific product at all sites
within the dates given. Is there an easy way to do this or do I have to write
code? If so what. I was hoping they could just leave the field blank that
they want all values. Any help would be greatly appreciated.

 

[Van T. Dinh]

See The Access Web article:

http://www.mvps.org/access/forms/frm0043.htm

HTH
Van T. Dinh
MVP (Access)

 

[Guest]

I have tried what the article suggested - here is my code in the Row Source
of my request form.

SELECT [Name Of Product] FROM [Product Information] UNION Select Null as
AllChoice, "(All)" as Bogus From [Product Information] ORDER BY [Name Of
Product];

This should then go to the Product information table and retrieve either the
value the user chose or All and all the records in that field.

I am getting an error message "The number of columns in the two selected
tables or queries of a union query do not match"

Thank you for your time

 

[Van T. Dinh]

The selection list of the 2 components of a Union Query must have the _same_
number of Fields.

Try:

SELECT [Name Of Product]
FROM [Product Information]
UNION
SELECT DISTINCT "(All)"
FROM [Product Information]
ORDER BY [Name Of Product]


--
HTH
Van T. Dinh
MVP (Access)

I have tried what the article suggested - here is my code in the Row Source
of my request form.

SELECT [Name Of Product] FROM [Product Information] UNION Select Null as
AllChoice, "(All)" as Bogus From [Product Information] ORDER BY [Name Of
Product];

This should then go to the Product information table and retrieve either the
value the user chose or All and all the records in that field.

I am getting an error message "The number of columns in the two selected
tables or queries of a union query do not match"

Thank you for your time

See The Access Web article:

http://www.mvps.org/access/forms/frm0043.htm

HTH
Van T. Dinh
MVP (Access)



produces
the ability
to to
write

 

[Guest]

Thank you - I am getting closer. This does produce the "All" in the combo
box. This combo box feeds into a parameter query. When the user chooses a
product from the form, that selection pass to the query and chooses a
particular product. What if they want to see all products. Can I make that
"All" pull all products but still respect the other criterea in the query?

The selection list of the 2 components of a Union Query must have the _same_
number of Fields.

Try:

SELECT [Name Of Product]
FROM [Product Information]
UNION
SELECT DISTINCT "(All)"
FROM [Product Information]
ORDER BY [Name Of Product]


--
HTH
Van T. Dinh
MVP (Access)

I have tried what the article suggested - here is my code in the Row Source
of my request form.

SELECT [Name Of Product] FROM [Product Information] UNION Select Null as
AllChoice, "(All)" as Bogus From [Product Information] ORDER BY [Name Of
Product];

This should then go to the Product information table and retrieve either the
value the user chose or All and all the records in that field.

I am getting an error message "The number of columns in the two selected
tables or queries of a union query do not match"

Thank you for your time

See The Access Web article:

http://www.mvps.org/access/forms/frm0043.htm

HTH
Van T. Dinh
MVP (Access)



I have a form where the user can select a Product from a combo box, a site
from another combo box, then they have to enter a beginning and ending
date
in two other fields. This form passes the data to a query then produces
the
records asked for. Everything works great. The user now wants the ability
to
select all products at a specfic site or a specific product at all sites
within the dates given. Is there an easy way to do this or do I have to
write
code? If so what. I was hoping they could just leave the field blank that
they want all values. Any help would be greatly appreciated.

 

[Van T. Dinh]

Post the SQL String of your Parameter Query.

 

[Guest]

I have 4 fields in the reqeust form these fields feed into the parameter
which then produces a report.

The SQL of the Parameter is

SELECT SiteJob.Product, SiteJob.Site, SiteJob.[Input Date],
SiteJob.Location, SiteJob.[Unit of Measure], SiteJob.[Amount used],
SiteJob.[Initial job notes], SiteJob.BeginningApplicationDate,
SiteJob.EndingApplicationDate
FROM SiteJob
WHERE (((SiteJob.Product)=[Forms]![Product Used Request]![Product]) AND
((SiteJob.Site)=[Forms]![Product Used Request]![Site]) AND ((SiteJob.[Input
Date]) Between [Forms]![Product Used Request]![Startdate] And
[Forms]![Product Used Request]![Enddate]));

As previously noted I need the option for all products at a specific site, a
particular product at all sites, and all products at all site.

Thank you for your time

 

[Van T. Dinh]

Try:

....
WHERE ( (SiteJob.Product=[Forms]![Product Used Request]![Product])
OR ([Forms]![Product Used Request]![Product] = "(All)") )
AND ( (SiteJob.Site=[Forms]![Product Used Request]![Site])
OR (Forms]![Product Used Request]![Site] = "(All)") )
AND (SiteJob.[Input Date] Between [Forms]![Product Used Request]![Startdate]
And [Forms]![Product Used Request]![Enddate]);

With the above SQL, you can also have all Products for all sites.

 

[Guest]

I so appreciate your help. I am able to get all products at a selected site
and all roducts at all sites however, when I try to select a specific product
at all sites I receive all products at all sites.

Here is the SQL in my query

SELECT SiteJob.Product, SiteJob.Site, SiteJob.BeginningApplicationDate,
SiteJob.Location, SiteJob.[Unit of Measure], SiteJob.[Amount used],
SiteJob.[Initial job notes], SiteJob.BeginningApplicationDate,
SiteJob.EndingApplicationDate
FROM SiteJob
WHERE (((SiteJob.Product)=[Forms]![Product Used Request]![Product]) OR
((SiteJob.Site)=[Forms]![Product Used Request]![Site]) AND (([Forms]![Product
Used Request]![Product])="(ALL)") OR ((SiteJob.BeginningApplicationDate)
Between [Forms]![Product Used Request]![Startdate] And [Forms]![Product Used
Request]![Enddate]) AND (([Forms]![Product Used Request]![Site])="(ALL)"));

Try:

....
WHERE ( (SiteJob.Product=[Forms]![Product Used Request]![Product])
OR ([Forms]![Product Used Request]![Product] = "(All)") )
AND ( (SiteJob.Site=[Forms]![Product Used Request]![Site])
OR (Forms]![Product Used Request]![Site] = "(All)") )
AND (SiteJob.[Input Date] Between [Forms]![Product Used Request]![Startdate]
And [Forms]![Product Used Request]![Enddate]);

With the above SQL, you can also have all Products for all sites.

--
HTH
Van T. Dinh
MVP (Access)

I have 4 fields in the reqeust form these fields feed into the parameter
which then produces a report.

The SQL of the Parameter is

SELECT SiteJob.Product, SiteJob.Site, SiteJob.[Input Date],
SiteJob.Location, SiteJob.[Unit of Measure], SiteJob.[Amount used],
SiteJob.[Initial job notes], SiteJob.BeginningApplicationDate,
SiteJob.EndingApplicationDate
FROM SiteJob
WHERE (((SiteJob.Product)=[Forms]![Product Used Request]![Product]) AND
((SiteJob.Site)=[Forms]![Product Used Request]![Site]) AND ((SiteJob.[Input
Date]) Between [Forms]![Product Used Request]![Startdate] And
[Forms]![Product Used Request]![Enddate]));

As previously noted I need the option for all products at a specific site, a
particular product at all sites, and all products at all site.

Thank you for your time

 

[Van T. Dinh]

Please re-read my suggestion. You changed the order / bracketing of AND and
OR which is obviously not correct.

In my suggestion, I use indentations & spaces to indicate the grouping and
it must be exactly correct to get the desired result. Basically, it should
be:

.... WHERE
( ( ) OR ( ) ) ' relating to a particular Product OR All Products
AND
( ( ) OR ( ) ) ' relating to a particular Site OR All Sites
AND
( Date criteria )

 

[Guest]

Thank you sooooooooooooooooooo much. It works perfectly. I so appreciate your
assistance.

Please re-read my suggestion. You changed the order / bracketing of AND and
OR which is obviously not correct.

In my suggestion, I use indentations & spaces to indicate the grouping and
it must be exactly correct to get the desired result. Basically, it should
be:

.... WHERE
( ( ) OR ( ) ) ' relating to a particular Product OR All Products
AND
( ( ) OR ( ) ) ' relating to a particular Site OR All Sites
AND
( Date criteria )

--
HTH
Van T. Dinh
MVP (Access)

I so appreciate your help. I am able to get all products at a selected site
and all roducts at all sites however, when I try to select a specific product
at all sites I receive all products at all sites.

Here is the SQL in my query

SELECT SiteJob.Product, SiteJob.Site, SiteJob.BeginningApplicationDate,
SiteJob.Location, SiteJob.[Unit of Measure], SiteJob.[Amount used],
SiteJob.[Initial job notes], SiteJob.BeginningApplicationDate,
SiteJob.EndingApplicationDate
FROM SiteJob
WHERE (((SiteJob.Product)=[Forms]![Product Used Request]![Product]) OR
((SiteJob.Site)=[Forms]![Product Used Request]![Site]) AND (([Forms]![Product
Used Request]![Product])="(ALL)") OR ((SiteJob.BeginningApplicationDate)
Between [Forms]![Product Used Request]![Startdate] And [Forms]![Product Used
Request]![Enddate]) AND (([Forms]![Product Used Request]![Site])="(ALL)"));