date format (access 2003)

Topic

New Reply

I’m have a date field in my query that has the date stored in the table like 1/6/2006 10:01:00 AM. I trying to link the 2 tables by date table 1 stores the data like 1/6/2006 10:01:00 AM and table 2 stores it like 1/6/2006. I tried to format the data from table 1 on my query with the format below. I tried to use the date range below and when the result of the query showed it only showed date from October and November of 2006 and 2007. Can some explain what I’m doing wrong? Thakns…

Date: Format([Appointment Date],”mm/dd/yyyy”)

>=#1/1/2007# And <=#11/30/2007#

Reply | Quote

Replies

By using the Format function, you convert the data to text values. Try this:

Date: CLng([AppointmentDate])

BTW, I’d use a different name than Date since Date is also a built-in function.

Reply | Quote

Thanks Hans. I tried that and after in ran I got an error of invalid use of null. I pasted the SQL below. There are no null values in the aptDate field..

SELECT Count(event_tbl.[ApptID]) AS [CountOfApptID], CLng([Appointment Date]) AS ApptDate
FROM IBTTOEFLEvent_tbl
GROUP BY CLng([Appointment Date])
HAVING (((CLng([Appointment Date]))>=#1/1/2007# And (CLng([Appointment Date]))<=#11/30/2007#));

Reply | Quote

Does this work?

Date: CLng(Nz([AppointmentDate],#1/1/1900#))

Reply | Quote

I think it group them right but the dates displayed like this 39088. Any thoughts?

Reply | Quote

Simply set the Format property of the column to your favorite date format.

Reply | Quote