Comparing Date Values in Access - Data Type Mismatch in Criteria Expression

Question

i'm having an issue comparing a date in an access database. basically i'm parsing out a date from a text field, then trying to compare that date to another to only pull newer/older records.

so far i have everything working, but when i try to add the expression to the where clause, it's acting like it's not a date value.

here's the full SQL:

SELECT 
Switch(Isdate(TRIM(LEFT(bc_testingtickets.notes, Instr(bc_testingtickets.notes, ' ')))) = false, 'NOT     ASSIGNED!!!') AS [Assigned Status],
TRIM(LEFT(bc_testingtickets.notes, Instr(bc_testingtickets.notes, ' '))) AS [Last Updated Date], 
bc_testingtickets.notes AS [Work Diary], 
bc_testingtickets.ticket_id, 
clients.client_code, 
bc_profilemain.SYSTEM, 
list_picklists.TEXT, 
list_picklists_1.TEXT, 
list_picklists_2.TEXT, 
list_picklists_3.TEXT, 
bc_testingtickets.createdate, 
bc_testingtickets.completedate, 
Datevalue(TRIM(LEFT([bc_TestingTickets].[notes], Instr([bc_TestingTickets].[notes], ' '))))  AS datetest 

FROM   list_picklists AS list_picklists_3 
 RIGHT JOIN (list_picklists AS list_picklists_2 
             RIGHT JOIN (list_picklists AS list_picklists_1 
                         RIGHT JOIN (bc_profilemain 
                                     RIGHT JOIN (((bc_testingtickets 
                                                   LEFT JOIN clients 
                                                     ON 
 bc_testingtickets.broker = clients.client_id) 
              LEFT JOIN list_picklists 
                ON 
 bc_testingtickets.status = list_picklists.id) 
             LEFT JOIN bc_profile2ticketmapping 
               ON bc_testingtickets.ticket_id = 
                  bc_profile2ticketmapping.ticket_id) 
   ON bc_profilemain.id = 
      bc_profile2ticketmapping.profile_id) 
 ON list_picklists_1.id = bc_testingtickets.purpose) 
 ON list_picklists_2.id = bc_profilemain.destination) 
   ON list_picklists_3.id = bc_profilemain.security_type 
WHERE  ( ( ( list_picklists.TEXT ) <> 'Passed' 
     AND ( list_picklists.TEXT ) <> 'Failed' 
     AND ( list_picklists.TEXT ) <> 'Rejected' ) 
   AND ( ( bc_testingtickets.ticket_id ) <> 4386 ) ) 
GROUP  BY bc_testingtickets.notes, 
    bc_testingtickets.ticket_id, 
    clients.client_code, 
    bc_profilemain.SYSTEM, 
    list_picklists.TEXT, 
    list_picklists_1.TEXT, 
    list_picklists_2.TEXT, 
    list_picklists_3.TEXT, 
    bc_testingtickets.createdate, 
    bc_testingtickets.completedate, 
    DateValue(TRIM(LEFT([bc_TestingTickets].[notes], Instr([bc_TestingTickets].[notes], ' ')))) 
ORDER  BY Datevalue(TRIM(LEFT([bc_TestingTickets].[notes], Instr([bc_TestingTickets].[notes], ' '))));

the value i'm trying to compare against a various date is this:

DateValue(Trim(Left([bc_TestingTickets].[notes],InStr([bc_TestingTickets].[notes],' '))))

if i add a section to the where clause like below, i get the Data Type Mismatch error:

WHERE DateValue(Trim(Left([bc_TestingTickets].[notes],InStr([bc_TestingTickets].[notes],' ')))) > #4/1/2012#

i've even tried using the DateValue function around the manual date i'm testing with but i still get the mismatch error:

WHERE DateValue(Trim(Left([bc_TestingTickets].[notes],InStr([bc_TestingTickets].[notes],' ')))) > DateValue("4/1/2012")

any tips on how i can compare a date in this method? i can't change any fields in the database, ect, that's why i'm parsing the date in SQL and trying to manipulate it so i can run reports against it.

i've tried googling but nothing specifically talks about parsing a date from text and converting it to a date object. i think it may be a bug or the way the date is being returned from the left/trim functions. you can see i've added a column to the end of the SELECT statement called DateTest and it's obvious access is treating it like a date (when the query is run, it asks to sort by oldest to newest/newest to oldest instead of A-Z or Z-A), unlike the second column in the select.

thanks in advance for any tips/clues on how i can query based on the date.

edit: i just tried the following statements in my where clause and still getting a mismatch:

CDate(Trim(Left([bc_TestingTickets].[notes],InStr([bc_TestingTickets].[notes],' ')))) > #4/1/2012#
CDate(Trim(Left([bc_TestingTickets].[notes],InStr([bc_TestingTickets].[notes],' ')))) >
CDate("4/1/2012") CDate(DateValue(Trim(Left([bc_TestingTickets].[notes],InStr([bc_TestingTickets].[‌notes],' '))))) > #4/1/2012#

i tried with all the various combinations i could think of regarding putting CDate inside of DateValue, outside, ect. the CDate function does look like what i should be using though. not sure why it's still throwing the error.

here's a link to a screenshot showing the results of the query http://ramonecung.com/access.jpg. there's two screenshots in one image.

HansUp HansUp · Accepted Answer · 2012-04-13T18:00:45

You reported you get Data Type Mismatch error with this WHERE clause.

WHERE DateValue(Trim(Left([bc_TestingTickets].[notes],
    InStr([bc_TestingTickets].[notes],' ')))) > #4/1/2012#

That makes me wonder whether [bc_TestingTickets].[notes] can ever be Null, either because the table design allows Null for that field, or Nulls are prohibited by the design but are present in the query's set of candidate rows as the result of a LEFT or RIGHT JOIN.

If Nulls are present, your situation may be similar to this simple query which also triggers the data type mismatch error:

SELECT DateValue(Trim(Left(Null,InStr(Null,' '))));

If that proves to be the cause of your problem, you will have to design around it somehow. I can't offer a suggestion about how you should do that. Trying to analyze your query scared me away. :-(

Comparing Date Values in Access - Data Type Mismatch in Criteria Expression

3 Answers