Relationship with date to date and time.

[James]

My report will only filter if there is no time and the date is the same. Is
there a way to put in datevalue([PUDate]) so that it show all of the records
even if there is a time? And then on the frmDriver how do I tell it to show
all of January 2003 etc. is there a way to select the months from a grouped
dropbox list of just month and year. Or do I have to have the start date
and end dates.

SELECT Calendar.CalDate, tblEvents.DODate, tblEvents.PUDate,
tblEvents.TotalHours, tblEvents.rndhours, tblEvents.LimoID,
tblEvents.EmployeeID
FROM Calendar LEFT JOIN tblEvents ON Calendar.CalDate = tblEvents.PUDate
WHERE (((tblEvents.EmployeeID) Is Null Or
(tblEvents.EmployeeID)=[forms]![frmDriver]![cmbDriver]))
GROUP BY Calendar.CalDate, tblEvents.DODate, tblEvents.PUDate,
tblEvents.TotalHours, tblEvents.rndhours, tblEvents.LimoID,
tblEvents.EmployeeID;

 

[Allen Browne]

You can use DateValue() in the JOIN.

Performance will be slower (since Access can't use the index on the PUDate
field), and Access won't be able to show you the query in the graphcial
view, but you can use:
SELECT ... FROM Calendar LEFT JOIN tblEvents ON Calendar.CalDate =
DateValue(tblEvents.PUDate)

 

[Allen Browne]

First up, remove the GROUP BY clause, and verify that the query is working
with the non-equi join without the aggregation.

Once that's working, try fewer fields in the GROUP BY. Particularly, if you
have Calendar.Date, you probably don't want tblEvents.PUDate as well.

Finally, could you use DateValue(tblEvents.PUDate) in the other clauses for
consistency as well?

Something along that line should get you to where you need to go.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Thanks for the help

I put in the datevalue but it gave the same error as when I tried the first
time. "The expression is typed incorecly or is too complex to evaluate..."

SELECT [Calendar].[CalDate], [tblEvents].[DODate], [tblEvents].[PUDate],
[tblEvents].[TotalHours], [tblEvents].[rndhours], [tblEvents].[LimoID],
[tblEvents].[EmployeeID]
FROM Calendar LEFT JOIN tblEvents ON Calendar.CalDate =
DateValue(tblEvents.PUDate)
WHERE ((([tblEvents].[EmployeeID]) Is Null Or
([tblEvents].[EmployeeID])=[forms]![frmDriver]![cmbDriver]))
GROUP BY [Calendar].[CalDate], [tblEvents].[DODate], [tblEvents].[PUDate],
[tblEvents].[TotalHours], [tblEvents].[rndhours], [tblEvents].[LimoID],
[tblEvents].[EmployeeID];

You can use DateValue() in the JOIN.

Performance will be slower (since Access can't use the index on the PUDate
field), and Access won't be able to show you the query in the graphcial
view, but you can use:
SELECT ... FROM Calendar LEFT JOIN tblEvents ON Calendar.CalDate =
DateValue(tblEvents.PUDate)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

My report will only filter if there is no time and the date is the

same.
Is

there a way to put in datevalue([PUDate]) so that it show all of the records
even if there is a time? And then on the frmDriver how do I tell it

to
show

all of January 2003 etc. is there a way to select the months from a grouped
dropbox list of just month and year. Or do I have to have the start date
and end dates.

SELECT Calendar.CalDate, tblEvents.DODate, tblEvents.PUDate,
tblEvents.TotalHours, tblEvents.rndhours, tblEvents.LimoID,
tblEvents.EmployeeID
FROM Calendar LEFT JOIN tblEvents ON Calendar.CalDate = tblEvents.PUDate
WHERE (((tblEvents.EmployeeID) Is Null Or
(tblEvents.EmployeeID)=[forms]![frmDriver]![cmbDriver]))
GROUP BY Calendar.CalDate, tblEvents.DODate, tblEvents.PUDate,
tblEvents.TotalHours, tblEvents.rndhours, tblEvents.LimoID,
tblEvents.EmployeeID;

 

[James]

It gave me this message when I took out the Group By statement. HAVING
(((tblEvents.EmployeeID) Is Null Or
(tblEvents.EmployeeID)=[forms]![frmDriver]![cmbDriver])) without grouping or
aggregation.

First up, remove the GROUP BY clause, and verify that the query is working
with the non-equi join without the aggregation.

