Trouble with Dates

[nutmeg]

Good morning,

I am trying to make a query to build a graph that will be a sub report. It
runs from a Print Dialogue Box. I have a table called tblCalls which has the
Date the call came in and the Call Category i.e. Appt Booked. I was trying
to group by the Date and by Category for graph results. Because Date field
is General format is has a time attached to each call and so when I tried to
group doesn't work so I tried to format the Date field to DayofMonth:
Format([Date],"Medium Date") to match the criteria format, didn't work, gives
me all sorts of dates that aren't even in the date range that I'm asking for:
01/01/05 - 01/08/05. I get things from 2004 and 2003.

Date field is table is formatted as "dddd ","mmmm d"," yyyy hh:nn ampm
Format for Criteria in query is Medium Date:

=[Forms]![ME-SLB15-sfrmPrintDialogueBox]![txtBeginningDate] And

<DateAdd("d",1,[Forms]![ME-SLB15-sfrmPrintDialogueBox]![txtEndingDate])
Regional Settings is: Short Date

I may be making this too complicated. What I'm actually trying to do is
count all the records for a specific time frame i.e. 309, and then count the
number of Appts Booked. What I'm trying to do is build a chart that shows
each line separately so I thought that I can to build a query to do this.
Can anyone give me some guidance.
Thank you,
IEJ

 

[Steve Schapel]

IEJ,

I am assuming your [Date] field is actually a Date/Time data type, as
defined in the table design.
You should be able to put a calculated field in your query like this...
DayofMonth: DateValue([Date])
.... and use this for the grouping for your graph.

As for getting data outside the date range criteria, are
[txtBeginningDate] and [txtEndingDate] unbound textboxes on your form?
Do they have their Format property set to a date format? I think this
is a separate problem, unrelated to the issue of getting discrete data
values.

It is helpful to remember that the specific format of a date
field/control is not normally relevant to these type of problems, as
this only relates to the way the data is displayed, not to its value,
and it is normally its value you are concerned with, not its appearance.

--
Steve Schapel, Microsoft Access MVP

Good morning,

I am trying to make a query to build a graph that will be a sub report. It
runs from a Print Dialogue Box. I have a table called tblCalls which has the
Date the call came in and the Call Category i.e. Appt Booked. I was trying
to group by the Date and by Category for graph results. Because Date field
is General format is has a time attached to each call and so when I tried to
group doesn't work so I tried to format the Date field to DayofMonth:
Format([Date],"Medium Date") to match the criteria format, didn't work, gives
me all sorts of dates that aren't even in the date range that I'm asking for:
01/01/05 - 01/08/05. I get things from 2004 and 2003.

Date field is table is formatted as "dddd ","mmmm d"," yyyy hh:nn ampm
Format for Criteria in query is Medium Date:

=[Forms]![ME-SLB15-sfrmPrintDialogueBox]![txtBeginningDate] And

<DateAdd("d",1,[Forms]![ME-SLB15-sfrmPrintDialogueBox]![txtEndingDate])
Regional Settings is: Short Date

I may be making this too complicated. What I'm actually trying to do is
count all the records for a specific time frame i.e. 309, and then count the
number of Appts Booked. What I'm trying to do is build a chart that shows
each line separately so I thought that I can to build a query to do this.
Can anyone give me some guidance.
Thank you,
IEJ

 

[nutmeg]

Thank you. Yes, correct it is a Date/Time field. I added the DayofMonth
field to the query and tried to group on it and received an error box saying
'Data Type Mismatch in Criteria Expression', and it won't allow me to group
on the date at all. May I ask for some more guidance.

Thank you,
IEJ

IEJ,

I am assuming your [Date] field is actually a Date/Time data type, as
defined in the table design.
You should be able to put a calculated field in your query like this...
DayofMonth: DateValue([Date])
.... and use this for the grouping for your graph.

As for getting data outside the date range criteria, are
[txtBeginningDate] and [txtEndingDate] unbound textboxes on your form?
Do they have their Format property set to a date format? I think this
is a separate problem, unrelated to the issue of getting discrete data
values.

It is helpful to remember that the specific format of a date
field/control is not normally relevant to these type of problems, as
this only relates to the way the data is displayed, not to its value,
and it is normally its value you are concerned with, not its appearance.

--
Steve Schapel, Microsoft Access MVP

Good morning,

I am trying to make a query to build a graph that will be a sub report. It
runs from a Print Dialogue Box. I have a table called tblCalls which has the
Date the call came in and the Call Category i.e. Appt Booked. I was trying
to group by the Date and by Category for graph results. Because Date field
is General format is has a time attached to each call and so when I tried to
group doesn't work so I tried to format the Date field to DayofMonth:
Format([Date],"Medium Date") to match the criteria format, didn't work, gives
me all sorts of dates that aren't even in the date range that I'm asking for:
01/01/05 - 01/08/05. I get things from 2004 and 2003.

