Query using Combo Box

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

How do I make a query that you use a combo box to select
the criteria, the combo box is using a table of names..
 
You'll have to put the combo box on a form. If the info
for the combo is in a table, link it to the field in the
table (use the wizard if you haven't bound an object to a
table/field before). Once you do that, you will have your
list in the combo box. You'll have to build a predefined
query that utilizes the selection (watch your code here-
see my sample below) or build the query in the report_open
event of the report. If you have access to the Developers
Solution, you can get a very good idea of how to do this
from that. Below is a query that pulls it's info from a
form (note the use of the !'s & []'s). The report actually
calls this query to fill the pages from the report_open
event.

PARAMETERS Forms!MultiSiteMrB!txtVend Text ( 255 ), Forms!
MultiSiteMrB!txtCust Text ( 255 ), Forms!MultiSiteMrB!
txtStYr Short, Forms!MultiSiteMrB!txtEndYr Short, Forms!
MultiSiteMrB!txtTimePrd Text ( 255 ), Forms!MultiSiteMrB!
txtStrPrd Short, Forms!MultiSiteMrB!txtEndPrd Short, Forms!
MultiSiteMrB!txtStrLoc Text ( 255 ), Forms!MultiSiteMrB!
txtEndLoc Text ( 255 );
TRANSFORM Sum(Totalpounds.TotWgt) AS Wgt
SELECT Totalpounds.Vnd, Totalpounds.Cust,
Totalpounds.Item, DatePart("yyyy",[SHIPDATE]) AS Yr,
DatePart([Forms]![MultiSiteMrB]![txtTimePrd],[SHIPDATE])
AS Period
FROM Totalpounds
WHERE (((UCase([Totalpounds].[Vnd]))=UCase([Forms]!
[MultiSiteMrB]![txtVend])) AND ((UCase(Left([Totalpounds].
[Cust],5)))=UCase(Left([Forms]![MultiSiteMrB]!
[txtCust],5))) AND ((DatePart("yyyy",[SHIPDATE])) Between
[Forms]![MultiSiteMrB]![txtStYr] And [Forms]!
[MultiSiteMrB]![txtEndYr]) AND ((DatePart([Forms]!
[MultiSiteMrB]![txtTimePrd],[SHIPDATE])) Between [Forms]!
[MultiSiteMrB]![txtStrPrd] And [Forms]![MultiSiteMrB]!
[txtEndPrd]) AND ((UCase(Left([Totalpounds].[Loc],5)))
Between UCase(Left([Forms]![MultiSiteMrB]![txtStrLoc],5))
And UCase(Left([Forms]![MultiSiteMrB]![txtEndLoc],5))))
GROUP BY Totalpounds.Vnd, Totalpounds.Cust,
Totalpounds.Item, DatePart("yyyy",[SHIPDATE]), DatePart
([Forms]![MultiSiteMrB]![txtTimePrd],[SHIPDATE])
ORDER BY Totalpounds.Vnd, Totalpounds.Cust,
Totalpounds.Item, DatePart("yyyy",[SHIPDATE]), DatePart
([Forms]![MultiSiteMrB]![txtTimePrd],[SHIPDATE])
PIVOT Left([Totalpounds].[Loc],9);
 
Back
Top