Relationship with date to date and time.

  • Thread starter Thread starter James
  • Start date Start date
J

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;
 
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)
 
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")

James said:
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];

Allen Browne said:
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")

James said:
My report will only filter if there is no time and the date is the
Click to expand...
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
Click to expand...
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;
Click to expand...
Click to expand...
Click to expand...
 
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.


Allen Browne said:
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")

James said:
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];

Allen Browne said:
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;
Click to expand...
Click to expand...
Click to expand...
 
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")
James said:
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.


Allen Browne said:
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")

James said:
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
Click to expand...
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;
Click to expand...
Click to expand...
Click to expand...
 
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.


Allen Browne said:
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")
James said:
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
Click to expand...
grouping
or
aggregation.


Allen Browne said:
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,
Click to expand...
if
you
have Calendar.Date, you probably don't want tblEvents.PUDate as well.

Finally, could you use DateValue(tblEvents.PUDate) in the other
Click to expand...
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
Click to expand...
Click to expand...
tell
it
Click to expand...
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;
Click to expand...
Click to expand...
Click to expand...
 
Back
Top