Date field is table is formatted as "dddd ","mmmm d"," yyyy hh:nn ampm
Format for Criteria in query is Medium Date:

=[Forms]![ME-SLB15-sfrmPrintDialogueBox]![txtBeginningDate] And

<DateAdd("d",1,[Forms]![ME-SLB15-sfrmPrintDialogueBox]![txtEndingDate])
Regional Settings is: Short Date

I may be making this too complicated. What I'm actually trying to do is
count all the records for a specific time frame i.e. 309, and then count the
number of Appts Booked. What I'm trying to do is build a chart that shows
each line separately so I thought that I can to build a query to do this.
Can anyone give me some guidance.
Thank you,
IEJ

 

[Steve Schapel]

IEJ,

Can you please post back with the SQL view of the query (via the View
menu from the query design window)? Thanks.

 

[nutmeg]

Hello, thank you. I have attached the code. I had changed the dates to what
is shown instead of the parameter names i.e.

=[Forms]![ME-SLB15-sfrmPrintDialogueBox]![txtBeginningDate] And <DateAdd("d",1,[Forms]![ME-SLB15-sfrmPrintDialogueBox]![txtEndingDate])

SELECT tblCalls.Date, Count(tblCalls.Date) AS CountOfDate,
tblCalls.CategoryID, tblCallCategories.CategoryDescr, DateValue([Date]) AS
DateofMonth
FROM tblCallCategories INNER JOIN tblCalls ON tblCallCategories.CategoryID =
tblCalls.CategoryID
GROUP BY tblCalls.Date, tblCalls.CategoryID,
tblCallCategories.CategoryDescr, DateValue([Date])
HAVING (((DateValue([Date]))>="01/05/05" And (DateValue([Date]))<="01/08/05"))
ORDER BY tblCalls.Date;

IEJ

 

[Steve Schapel]

IEJ,

Well, since you have the [Date] field in the query, and are Grouping By
this field, and since you have already told us that this field includes
a Time component, then each grouping will be unique, so the end result
will be the same as if you did no grouping at all. Unless I am
misunderstanding your purpose, I think you need to remove this field
from the query. Also, I would have expected you would want to Group By
the DateOfMonth before Grouping By the Category... No?

The reason you got a "type mismatch" error is because the correct
delimiter for date values is # not ".

So, probably something more like this?...

