input date via calendar control

[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

 

[Arvin Meyer [MVP]]

Try:

BETWEEN [Forms]![YourFormName]![txtStartDate] AND
[Forms]![YourFormName]![txtEndDate]

where txtStartDate and txtEndDate are the names of the textboxes and
YourFormName is the name of your form.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Douglas J Steele]

While Arvin's shown you how to refer to a form control rather than a named
parameter, note that queries in Access don't work well with dates in
dd/mm/yyyy format, regardless of what you may have set your Short Date
format to under Regional Setttings.

My advice would be

BETWEEN Format([Forms]![YourFormName]![txtStartDate], "\#mm\/dd\/yyyy\#")
AND Format([Forms]![YourFormName]![txtEndDate], "\#mm\/dd\/yyyy\#")

You might want to take a look at Allen Browne's "International Dates in
Access" at http://allenbrowne.com/ser-36.html
or what I have in my September 2003 Access Answers column for Pinnacle
Publication's "Smart Access" newsletter. (The column and accompanying
database can be downloaded at
http://www.accessmvp.com/djsteele/SmartAccess.html)