Data Type Mismatch - CVDate

[NeoPa]

Before I ask that you attach a sanitised copy of your database I'd just ask you to try one further thing for me.

Please create an extra field in your query that reflects the number of days calculated, just as used in the WHERE clause :

SELECT ...
     , [FPYD]
     , [DOFD]
     , DateDiff('d',
                CDate(Format(Nz([FPYD],''),'&&/&&/&&')),
                CDate(Format(Mid(Nz([DOFD],'99991231'),5,4),'&&/&&/') &
                      Left(Nz([DOFD],'9999'),4))) AS DaysDiff
     , ...

It will be easier then to determine which records are failing the check and why.

[toadmaster]

Neopa thanks for your help, I cannot even begin to express how much I appreciate all the effort you are putting into this.

I am not sure if I understand your instruction correctly but in my earliar posting

Toadmaster: NeoPa thank you for your response, my major huddle right now is, it's still displaying 2018 records instead of the 78 records which actually qualify under the criteria (<120).

I tried to work around it by splitting the code into two different fields DOFD and FYD and then subtracted [DOFD]-[FYD] after which I then enter <120 in the criteria field.

This then throws up the data mismatch error message again.

That is what I did, I created a two extra fields [FPYD] AND [DOFD] to reflect the number of days calculated, my SQL knowledge is not very broad so I am using the query builder and making the necessary changes where I am competent. So please let me know if I am wrong and didn't understand your question.

On the other hand I noticed that when I apply your SQL in the select statement before the where clause

(DateDiff('d',CDate(Format(Nz([FPYD],''),'&&/&&/&&')),CDate(Format(Mid(Nz([DOFD],'99991231'),5,4),'&&/&&/') & Left(Nz([DOFD],'9999'),4)))<120) AS [PAST DUE]

I show 0, -1 and #Errors in the field PAST DUE, the numbers that are greater than 120 are showing up as 0's and those less than 120, 1's. Meanwhile a few like DOFD (where the date in the database is 19970207) and FYD (where the date in the database is 93194) are showing up as #Errors.

I hope this helps, thank you

[NeoPa]

It's quite good thinking, but copying what I suggested exactly would give you (and me) clearer results. You are including the assertion SQL, that I was careful to avoid. This tells us that the wrong records are wrong (but very little else).

My posted SQL was designed to give us a bit more information as to why.

Let me know what you get when you try that for me would you.

[toadmaster]

I hope I am not confused, this is the entire code

