Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data Type Mismatch (Access 2003)

    In a query I have an expression, Expr1: CDate(Left([Completion Date],(InStr(1,[Completion Date]," ")-1))).
    I am trying to compare this to Date(). The syntax seems correct, but I am getting a Data Type Mismatch error.


    HELP.
    Richard

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

    Re: Data Type Mismatch (Access 2003)

    What kind of field is Completion Date and what does it contain?

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Type Mismatch (Access 2003)

    It is a string field. An example of a value is "3/8/2008 12:00 AM America/New York".
    Richard

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

    Re: Data Type Mismatch (Access 2003)

    This will happen if there are blank values in the Completion Date fields, or values that don't contain a valid date followed by a space.

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

    Re: Data Type Mismatch (Access 2003)

    Is there a good way to find this value?
    Richard

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

    Re: Data Type Mismatch (Access 2003)

    If you remove the Date() condition, the query should work, but it will return #Error for records that pose a problem.

  7. #7
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Type Mismatch (Access 2003)

    Hello -- I felt certain that I posted something about this earlier, but I can't find it, maybe I didn't. I found that I got this in one or two databases. I can't remember if I remade or dragged the query or queries into a new database -- and that solved the issue. In my case, I don't think it was an issue with nulls, or anything. It was definitely a fluke -- and if you search the web, you will see that there are a couple of others out there who have experienced this flukiness. -- thx, Pat

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Type Mismatch (Access 2003)

    I did have some the first time out that showed #Error. I added a Is Not Null condition and that eliminated those, but I was still getting the error.

    What I came up with was to do a Make Table query to put the data into a new table with the date value in a date field. Then I run the MAX query on the new table. Not very elegant, but it gets the job done.


    Thanks to all for your help.
    Richard

Posting Permissions

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