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

    data type mismatch error on date/time value (access 2003)

    When I run this SQL I'm getting a Data type mismatch error. I'm at a lose as to why. The table adminsum stores the appointment date as a text value. I'm trying to convert this to a date/time value in my query. Any thoughts why I'm getting this error? When I remove the group by on the admin date field the error goes away but I want to group by the different dates. Thanks..


    SELECT AdminSum.[Admin ID], Count(AdminSum.[CBSR Appointment ID]) AS [CountOfCBSR Appointment ID], AdminSum.[Site ID], CLng(Nz([Appointment Date])) AS AdminDate
    FROM AdminSum
    WHERE (((AdminSum.[Event Outcome]) Not Like "Candidate No-Show"))
    GROUP BY AdminSum.[Admin ID], AdminSum.[Site ID], CLng(Nz([Appointment Date]))
    HAVING (((AdminSum.[Admin ID]) Not Like "2007*"));

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: data type mismatch error on date/time value (access 2003)

    What type of field is [Admin ID] ? Is it a Text field? Using Like with an ID field looks odd, but may be ok.

    Does CLng work with your Appointment Dates?
    Why don't you use CDate?
    Regards
    John



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

    Re: data type mismatch error on date/time value (access 2003)

    What happens if you change both occurrences of

    CLng(Nz([Appointment Date]))

    to

    CDate(Nz([Appointment Date],#01/01/1900#))

    You can replace #01/01/1900# with whatever date you would want to use instead of a blank.

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: data type mismatch error on date/time value (access 2003)

    Why are you using CLng to convert [Appointment Date] to a Date field? Use CDate()!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: data type mismatch error on date/time value (access 2003)

    Tried it but I still get the same error. It runs and then fails. It's only when I do a group by statement or add any kind of parameter. If I don't group by any feilds or andd amy parameters it runs and converts the format to a date/time value. I set the properties of the field in the query to date/time value..


    SELECT AdminSum.[Admin ID], Count(AdminSum.[CBSR Appointment ID]) AS [CountOfCBSR Appointment ID], AdminSum.[Site ID], CDate(Nz([Appointment Date],#1/1/1900#)) AS AdminDate
    FROM AdminSum
    WHERE (((AdminSum.[Event Outcome]) Not Like "Candidate No-Show"))
    GROUP BY AdminSum.[Admin ID], AdminSum.[Site ID], CDate(Nz([Appointment Date],#1/1/1900#));

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

    Re: data type mismatch error on date/time value (access 2003)

    Does the Appointment Date field contain values that cannot be converted to a date? Create a 'normal' query that adds the AdminDate field, and look for records that show #Error or similar in the AdminDate column.

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

    Re: data type mismatch error on date/time value (access 2003)

    That was it Hans. I had some goofy dates in there. Once I got them out it ran ok..

Posting Permissions

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