Null field causing data mismatch

[Dulanic]

I am trying to avoid a data mismatch for my query. The portion of the query (query2) causing the problem is:

SELECT Date()-[caAssignmentDate] AS AsgnAge
FROM Query1 
WHERE (((Date()-[caAssignmentDate])>10));

If I remove the WHERE portion, it works fine. If I remove the null records from the table it works (not feasible as I need them in the table for another query. That caAssignmentDate is created from query1 doing this:

caAssignmentDate: CDate(Mid([Query1]![pxCreateDateTime],5,2) & "-" & Mid([Query1]![pxCreateDateTime],7,2) & "-" & Left([Query1]![pxCreateDateTime],4))

I also tried doing a is not null where portion which throws another data mismatch. If I do a is not null on another field on the same record it takes out the #ERROR record... but yet I am still getting the data mismatch on the 2nd query (posted 1st here).

I tried wrapping this in Iif(IsNumeric,....,0 but that still results in a #ERROR.... where am I going wrong? lol how can i get these null fields to not cause this data mismatch, besides removing them from the table as that isn't feasible.

 

[MStef]

Try to do it via NZ function.

 

[John Big Booty]

Try to do it via NZ function.

An explanation of which you will find here

 

[Dulanic]

I don't believe NZ will work mostly because the result is #ERROR and not null. I had tried...

NZ(CDate(Mid([tbl_NF_Pinless]![pxCreateDateTime],5,2) & "-" & Mid([tbl_NF_Pinless]![pxCreateDateTime],7,2) & "-" & Left([tbl_NF_Pinless]![pxCreateDateTime],4)),0)

But that just results in #ERROR. I also tried wrapping /w Iif(Not Isnumeric and that also results in #ERROR.

caAssignmentDate: Iif(Not IsNumeric(CDate(Mid([tbl_NF_Pinless]![pxCreateDateTime],5,2) & "-" & Mid([tbl_NF_Pinless]![pxCreateDateTime],7,2) & "-" & Left([tbl_NF_Pinless]![pxCreateDateTime],4)),"0",CDate(Mid([tbl_NF_Pinless]![pxCreateDateTime],5,2) & "-" & Mid([tbl_NF_Pinless]![pxCreateDateTime],7,2) & "-" & Left([tbl_NF_Pinless]![pxCreateDateTime],4)))

 

[Dulanic]

I also just tried throwing an extra query in between the table & query 1... I had it exclude the null records. Same problem where I am running into a data type mismatch. I am... stuck lol. I don't get why I can't add a criteria of >10... if I pull the query with no criteria, access sees the column as numeric as I can from the drop down filter the query to >10... but if I put it as part of the query code, it won't accept it.

 

[Pat Hartman]

Use the DateDiff() function. It will return a numeric data type. Or, add a CInt() to the expression to ensure that the result of the subtraction is cast as an integer.

 

[Dulanic]

Use the DateDiff() function. It will return a numeric data type. Or, add a CInt() to the expression to ensure that the result of the subtraction is cast as an integer.

When I used DateDiff it said function not assigned... kind of odd. I was able to wrap the Date() - the other date field in CInt to make sure that was seen as a int. It is recognizing the field as numeric (it was before also).... I don't understand lol... it should be working.

It works fine.. until I add the criteria where > 10... as soon as I add >10 it stops working.

 

[Pat Hartman]

Humor me and use the DateDiff() function.

 

[John Big Booty]

When I used DateDiff it said function not assigned... kind of odd. I was able to wrap the Date() - the other date field in CInt to make sure that was seen as a int.

...

Have a look at he syntax for the DateDiff() function, I believe that is your problem.

 

[Dulanic]

I wish it was I tried that before and just tried it again, same problem. As soon as I delete the null record or if i delete the >10 criteria it works.

 

[rodmc]

Humor me and use the DateDiff() function.

this place need a pmsl type smilie!

 

[Pat Hartman]

One of the dates is not a valid date datatype. Since Now() is a function, I'm betting on CaAssignmentDate.

Try this to see:

Select caAssignmentDate, IsDate(caAssignmentDate) as exp1
From yourtable
Where IsDate(caAssignmentDate) = False;

Just because a column looks like a date, doesn't mean that it is.

PS: Now() returns the current date + time but Date() only returns the current date. For comparison purposes, Date() will cause fewer issues. Also, if the dates you are comparing to have time components, you can extract only the date part with DateValue(yourdate);

 

[Dulanic]

Same problem with that query you just gave.... it gives an error of data mismatch. Once I delete the null record it works.

caAssignmentDate is created by another query. The reason being the original date comes across as a string (yyymmdd&"T"&hhmmss.sss&" GMT") based on 24 hour clock.

I then have caAssignmentDate created by doing: CDate(Mid([table1]![pxCreateDateTime],5,2) & "-" & Mid([table1]![pxCreateDateTime],7,2) & "-" & Left([table1]![pxCreateDateTime],4))

So basically it goes like this: table1.pxCreateDateTime = 20120727T033218.770 GMT
Query1 = Above formula which makes the string 07-27-2012 which is then wrapped in CDate which converts it to serial date.

 

[Dulanic]

I'm a flippin idiot lol I think I got it. I had tried to filter out the null values but it wouldn't be null. That Cdate code is going to cause a #error when the pxCreateDateTime is null , I needed to filter out where null on the FIRST query not the 2nd. I'm so sorry for wasting time, I am 99% sure this is going to work. The queries take ~30 minutes to run as my laptop is crazy old and it's a few million records so it's running now but looks to be working.