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.

 

[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.

 

[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!

 

[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.