SELECT [ACCT] & "-" & [SUFF] AS [ACCT+SUFF], PAYD.ACCT, PAYD.SUFF, PAYD.DOFD, PAYD.FPYD, DateDiff('d',CDate(Format(Nz([FPYD],''),'&&/&&/&&')),CDate(Format(Mid(Nz([DOFD],'99991231'),5,4),'&&/&&/') & Left(Nz([DOFD],'9999'),4))) AS DaysDiff
FROM MAIN RIGHT JOIN PAYD ON MAIN.ACCT = PAYD.ACCT
WHERE (((PAYD.DOFD)>0);

I end up with 2418 records,534(are < 120),1884(are > 120 therefore are failing the criteria). Out of all these records I also have 246 showing #Errors and 42 Zeros.

NOTE: 2018 was a typo on my part(it should be 2418)

On the other hand if I use the following code it gives me the datatype mismatch error

SELECT [ACCT] & "-" & [SUFF] AS [ACCT+SUFF], PAYD.ACCT, PAYD.SUFF, PAYD.DOFD, PAYD.FPYD, DateDiff('d',CDate(Format(Nz([FPYD],''),'&&/&&/&&')),CDate(Format(Mid(Nz([DOFD],'99991231'),5,4),'&&/&&/') & Left(Nz([DOFD],'9999'),4))) AS DaysDiff
FROM MAIN RIGHT JOIN PAYD ON MAIN.ACCT = PAYD.ACCT
WHERE (((PAYD.DOFD)>0) AND ((DateDiff('d',CDate(Format(Nz([FPYD],''),'&&/&&/&&')),CDate(Format(Mid(Nz([DOFD],'99991231'),5,4),'&&/&&/') & Left(Nz([DOFD],'9999'),4))))<120));

Thank you

[NeoPa]

OK. What I need to see is some of the data. Please include a number of rows with #Error, a number of rows which should be selected and a number that shouldn't. The data for each row should include only :
[DOFD]
[FPYD]
[DaysDiff]
Something to indicate whether you feel the record should be included or excluded from your output.

[toadmaster]

Neopa please find the information as requested, thanks again

DOFD	        FPYD	DaysDiffIndicator
19970808	80897	0	Include
20080515	51508	0	Include
20031214	121403	0	Include
20040501	50104	0	Include
20050805	80505	0	Include
20061205	120506	0	Include
19950728	93194	#Error	#Error
19981224	23196	#Error	#Error
19980128	23197	#Error	#Error
20040501	23004	#Error	#Error
19960912	81296	31	Include
20001111	101100	31	Include
20070701	40107	91	Include
20100731	50110	91	Include
19970124	102496	92	Include
20090430	12109	99	Include
20081130	81908	103	Include
20090331	121608	105	Include
20090331	121508	106	Include
20081130	81508	107	Include
20090630	31509	107	Exclude
20020905	21995	2755	Exclude
20020423	110992	3452	Exclude
20060705	102796	3538	Exclude
20010810	12890	4212	Exclude
20100531	32208	800	Exclude
20100731	52108	801	
20080515	51508	0	#Error
20010131	0	#Error	#Error

[NeoPa]

Most of the #Errors are simply impossible dates. There are only 30 days in September, and 29 maximum for February. Line #30 is not a date at all, nor is it a Null (which would be handled).

Line #29 is trickier. Even in the UK (where our default date format is d/m/y) this is handled fine. Are you sure you have shown the data correctly? It resolves to 0 in the [DaysDiff] column, so I see no reason this would result in #Error anywhere.

The indicator column is supposed to be telling me what you would expect to see for that record. I can't see why there is ever anything in there other than Include or Exclude.

[NeoPa]

Moving on...

You have an Exclude against line #22 even though the value for [DaysDiff] is 107 (<120). Can you explain this.

Otherwise, I assume you're telling me that all these records are included in the output, regardless of the associated value of [DaysDiff] yes?

Looking at the data, it seems that the fundamental calculation appears to be working, even though the data for [FPYD] is often not in the format you led us to expect (mmddyy). I appreciate you posted some code at some point (post #9) which referred to this obliquely, but it's really much better to share all the relevant facts at the start. Anyway, the code handles this without problem thankfully (due in some part to your clever use of the '&' in your format call that I copied in my suggested code).

What is curious then, is why the check for this value against 120 doesn't appear to be working.

What are the field types for [DOFD] and [FPYD]?

[toadmaster]

Neopa - the Exclude against line #22 is a typo on my part(I had to export the result to excel and then typed in the exclude or include).

Yes you are correct all the records are included in the output regardless of the associated value of [DaysDiff]

I am assuming that the field types are text, this an inherited database(also when I right click and look in the property sheet, the format field is blank). I have the option to only select Numbers nothing about dates. That is the reason why in my earlier post I had mentioned creating two different fields for [FPYD] and [DOFD] by using

CDate(Format(Nz([FPYD],''),'&&/&&/&&'))

CDate(Format(Mid(Nz([DOFD],'99991231'),5,4),'&&/&&/') & Left(Nz([DOFD],'9999'),4))

by doing that it allowed me to select "short date" for the field types in the property page. However even converting the field types to dates I am still getting data type mismatch or the criteria is failing entirely.

[NeoPa]

Well, I have to say, I've looked through this a number of times and seen that possibly the default value of '' for when [FPYD] is found to be Null may cause a problem at some stage, but as the comparison is simply between the DateDiff() call (Return value=Variant Long) and a numeric literal 120, I fail to see why this would not be resolving correctly and as expected.

I'm afraid the time has come for me to request a copy of your database so that I can see for myself exactly what is happening. See below for detailed instructions.

When attaching your work please follow the following steps first :

1. Remove anything not relevant to the problem. This is not necessary in all circumstances but some databases can be very bulky and some things do not effect the actual problem at all.
2. Likewise, not entirely necessary in all cases, but consider saving your database in a version not later than 2003 as many of our experts don't use Access 2007. Largely they don't want to, but some also don't have access to it. Personally I will wait until I'm forced to before using it.
3. If the process depends on any linked tables then make local copies in your database to replace the linked tables.
4. If you've done anything in steps 1 to 3 then make sure that the problem you're experiencing is still evident in the updated version.
5. Compile the database (From the Visual Basic Editor select Debug / Compile {Project Name}).
6. Compact the database (Tools / Database Utilities / Compact and Repair Database...).
7. Compress the database into a ZIP file.
8. When posting, scroll down the page and select Manage Attachments (Pressing on that leads you to a page where you can add or remove your attachments. It also lists the maximum file sizes for each of the allowed file types.) and add this new ZIP file.

It's also a good idea to include some instructions that enable us to find the issue you'd like help with. Maybe some instructions of what to select, click on, enter etc that ensures we'll see what you see and have the same problems.

[toadmaster]

Thank you for all your help, please find the database attached
- Within the database is a query called - Delinquency Report. It contains all the SQL and info,

In taking away all the non-relevant information I had to make some changes to the SQL (but it does replicate the error message exactly).

SELECT [ACCT] & "-" & [SUFF] AS [ACCT+SUFF], PAYD.ACCT, PAYD.SUFF, PAYD.LTDT, IIf(Len(NZ([LTDT],0))=1,Null,CDate(Format([LTDT],"&&/&&/&&"))) AS LTDT1, PAYD.DOFD, PAYD.FPYD, (DateDiff('d',CDate(Format(Nz([FPYD],''),'&&/&&/&&')),CDate(Format(Mid(Nz([DOFD],'99991231'),5,4),'&&/&&/') & Left(Nz([DOFD],'9999'),4)))<120) AS [PAST DUE], CDate(Format(Nz([FPYD],''),'&&/&&/&&')) AS FYD, CDate(Format(Mid(Nz([DOFD],'99991231'),5,4),'&&/&&/') & Left(Nz([DOFD],'9999'),4)) AS DOFD1, [DOFD1]-[FYD] AS PS
FROM PAYD
WHERE (((PAYD.DOFD)>0));

As I said in my earlier post this is an inherited database that is managed by a vendor.

Thanks again

[NeoPa]

I'm not sure whether you realised, but you've sent me an MDE file. I can't see any of your code in there I'm afraid. Please try again with the MDB. Thank you.

[toadmaster]

Neopa sorry about that, I am using access 2007 so I had to convert it to 2000.
Hope it didn't mess anything up.

Thanks

[ADezii]

I've just revisited this Thread and had a quick look at the Delinquency Report Query. I am nowhere near familiar with this Thread as you and NeoPa are, and please forgive me if I am over simplifying, but the Error Conditions appear to exist because of the presence of 0 Values in the [FPYD] Field which conflicts with Formatting and Type Conversions in the Calculated Fields. In addition to the [DOFD] > 0, it would appear to me that you also need a Criteria of [FPYD] > 0, namely: [DOFD] > 0 AND [FPYD] > 0. Once you make this change, Errors cease to exist. Again, I apologize if I am oversimplifying , or have misread this Thread.

[NeoPa]

That certainly may turn out to be an issue ADezii, but from the data there is only one instance of [FPYD] being 0. There are a number of instances of [FPYD] being data that is not consistent with a valid date of the format described (See post #22). I don't feel it is within the scope of the question for me/us to handle such an issue. I would expect that to be a matter for the OP to deal with by internal communication with whoever is producing the data.

What is fundamental to the issue is why the calculation is clearly producing the correct value for the number of days, yet the filtering doesn't appear to be effective. There are other issues (a few it seems) but this is the one that's fundamental to the thread.

I had a bit of a break over the weekend, so I need to catch up with this tonight with a thorough check through. I'll see then what I come up with.