Results 1 to 8 of 8
  1. #1
    ccoscina is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Dec 2014
    Posts
    14

    Data Type Mismatch - Converting String To Date and then Comparing To Date()

    Hello everyone,



    I have been experiencing some errors that I simply haven't been able to resolve on my own. Some background information:

    I'm migrating my company's Access database to SQL Server. My boss still wants the reports in Access to work if we need them. I am generating equivalents directly from SQL Server too, but he still wants the Access reports to work.

    The Access tables are going to be linked to CSV files generated from the SQL Server. However, once the CSV files are linked to Access, the date values need to be stored as Text data types, since if I try to import the Date fields as Dates, all the date fields get set to #NUM instead of the date.

    One of our reports generates a list of orders that have a transaction date from the past 24 hours, so I need to compare the transaction date to the current date (minus a day).

    First I convert the text value to a date, and then compare that to the current date.

    I'm going to show a simplified query instead of the query I'm actually doing, since essentially I just have to resolve the "Data Type Mismatch" error that I'm getting:

    SELECT id, TransDateTime, CDate(Left(Replace(TransDateTime,"-","/"),19)) AS Formatted
    FROM OrdersCopy
    WHERE DateValue(CDate(Left(Replace(TransDateTime,"-","/"),10)) )< DateValue(Date())

    It doesn't work if I leave out DateValue() as well. I always get a data type mismatch error.

    Does anybody know how to get it to properly compare?

    Thanks,
    -Chris

  2. #2
    JeffChr is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2014
    Posts
    82
    Your SQL statement works on my installation. Click image for larger version. 

Name:	2014-12-24_1157.png 
Views:	17 
Size:	28.2 KB 
ID:	19121 Included references just in case that might be the problem.

  3. #3
    ccoscina is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Dec 2014
    Posts
    14
    This is a screenshot of my references window, it's a little different:
    Click image for larger version. 

Name:	references.png 
Views:	17 
Size:	46.2 KB 
ID:	19123
    By the way, I'm using Access 2003

  4. #4
    ccoscina is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Dec 2014
    Posts
    14
    Also, here is an example of the results of what my query would be without the WHERE clause so that you can see the original format of the date strings:

    Click image for larger version. 

Name:	queryexample.png 
Views:	18 
Size:	114.8 KB 
ID:	19124

    The query above is
    SELECT id, TransDateTime, CDate(Left(Replace(TransDateTime,"-","/"),19)) AS Formatted
    FROM OrdersCopy

  5. #5
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Have you tried using a basic "CDate(TransDateTime)"? Compare that directly to Date(). This has always worked for me without all the other bits.

    Note that "Date()" does not contain the time so your requirement "past 24 hours" may not produce the results that you expect.

    Someone here smarter than me will know what the reference to "Microsoft Visual Basic for Applications Extensibility" contains, but I always include it.

  6. #6
    ccoscina is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Dec 2014
    Posts
    14
    aytee - Instead of using Date(), I would just use Now() - but that's irrelevant - once I get past the data type mismatch error I can adjust it to use now() instead.

    And yes, I have tried using CDate(TransDateTime) - I have tried a million other things before getting to the point I'm at now.

    CDate(TransDateTime) produces #Error in every field of the output. As in, when I do:

    SELECT CDate(TransDateTime)
    FROM OrdersCopy;

    This occurs:
    Click image for larger version. 

Name:	Cdate.png 
Views:	14 
Size:	92.5 KB 
ID:	19125

    I'm going to start heading home from work now, we got the day off early for the holiday - but I will be back in to check this thread on Friday. Thanks everyone for your help so far.

  7. #7
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    After further research, I found that once you go past the seconds the "#Error" appears. I did "CDate(Left(TransDate,19))" and it worked fine.

    Hope you had a happy holiday!

  8. #8
    ccoscina is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Dec 2014
    Posts
    14
    You are awesome - thank you.

    It works.

    It seems the main problem was that when you try to convert data types in a WHERE expression, it does the comparison before it converts the values. Strange

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 10-27-2014, 10:33 PM
  2. Converting a string to date/time
    By RayMilhon in forum Programming
    Replies: 8
    Last Post: 09-28-2012, 10:02 AM
  3. Year(date) returns type mismatch
    By reema in forum Programming
    Replies: 9
    Last Post: 03-28-2012, 08:46 AM
  4. Date - Textbox type mismatch 13
    By mdex in forum Programming
    Replies: 7
    Last Post: 01-20-2012, 08:44 AM
  5. Replies: 2
    Last Post: 07-03-2010, 08:45 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums