Dates

[Kay]

Hi
I have 2 queries. The second query uses the first query to select the
total jobs for each driver. What I cannot do is to get the query to
prompt for start and end date so that the selected records fall between
the two dates. My first 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]
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
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]
GROUP BY [Driver ID];

and second:
SELECT [Q1].[Driver ID], [Q1].[Date],
Sum([Q1].[Current]+[Q1].[Bookings]+[Q1].[Contracts]) AS Jobs
FROM Q1
WHERE Date Between [Start] and [End]
GROUP BY [Q1].[Driver ID]
ORDER BY [Q1].[Driver ID];

I cannot get the second table to recognise the dates from the first.
Any help will be much appreciated

 

[Kay]

Second Query is as follows:
SELECT [Q1].[Driver ID], MAX([XDate]) AS x_Date, MAX([YDate]) AS
y_Date, MAX([ZDate]) AS z_Date,
Sum([Q1].[Current]+[Q1].[Bookings]+[Q1].[Contracts]) AS Jobs
FROM Q1
GROUP BY [Q1].[Driver ID]
ORDER BY [Q1].[Driver ID];

 

[John Spencer]

Try making your dates either NULL (instead of Zero) or forcing them to be a
date using CDate(0). Access is either making the dates a number value or a
string value since you have given it two data types.

SELECT [Driver ID], MAX([Date]) AS XDate, CDate(0) AS YDate, CDate(0) AS
ZDate,

or

SELECT [Driver ID], MAX([Date]) AS XDate, Null AS YDate, Null AS ZDate,