Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    date format (access 2003)

    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#

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: date format (access 2003)

    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.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: date format (access 2003)

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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: date format (access 2003)

    Does this work?

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

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: date format (access 2003)

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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: date format (access 2003)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •