Data type mismatch in query. Why? (1 Viewer)

[RichO]

This is a strange one. Initially this code worked, and then it gave me the error, and then it worked, and now I am getting the error again.

I have a text field in a table that will either be blank (null) or contain a date in the format mm/dd/yyyy. In my query I am trying to get all records that contain a date in that field and it's less than today's date. I am getting the error from my criteria in this:

SELECT * FROM myTable WHERE CDate(Nz(Hold_Expiry_Date))<Date()

This is the only way I could think to code it. Hold_Expiry_Date is a text field so I need to convert it to a date field before comparing it to a date data type. What is the proper syntax to code this so I don't get the error?

Thanks

 

[Gasman]

I'm guessing it does not work when the field is null?

Use a replacement value in the nz function? nz(Hold_Expiry_Date,DATE())

 

[jdraw]

What if the Hold_Expiry_Date is Null? What is the intended default?

OOops: I see gasman has posted.

 

[RichO]

Use a replacement value in the nz function? nz(Hold_Expiry_Date,DATE())

I still get the same error when I try that :banghead:

The intended default can be anything that is today's date or greater. I have also tried Nz(Hold_Expiry_Date, #12/12/2050#) and I still get the error.

 

[RichO]

The error has everything to do with the field being null. When I filled all of the records with a date value in that field there was no error in running the code.

 

[RichO]

I even tried this

IIf(IsNull(Hold_Expiry_Date), Date(), CDate(Hold_Expiry_Date))<Date()

and I still get the same error

 

[Gasman]

When I first started using Access I had a lot of problems, with nulls, so I used a space for text fields instead. That was before I was informed of the nz function.

 

[jdraw]

You could try (untested)

iif(isDate(Cdate(Hold_Expiry_Date)), CDate( Hold_expiry_Date),Date())

 

[CJ_London]

are you sure the Hold_Expiry_Date is actually null and not a zero length string?

 

[RichO]

You could try (untested)

iif(isDate(Cdate(Hold_Expiry_Date)), CDate( Hold_expiry_Date),Date())

That first resulted in invalid use of null errors, and when I added Nz then the dreaded type mismatch error came back.

The blank fields are definitely null.

 

[jdraw]

Ok 1 more try (untested)

iif(len(Hold_expiry_date & "")=0 or IsNull(Hold_expiry_date),Date(),Cdate(Hold_Expiry_Date))

Edit:
These were reversed in original post

 

[RichO]

It has everything to do with comparing the field to a date type. I can set up a select query with this field:

CDate(Nz([Hold_Expiry_Date],"12/12/2050"))

And it displays as it should, but as soon as I attempt to compare it to Date() I get the error. It seems that CDate is not converting it to a date data type. I also tried DateValue and got the same error.

 

[jdraw]

So, my last attempt gave some error??

 

[jdraw]

I just created a table and tested this sql.

Table:
id Hold_Expiry_Date ( a text field)
1 21/3/2013
2
3 12/7/2013


I had a date in ID 2 but removed it, to get null.
Check for null

SELECT Tb_TestEx.id
FROM Tb_TestEx
WHERE (((Tb_TestEx.Hold_Expiry_Date) Is Null));

returns:

id
2

SELECT Tb_TestEx.id, IIf(Len([Hold_expiry_date] & "")=0 Or IsNull([Hold_expiry_date]),Date(),CDate([Hold_Expiry_Date])) AS xx
FROM Tb_TestEx;


Query result is: (I am using Canadian date format)

id xx
1 21/03/2013
2 31/01/2015
3 12/07/2013

 

[Gasman]

Some people offer DateSerial() in place of Cdate()

http://stackoverflow.com/questions/10502802/cdate-type-mismatch-error

 

[RichO]

jdraw, the query you created will work but try putting <Date() into the criteria for that field and I believe you will get the error

What's most puzzling is that you can fill that field with a string value of 12/12/2050 and when you use CDate to convert it to a date type and compare to Date() it works fine, but when the field is null and you use the expression in the query to convert it to 12/12/2050, you get an error.

In order to use DateSerial I will have to create a function that the query can call. I will give that a try.

 

[jdraw]

RichO,
---tested----
This works for me

SELECT Tb_TestEx.id, IIf(Len([Hold_expiry_date] & "")=0 Or IsNull([Hold_expiry_date]),Date(),CDate([Hold_Expiry_Date])) AS xx
FROM Tb_TestEx
where  IIf(Len([Hold_expiry_date] & "")=0 Or IsNull([Hold_expiry_date]),Date(),CDate([Hold_Expiry_Date]))<Date()

Result:

id xx
1 21/03/2013
3 12/07/2013

 

[RichO]

jdraw, that last one did the trick. I have tried everything even within a function, all sorts of manipulation of the data to make it into a date and I kept getting the same error but yours finally worked.

Thanks!

 

[jdraw]

Happy to help.
Good luck with your project.

 

[RichO]

I guess the remaining question is WHY it has to be done this way in order to work All of the other ways seemed logical but not to Access.