input date in query

K

Kay

Hi

I have a query that requests a from and a to date from the user and
then finds records that fall between the 2 dates. I have also got a
calendar control in my database. How can I change my query so that
instead of a box appearing to request the date, the calendar contol
appears to request the from and to date as this makes it much easier to
select a date for the user.

My query is as follows
SELECT [Driver ID], MAX([Date]) AS XDate, 0 AS YDate, 0 AS ZDate,
COUNT(*) AS Current, 0 AS Bookings, 0 AS Contracts
FROM [Current Jobs]
WHERE [Date] BETWEEN [Start Date (dd/mm/yyyy) ] AND [End Date
(dd/mm/yyyy) ]
GROUP BY [Driver ID]

UNION ALL
SELECT [Driver ID], 0 AS XDate, MAX([Date]) AS YDate, 0 AS ZDate,
0 AS Current, COUNT(*) AS Bookings, 0 AS Contracts
FROM Bookings
WHERE [Date] BETWEEN [Start Date (dd/mm/yyyy) ] AND [End Date
(dd/mm/yyyy) ]
GROUP BY [Driver ID]

UNION ALL
SELECT [Driver ID], 0 AS XDate, 0 AS YDate, MAX([Date]) AS ZDate,
0 AS Current, 0 AS Bookings, COUNT(*) AS Contracts
FROM [Contract Jobs]
WHERE [Date] BETWEEN [Start Date (dd/mm/yyyy) ] AND [End Date
(dd/mm/yyyy) ]
GROUP BY [Driver ID];

Can anyone help please
 
A

Allen Browne

You need to create a form where the query can read the date from.

If the form is named Form1, and the calendar controls are Cal1 and Cal2, you
would change your query to read:
WHERE [Date] Between [Forms].[Form1].[Cal1] And [Forms].[Form1].[Cal2]


Try the first method in this article:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top