SELECT DateValue([Date]) AS DateofMonth, tblCalls.CategoryID,
tblCallCategories.CategoryDescr, Count(tblCalls.[Date]) AS CountOfDate
FROM tblCallCategories INNER JOIN tblCalls ON
tblCallCategories.CategoryID = tblCalls.CategoryID
GROUP BY DateValue([Date], tblCalls.CategoryID,
tblCallCategories.CategoryDescr
HAVING (DateValue([Date]) Between #01/05/05# And #01/08/05#);

--
Steve Schapel, Microsoft Access MVP

Hello, thank you. I have attached the code. I had changed the dates to what
is shown instead of the parameter names i.e.

=[Forms]![ME-SLB15-sfrmPrintDialogueBox]![txtBeginningDate] And <DateAdd("d",1,[Forms]![ME-SLB15-sfrmPrintDialogueBox]![txtEndingDate])

SELECT tblCalls.Date, Count(tblCalls.Date) AS CountOfDate,
tblCalls.CategoryID, tblCallCategories.CategoryDescr, DateValue([Date]) AS
DateofMonth
FROM tblCallCategories INNER JOIN tblCalls ON tblCallCategories.CategoryID =
tblCalls.CategoryID
GROUP BY tblCalls.Date, tblCalls.CategoryID,
tblCallCategories.CategoryDescr, DateValue([Date])
HAVING (((DateValue([Date]))>="01/05/05" And (DateValue([Date]))<="01/08/05"))
ORDER BY tblCalls.Date;

 

[nutmeg]

Good morning, yes you are correct, it is a date/time field, and quite right I
should have removed the other Date so have done that now. I am still having
difficulty though. I have changed the date field to be the
DateofMonthateValue([Date]) on the query grid but still continue to get an
Data Type Mismatch error and the cursor goes back and highlights the
DateValue([Date]) every time. I have tried removing all the other fields and
even removing the date criteria and it still continues to give the same
message on the DateValue. Any idea what might be the problem with the
DateValue. I noted though that when I made all the changes to the SQL
statement to follow yours I got a slightly different statement i.e.

SELECT DateValue([Date]) AS DateofMonth, tblCalls.CategoryID,
tblCallCategories.CategoryDescr, Count(tblCalls.Date) AS CountOfDate
FROM tblCallCategories INNER JOIN tblCalls ON tblCallCategories.CategoryID =
tblCalls.CategoryID
GROUP BY DateValue([Date]), tblCalls.CategoryID,
tblCallCategories.CategoryDescr
HAVING (((DateValue([Date])) Between #1/5/2005# And #1/8/2005#));

Thank you for any help I really appreciate it.
IEJ

IEJ,

Well, since you have the [Date] field in the query, and are Grouping By
this field, and since you have already told us that this field includes
a Time component, then each grouping will be unique, so the end result
will be the same as if you did no grouping at all. Unless I am
misunderstanding your purpose, I think you need to remove this field
from the query. Also, I would have expected you would want to Group By
the DateOfMonth before Grouping By the Category... No?

The reason you got a "type mismatch" error is because the correct
delimiter for date values is # not ".

So, probably something more like this?...

SELECT DateValue([Date]) AS DateofMonth, tblCalls.CategoryID,
tblCallCategories.CategoryDescr, Count(tblCalls.[Date]) AS CountOfDate
FROM tblCallCategories INNER JOIN tblCalls ON
tblCallCategories.CategoryID = tblCalls.CategoryID
GROUP BY DateValue([Date], tblCalls.CategoryID,
tblCallCategories.CategoryDescr
HAVING (DateValue([Date]) Between #01/05/05# And #01/08/05#);

--
Steve Schapel, Microsoft Access MVP

Hello, thank you. I have attached the code. I had changed the dates to what
is shown instead of the parameter names i.e.

=[Forms]![ME-SLB15-sfrmPrintDialogueBox]![txtBeginningDate] And <DateAdd("d",1,[Forms]![ME-SLB15-sfrmPrintDialogueBox]![txtEndingDate])

SELECT tblCalls.Date, Count(tblCalls.Date) AS CountOfDate,
tblCalls.CategoryID, tblCallCategories.CategoryDescr, DateValue([Date]) AS
DateofMonth
FROM tblCallCategories INNER JOIN tblCalls ON tblCallCategories.CategoryID =
tblCalls.CategoryID
GROUP BY tblCalls.Date, tblCalls.CategoryID,
tblCallCategories.CategoryDescr, DateValue([Date])
HAVING (((DateValue([Date]))>="01/05/05" And (DateValue([Date]))<="01/08/05"))
ORDER BY tblCalls.Date;

 

[Steve Schapel]

IEJ,

Well, Date is a Reserved Word (i.e. has a special meaning) in Access,
and shouldn't be used as the name of a field or control. But that
shouldn't be causing the problem in this instance, as putting the []s
around it should take care of any potential conflict. So, just to
check, if you go to the design view of the tblCalls table, and look at
the Date field, in the Data Type column it says Date/Time, right?

 

[nutmeg]

Good morning Steve - yes, is Date/Time and format is dddd", "mmmm d", "yyyy
hh:nn ampm. I had actually put all the formats in my first post though too.
Any idea what might be going on.
Thanks,
IEJ

IEJ,

Well, Date is a Reserved Word (i.e. has a special meaning) in Access,
and shouldn't be used as the name of a field or control. But that
shouldn't be causing the problem in this instance, as putting the []s
around it should take care of any potential conflict. So, just to
check, if you go to the design view of the tblCalls table, and look at
the Date field, in the Data Type column it says Date/Time, right?

--
Steve Schapel, Microsoft Access MVP

Good morning, yes you are correct, it is a date/time field, and quite right I
should have removed the other Date so have done that now. I am still having
difficulty though. I have changed the date field to be the
DateofMonthateValue([Date]) on the query grid but still continue to get an
Data Type Mismatch error and the cursor goes back and highlights the
DateValue([Date]) every time. I have tried removing all the other fields and
even removing the date criteria and it still continues to give the same
message on the DateValue. Any idea what might be the problem with the
DateValue. I noted though that when I made all the changes to the SQL
statement to follow yours I got a slightly different statement i.e.

SELECT DateValue([Date]) AS DateofMonth, tblCalls.CategoryID,
tblCallCategories.CategoryDescr, Count(tblCalls.Date) AS CountOfDate
FROM tblCallCategories INNER JOIN tblCalls ON tblCallCategories.CategoryID =
tblCalls.CategoryID
GROUP BY DateValue([Date]), tblCalls.CategoryID,
tblCallCategories.CategoryDescr
HAVING (((DateValue([Date])) Between #1/5/2005# And #1/8/2005#));

Thank you for any help I really appreciate it.
IEJ

 

[Steve Schapel]

IEJ,

Thanks... just checking. And yes, I saw the information in the first
post, but in fact the format is totally irrelevant, as it just affects
the way the data is displayed, and does not affect its value, so it has
no bearing on the problem.

And no, I am sorry, I have no idea what's going on, I can't see any
reason for this error. All I can suggest is for you to zip up the
database and email it to me, and I will have a look at it for you. If
it's too big, or contains sensitive information, you can just extract
the relevant bits out to a new .mdb file. Just remove the .ns from the
end of my return email address.