Once that's working, try fewer fields in the GROUP BY. Particularly, if you
have Calendar.Date, you probably don't want tblEvents.PUDate as well.

Finally, could you use DateValue(tblEvents.PUDate) in the other clauses for
consistency as well?

Something along that line should get you to where you need to go.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Thanks for the help

I put in the datevalue but it gave the same error as when I tried the first
time. "The expression is typed incorecly or is too complex to evaluate..."

SELECT [Calendar].[CalDate], [tblEvents].[DODate], [tblEvents].[PUDate],
[tblEvents].[TotalHours], [tblEvents].[rndhours], [tblEvents].[LimoID],
[tblEvents].[EmployeeID]
FROM Calendar LEFT JOIN tblEvents ON Calendar.CalDate =
DateValue(tblEvents.PUDate)
WHERE ((([tblEvents].[EmployeeID]) Is Null Or
([tblEvents].[EmployeeID])=[forms]![frmDriver]![cmbDriver]))
GROUP BY [Calendar].[CalDate], [tblEvents].[DODate], [tblEvents].[PUDate],
[tblEvents].[TotalHours], [tblEvents].[rndhours], [tblEvents].[LimoID],
[tblEvents].[EmployeeID];

You can use DateValue() in the JOIN.

Performance will be slower (since Access can't use the index on the PUDate
field), and Access won't be able to show you the query in the graphcial
view, but you can use:
SELECT ... FROM Calendar LEFT JOIN tblEvents ON Calendar.CalDate =
DateValue(tblEvents.PUDate)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

My report will only filter if there is no time and the date is the same.
Is
there a way to put in datevalue([PUDate]) so that it show all of the
records
even if there is a time? And then on the frmDriver how do I tell it to
show
all of January 2003 etc. is there a way to select the months from a
grouped
dropbox list of just month and year. Or do I have to have the start date
and end dates.

SELECT Calendar.CalDate, tblEvents.DODate, tblEvents.PUDate,
tblEvents.TotalHours, tblEvents.rndhours, tblEvents.LimoID,
tblEvents.EmployeeID
FROM Calendar LEFT JOIN tblEvents ON Calendar.CalDate = tblEvents.PUDate
WHERE (((tblEvents.EmployeeID) Is Null Or
(tblEvents.EmployeeID)=[forms]![frmDriver]![cmbDriver]))
GROUP BY Calendar.CalDate, tblEvents.DODate, tblEvents.PUDate,
tblEvents.TotalHours, tblEvents.rndhours, tblEvents.LimoID,
tblEvents.EmployeeID;

 

[Allen Browne]

Yes, James, the parts of the HAVING clause will have to be dropped or moved
into the WHERE clause when there is no GROUP BY

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

It gave me this message when I took out the Group By statement. HAVING
(((tblEvents.EmployeeID) Is Null Or
(tblEvents.EmployeeID)=[forms]![frmDriver]![cmbDriver])) without grouping or
aggregation.

First up, remove the GROUP BY clause, and verify that the query is working
with the non-equi join without the aggregation.

Once that's working, try fewer fields in the GROUP BY. Particularly, if you
have Calendar.Date, you probably don't want tblEvents.PUDate as well.

Finally, could you use DateValue(tblEvents.PUDate) in the other clauses for
consistency as well?

Something along that line should get you to where you need to go.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Thanks for the help

I put in the datevalue but it gave the same error as when I tried the first
time. "The expression is typed incorecly or is too complex to evaluate..."

SELECT [Calendar].[CalDate], [tblEvents].[DODate], [tblEvents].[PUDate],
[tblEvents].[TotalHours], [tblEvents].[rndhours], [tblEvents].[LimoID],
[tblEvents].[EmployeeID]
FROM Calendar LEFT JOIN tblEvents ON Calendar.CalDate =
DateValue(tblEvents.PUDate)
WHERE ((([tblEvents].[EmployeeID]) Is Null Or
([tblEvents].[EmployeeID])=[forms]![frmDriver]![cmbDriver]))
GROUP BY [Calendar].[CalDate], [tblEvents].[DODate], [tblEvents].[PUDate],
[tblEvents].[TotalHours], [tblEvents].[rndhours], [tblEvents].[LimoID],
[tblEvents].[EmployeeID];

You can use DateValue() in the JOIN.

Performance will be slower (since Access can't use the index on the PUDate
field), and Access won't be able to show you the query in the graphcial
view, but you can use:
SELECT ... FROM Calendar LEFT JOIN tblEvents ON Calendar.CalDate =
DateValue(tblEvents.PUDate)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

