Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2004
    Location
    Nashville
    Posts
    52

    Angry Unanswered: DATEVALUE in query/report

    Hi everyone,
    I have a table I inherited. A little over 17,000 records.
    Anyway, the date field was in text data type without template characters (09202003).
    I created a select query to convert it to a real date value with...

    Date: DateValue(Left([CertificationDate],2) & "/" & Mid([CertificationDate],3,2) & "/" & Right([CertificationDate],4))

    It worked great!! However, every time I try to create a report off the query and group Date by Month or any other grouping that involves this date, Access will only open the report in design view and I receive an error about data type mismatch in the expression criteria.

    I have to keep the table the way it is because of third party program outside the agency. Hard to explain! This is why I didn't use a make table query.

    What's going on here?

    Thanks alot!!

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Try using CDate to convert to a date ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Apr 2004
    Location
    Nashville
    Posts
    52
    No luck. I still get the same error.
    Very weird.
    "The wizard is unable to preview your report, possibly because another user has the source table open in exclusive mode."

    "Data type mismatch in expression criteria"

  4. #4
    Join Date
    Sep 2004
    Location
    Reston, VA
    Posts
    86
    CDate should work. Are you doing this?:

    Date: CDate(Left([CertificationDate],2) & "/" & Mid([CertificationDate],3,2) & "/" & Right([CertificationDate],4))

  5. #5
    Join Date
    Apr 2004
    Location
    Nashville
    Posts
    52

    Talking

    Hi BummerJeff,
    That's exactly what I did. It works great when I run the query but when I try to create a report and use that date in grouping, it screws up. If I create the report and don't use the date in grouping but still have it in my detail section, the report opens up just fine. But the report is useless with out date grouping.

  6. #6
    Join Date
    Sep 2004
    Location
    Reston, VA
    Posts
    86
    It must have something to do with how you're grouping it, because if it's allowing you to group on Month (the Group On section), then it recognizes it as a date. So there should be something else in the report that's conflicting with it. Where are you placing the [Date] control and how is it grouped? (Header, Footer, etc?) Do you have any other groupings?

  7. #7
    Join Date
    Apr 2004
    Location
    Nashville
    Posts
    52
    Good point!

    I'm trying to group the report by Region, then County and then Certification date. All headers. In the Certification Date header, the labels for the fields are also present but thats it.

Posting Permissions

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