I am trying to create a crosstab query based on inputs selected on a form. One of the key fields is a list of Cities that allows for multiple selections. This data feeds into a crosstab query; however, the existing SQL criteria language (Where clause) pulls all Cities as opposed to the ones I selected in the form. I have highlighted the section that is failing me in red font below. I suppose it should include an IN clause; however, I do not know how to get the multi-list box selections to auto-populate into the criteria language. Any guidance would be most appreciated. Regards, Jeff
PARAMETERS [Forms].[frmClientSearch].[txtFilterMainName] Text ( 255 ), [Forms].[frmClientSearch].[txtFilterCity] Text ( 255 ), [Forms].[frmClientSearch].[txtStartDate] DateTime, [Forms].[frmClientSearch].[txtEndDate] DateTime, [Forms].[frmClientSearch].[txtfilterZip] Text ( 255 ), [Forms].[frmClientSearch].[txtFilterAddKey] Text ( 255 ), [Forms].[frmClientSearch].[cboFilterMinTax] Currency;
TRANSFORM Sum([Tax]/0.14) AS [Alcohol Sales]
SELECT tblClient.MainName AS Restaurant, tblClient.AddressL1 AS Address, tblClient.City, tblClient.Zip, Sum([Tax]/0.14) AS [Total of Alcohol Sales]
FROM tblClient
WHERE (((tblClient.MainName) Like "*" & [Forms].[frmClientSearch].[txtFilterMainName] & "*") AND ((tblClient.City) Like "*" & [Forms].[frmClientSearch].[txtFilterCity] & "*") AND ((tblClient.Zip) Like "*" & [Forms].[frmClientSearch].[txtfilterZip] & "*") AND ((tblClient.Tax)>[Forms].[frmClientSearch].[cboFilterMinTax]) AND ((tblClient.AddressL1) Like "*" & [Forms].[frmClientSearch].[txtFilterAddKey] & "*") AND ((tblClient.EnteredOn) Between [Forms].[frmClientSearch].[txtStartDate] And [Forms].[frmClientSearch].[txtEndDate]))
GROUP BY tblClient.MainName, tblClient.AddressL1, tblClient.City, tblClient.Zip
ORDER BY tblClient.MainName, tblClient.EnteredOn
PIVOT tblClient.EnteredOn;