My report will only filter if there is no time and the date is the same.
Is
there a way to put in datevalue([PUDate]) so that it show all of the
records
even if there is a time? And then on the frmDriver how do I tell

it
to

show
all of January 2003 etc. is there a way to select the months from a
grouped
dropbox list of just month and year. Or do I have to have the start
date
and end dates.

SELECT Calendar.CalDate, tblEvents.DODate, tblEvents.PUDate,
tblEvents.TotalHours, tblEvents.rndhours, tblEvents.LimoID,
tblEvents.EmployeeID
FROM Calendar LEFT JOIN tblEvents ON Calendar.CalDate = tblEvents.PUDate
WHERE (((tblEvents.EmployeeID) Is Null Or
(tblEvents.EmployeeID)=[forms]![frmDriver]![cmbDriver]))
GROUP BY Calendar.CalDate, tblEvents.DODate, tblEvents.PUDate,
tblEvents.TotalHours, tblEvents.rndhours, tblEvents.LimoID,
tblEvents.EmployeeID;

 

[James]

I changed the HAVING to WHERE and the query runs. But I can't use
DateValue() still. I made the pudate in the select statement and the value
just shows #Error. But it won't let me add the DateValue() to the FROM...ON
Calendar.CalDate = DateValue(tblEvents.PUDate)? I'm sorry that this is so
dificult.

Yes, James, the parts of the HAVING clause will have to be dropped or moved
into the WHERE clause when there is no GROUP BY

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

It gave me this message when I took out the Group By statement. HAVING
(((tblEvents.EmployeeID) Is Null Or
(tblEvents.EmployeeID)=[forms]![frmDriver]![cmbDriver])) without

grouping

or
aggregation.

First up, remove the GROUP BY clause, and verify that the query is working
with the non-equi join without the aggregation.

Once that's working, try fewer fields in the GROUP BY. Particularly,

if
you

have Calendar.Date, you probably don't want tblEvents.PUDate as well.

Finally, could you use DateValue(tblEvents.PUDate) in the other

clauses
for

consistency as well?

Something along that line should get you to where you need to go.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Thanks for the help

I put in the datevalue but it gave the same error as when I tried the
first
time. "The expression is typed incorecly or is too complex to
evaluate..."

SELECT [Calendar].[CalDate], [tblEvents].[DODate], [tblEvents].[PUDate],
[tblEvents].[TotalHours], [tblEvents].[rndhours], [tblEvents].[LimoID],
[tblEvents].[EmployeeID]
FROM Calendar LEFT JOIN tblEvents ON Calendar.CalDate =
DateValue(tblEvents.PUDate)
WHERE ((([tblEvents].[EmployeeID]) Is Null Or
([tblEvents].[EmployeeID])=[forms]![frmDriver]![cmbDriver]))
GROUP BY [Calendar].[CalDate], [tblEvents].[DODate], [tblEvents].[PUDate],
[tblEvents].[TotalHours], [tblEvents].[rndhours], [tblEvents].[LimoID],
[tblEvents].[EmployeeID];

You can use DateValue() in the JOIN.

Performance will be slower (since Access can't use the index on the
PUDate
field), and Access won't be able to show you the query in the graphcial
view, but you can use:
SELECT ... FROM Calendar LEFT JOIN tblEvents ON Calendar.CalDate =
DateValue(tblEvents.PUDate)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

My report will only filter if there is no time and the date is the
same.
Is
there a way to put in datevalue([PUDate]) so that it show all of the
records
even if there is a time? And then on the frmDriver how do I

tell

it

from

a

grouped
dropbox list of just month and year. Or do I have to have the start
date
and end dates.

SELECT Calendar.CalDate, tblEvents.DODate, tblEvents.PUDate,
tblEvents.TotalHours, tblEvents.rndhours, tblEvents.LimoID,
tblEvents.EmployeeID
FROM Calendar LEFT JOIN tblEvents ON Calendar.CalDate =
tblEvents.PUDate
WHERE (((tblEvents.EmployeeID) Is Null Or
(tblEvents.EmployeeID)=[forms]![frmDriver]![cmbDriver]))
GROUP BY Calendar.CalDate, tblEvents.DODate, tblEvents.PUDate,
tblEvents.TotalHours, tblEvents.rndhours, tblEvents.LimoID,
tblEvents.EmployeeID;