Parameter Query/Double Entry (Access 2003/SP 1)

Topic

New Reply

I have a parameter query that I use to retrieve records between 2 dates. For some reason it wants me to enter the dates twice. If I skip the first 2 parameter windows and enter them in the second set, it will return the data, but if I enter it in the first set and not the last it returns empty. Any ideas?

Reply | Quote

Replies

Could you do the following?
– Open the query in design view.
– Select View | SQL.
– Copy the text you see to the clipboard (Ctrl+C)
– Paste it into a reply.
Thanks in advance.

Reply | Quote

Edited by HansV to correct problems with field name starting with “tr” (it is interpreted as a table row tag by the Lounge software)

Here it is:
SELECT [dataInt Tract Totals].Country, Sum([dataInt Tract Totals].[Tract Totals]) AS [SumOfTract Totals], Sum([dataInt Tract Totals].Packages) AS SumOfPackages, Sum([dataInt Tract Totals].[Letter Count]) AS [SumOfLetter Count], Sum([dataInt Tract Totals].Bibles) AS SumOfBibles, Sum([dataInt Tract Totals].[New Testaments]) AS [SumOfNew Testaments], Sum([dataInt Tract Totals].[Gospel of John]) AS [SumOfGospel of John], Sum([dataInt Tract Totals].Books) AS SumOfBooks, Sum([dataInt Tract Totals].SSQ) AS SumOfSSQ, Sum([dataInt Tract Totals].MOT) AS SumOfMOT
FROM [dataInt Tract Totals]
GROUP BY [dataInt Tract Totals].Country
HAVING ((([dataInt Tract Totals].Country) In (SELECT [Country] FROM [dataInt Tract Totals] As Tmp GROUP BY [Country] HAVING Count(*)>0 )))
ORDER BY Sum([dataInt Tract Totals].[Tract Totals]) DESC;

I made this query probably 2 years ago when I was learning how to use Access. ( I still am) I have it referenced to a query that pulls specific data by date. The query it pulls data from (dataInt Tract Totals) pulls the data I want but I need to know the sum of each class by country and it shows the sum for every date, not by month. I know there’s a solution and I can probably do it in 1 query, but haven’t figured it out.

Here is the SQL view of the original query.
PARAMETERS [Enter beginning date] DateTime, [Enter ending date] DateTime;
SELECT tblInternational_Orders.Country, tblInternational_Orders.[Tract Totals], tblInternational_Orders.Sent, tblInternational_Orders.Packages, tblInternational_Orders.[Letter Count], tblInternational_Orders.Date, tblInternational_Orders.Bibles, tblInternational_Orders.[New Testaments], tblInternational_Orders.[Gospel of John], tblInternational_Orders.Books, tblInternational_Orders.SSQ, tblInternational_Orders.MOT
FROM tblInternational_Orders
WHERE (((tblInternational_Orders.Country) In (SELECT [Country] FROM [tblInternational_orders] As Tmp GROUP BY [Country] HAVING Count(*)>0 )) AND ((tblInternational_Orders.Date) Between [Enter beginning date] And [Enter ending date]))
ORDER BY tblInternational_Orders.[Tract Totals] DESC;

Reply | Quote

If you want to group data by month, you can add two calculated columns to the original query:

Y: Year([Date])

and

M: Month([Date])

You can then make this query into a totals query, and set the Total option for the Date field to Where (this will automatically clear the Show check box for this column).

Reply | Quote

I understand the concept you are talking about, but I’m lost as to how to put it into my query. I’ve seen that in other sample databases but can’t remember where. Is it something like this? Date: Format([Date],”mmmm”)

Reply | Quote

No, exactly what I wrote.

Reply | Quote

That did the trick. Thanks a lot for your help!!

Reply | Quote