Data Type Mismatch - CVDate

[toadmaster]

I would greatly appreciate it if I could get help on the following code

I keep getting a "Data Type mismatch" when I use the criteria (<120). It works fine when I take off the criteria.

The dates in the database are in different formats i.e. DOFD looks like this 19941208(which I then convert to MM/DD/YYYY) on the other had FPYD is being stored as 120891(which I also then convert to MM/DD/YYYY).

Unfortunately I am unable to update the database dates because I am not the only one using it.

SELECT [ACCT] & "-" & [SUFF] AS [ACCT+SUFF], PAYD.ACCT, PAYD.SUFF,PAYD.DOFD, CVDate(Mid$([DOFD],5,2) & "/" & Mid$([DOFD],7,2) & "/" & Left$([DOFD],4)) AS Expr1, DateSerial(Left$([DOFD],4),Mid$([DOFD],5,2),Right$([DOFD],2)) AS [DATE OF FIRST DELINQ], PAYD.FPYD, IIf(Len(NZ([FPYD],0))=1,Null,CVDate(IIf(Len([FPYD])=5,Left(CStr([FPYD]),1),Left(CStr([FPYD]),2)) & "/" & Left(Right(CStr([FPYD]),4),2) & "/" & Right(CStr([FPYD]),2))) AS [FIRST PAYMENT DATE], DateDiff("d",IIf(Len(NZ([FPYD],0))=1,Null,CVDate(Format([FPYD],"&&/&&/&&"))),CVDate(Mid$([DOFD],5,2) & "/" & Mid$([DOFD],7,2) & "/" & Left$([DOFD],4))) AS [PAST DUE], [Expr1]-[FIRST PAYMENT DATE] AS [PAST DUE2]
FROM MAIN RIGHT JOIN PAYD ON MAIN.ACCT = PAYD.ACCT
WHERE (((PAYD.DOFD)>0) AND ((DateDiff("d",IIf(Len(NZ([FPYD],0))=1,Null,CVDate(Format([FPYD],"&&/&&/&&"))),CVDate(Mid$([DOFD],5,2) & "/" & Mid$([DOFD],7,2) & "/" & Left$([DOFD],4))))<120))

Your urgent help will be greatly appreciated. Thank you in advance.

[ADezii]

@toadmaster
I think that the problem lies in the fact that if Len(NZ([FPYD],0))=1, you are now calculating the number of Days between a Null Value and CVDate(Mid$([DOFD],5,2) & "/" & Mid$([DOFD],7,2) & "/" & Left$([DOFD],4)). The result, which will always evaluate to Null, is then checked to see if it is < 120 (Null < 120).

[toadmaster]

Thanks ADezii for your quick response, unfortunately I am still getting the same error message after changing the parts of the of the code that show Len(NZ([FPYD])) to

CVDate(IIf(Len([FPYD])=5,Left(CStr([FPYD]),1),Left(CStr([FPYD]),2)) & "/" & Left(Right(CStr([FPYD]),4),2) & "/" & Right(CStr([FPYD]),2)) AS [FIRST PAYMENT DATE] 

[NeoPa]

I would suggest simplifying your SQL somewhat and displaying what you have clearly and legibly.

If someone is to think their way into your code it would help if it weren't simply dumped on the page as-is. You may find you get more attention that way.

It's probably a good idea also to strip out anything that is not causing you a problem. Not only will that simplify it for us to look at, it will also give you a much better chance of understanding, and therefore solving, your own problem.

We're always pleased to help, but seeing a member resolve their own issues is much better. That's only really likely with tidied SQL though.

[toadmaster]

I have been doing some research and came across this post,

http://bytes.com/topic/access/answer...pression-query

I also stripped out my SQL nailing down the bit where the error message seems to occur as suggested by Neopa(thank you)looks like the Data Mismatch is being caused by Null dates.

I modified my code as suggested in the post above and I am still getting the error message.

(DateDiff("d",Nz(CVDate(Format([FPYD],"&&/&&/&&")))),Nz(CVDate(Mid$([DOFD],5,2) & "/" & Mid$([DOFD],7,2) & "/" & Left$([DOFD],4))))))<120

All I am trying to do in this part of the code is get it to filter the results to show items < 120.
I really do appreciate all your help, this is really urgent and I am a desperate man.....

You know what they say about desperate men?

thanks soo much

[ADezii]

@toadmaster
Try changing your actual Format String to:

CVDate(Format([FPYD],"mm/dd/yy"))

[parodux]

You could also try this:

clng(DateDiff("d",Nz(CVDate(Format([FPYD],"&&/&&/&&")))),Nz(CVDate(Mid$([DOFD],5,2) & "/" & Mid$([DOFD],7,2) & "/" & Left$([DOFD],4))))))<120
 

[ADezii]

Just as a side note:
The CVDate Function is provided for compatibility with previous versions of Visual Basic. The syntax of the CVDate function is identical to the CDate function, however, CVDate returns a Variant whose subtype is Date instead of an actual Date type. Since there an intrinsic Date type exists, there is really no need for CVDate

[toadmaster]

Adezii and Paradox I really do appreciate all that you are doing to help.
The thing is Adezii with your recommendation,currently my dates are being displayed as 120891(which is mmddyy), I need it to be displayed as mm/dd/yyyy hence reason why I am using

CVDate(IIf(Len([FPYD])=5,Left(CStr([FPYD]),1),Left(CStr([FPYD]),2)) & "/" & Left(Right(CStr([FPYD]),4),2) & "/" & Right(CStr([FPYD]),2))

If I use your suggestion I get "12/26/1930" which is all wrong (my earlier post was not very clear, sorry). I believe the dates in the database are not actual dates.I am made some changes to my code using Paradox's suggestion, I got one main problem now. It is displaying all 2018 records when it should be just 78 records which do qualify under the criteria specified (<120).

The code is running but not filtering using the specified criteria(I get the same result whether with CVDate or CDate).

CLng(DateDiff("d",Nz(CVDate(IIf(Len([FPYD])=5,Left(CStr([FPYD]),1),Left(CStr([FPYD]),2)) & "/" & Left(Right(CStr([FPYD]),4),2) & "/" & Right(CStr([FPYD]),2))),Nz(CVDate(Mid$([DOFD],5,2) & "/" & Mid$([DOFD],7,2) & "/" & Left$([DOFD],4)))))<120

Thank you in advance

[NeoPa]

(DateDiff("d",Nz(CVDate(Format([FPYD],"&&/&&/&&")))),Nz(CVDate(Mid$([DOFD],5,2) & "/" & Mid$([DOFD],7,2) & "/" & Left$([DOFD],4))))))<120

First of all, the parentheses aren't balanced. This could just mean you included irrelevant, or failed to include relevant, parts of the code. Alternatively it could be a main part of your problem.

Also, it seems as if you're starting with a date value, then converting it to a string, just to convert it back again to a date. Is this your intention? Is there a reason for this? It appears as if you're simply struggling and making it all a bit more complicated than necessary. This is good news in wolf's clothing (if accurate). It would bean you can simplify much just by removing the code that converts from one to the other then back again.

PS. It seems things have changed since I last loaded. Can you explain exactly what format you have of data in your date fields. I assume now they are not actual date fields, but you know which they are I expect.

[NeoPa]

I've looked again at your first post and it's (almost) clear how the data is stored for [FPYD] and [DOFD]. I'll assume both dates are referring to 8th December rather than 12th August.

Try this SQL snippet :

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

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

[thelonelyghost]

Since only AFTER sorting through all of the code I found out I didn't have enough knowledge on the subject, I thought I would make it easier for people to read what's going on by summarizing the code in each of the above posts, thanks to the magic of variables. If there's any question as to the logistics of any part, refer to the variable section. Otherwise you should be able to get the gist of it from the equation section.

toadmaster:

______________________________
__________POST 3:_____________
--Variables:--
If Len([FPYD]) = 5 Then
  Month = Left(CStr([FPYD]),1)
Else
  Month = Left(CStr([FPYD]),2)
End If
Day = Left(Right(CStr([FPYD]),4),2)
Year = Right(CStr([FPYD]),2)
 
--Equation:--
CVDate( Month & "/" & Day & "/" & Year) AS [FIRST PAYMENT DATE]

toadmaster:

______________________________
__________POST 5:_____________
--Variables:--
strFPYDDate = Format([FPYD], "&&/&&/&&")
dtFPYDDate = Nz(CVDate(strFPYDDate))
DOFDMonth = Mid([DOFD], 5, 2)
DOFDDay = Mid([DOFD], 7, 2)
DOFDYear = Left([DOFD], 4)
dtDFODDate = Nz(CVDate(DOFDMonth & "/" & DOFDDay & "/" & DOFDYear))
 
--Equation:--
DateDiff("d", dtFPYDDate, dtDFODDate)

paradux:

______________________________
__________POST 7:_____________
--Variables:--
strFPYDDate = Format([FPYD], "&&/&&/&&")
dtFPYDDate = Nz(CVDate(strFPYDDate))
DOFDMonth = Mid([DOFD], 5, 2)
DOFDDay = Mid([DOFD], 7, 2)
DOFDYear = Left([DOFD], 4)
dtDFODDate = Nz(CVDate(DOFDMonth & "/" & DOFDDay & "/" & DOFDYear))
 
--Equation:--
CLng(DateDiff("d", dtFPYDDate, dtDFODDate)) < 120

toadmaster:

______________________________
_______POST 9 (part1):________
--Variables:--
Day = Left(Right(CStr([FPYD]), 4), 2)
Year = Right(CStr([FPYD]), 2)
If Len([FPYD]) = 5 Then
  Month = Left(CStr([FPYD]), 1)
Else
  Month = Left(CStr([FPYD]), 2)
End If
 
--Equation:--
CVDate(Month & "/" & Day & "/" & Year)

______________________________
_______POST 9 (part2):________
--Variables:--
dtFPYDDate = Nz(CVDate(FPYDMonth & "/" & FPYDDay & "/" & FPYDYear))
  FPYDDay = Left(Right(CStr([FPYD]), 4), 2)
  FPYDYear = Right(CStr([FPYD]), 2)
  If Len([FPYD]) = 5 Then
    FPYDMonth = Left(CStr([FPYD]), 1)
  Else
    FPYDMonth = Left(CStr([FPYD]), 2)
  End If
 
dtDOFDDate = Nz(CVDate(DOFDMonth & "/" & DOFDDay & "/" & DOFDYear))
  DOFDDay = Mid([DOFD], 7, 2)
  DOFDYear = Left{[DOFD], 4)
  DOFDMonth = Mid([DOFD], 5, 2)
 
--Equation:--
CLng(DateDiff("d", dtFPYDDate, dtDOFDDate)) < 120

NeoPa:

______________________________
__________POST 11:____________
--Variables:--
strFPYDDate = Format(Nz([FPYD], ''), '&&/&&/&&')
dtFPYDDate = CDate(strFPYDDate)
 
strDOFDMonthDay = Format(Mid(Nz([DOFD], '99991231'), 5, 4), '&&/&&/')
strDOFDYear = Left(Nz([DOFD], '9999'), 4)
dtDOFDDate = strDOFDMonthDay & strDOFDYear
 
--Equation:--
DateDiff('d', dtFPYDDate, dtDOFDDate) < 120

I'm just doing this to help in any way I can. Hope this helps bring more attention to the thread! (n_n)

[NeoPa]

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

You don't say if you tried out the SQL I posted. If you did, what was the result?

[toadmaster]

I did try out your SQL, only thing is it is still displaying 2018 records and not the 78 records which qualify under the criteria (<120)

Sorry I didn't make myself clear enough. Thank you

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

[NeoPa]

It seems you also took out the selection criteria in the latest SQL for checking the number of days < 120. Had you overlooked this?

[NeoPa]

It appears that the invalid date values are more than a side-issue. They are the fundamental reason behind the "Data type mismatch in criteria expression" message you are getting.

Try this again with only valid data and you will not see it. I did a little mock-up for you to test with. The erroneous data doesn't come into the first 200 records :

SELECT [ACCT] & '-' & [SUFF] AS [ACCT+SUFF]
     , [ACCT]
     , [SUFF]
     , [LTDT]
     , IIf(Len(NZ([LTDT],0))=1,Null,CDate(Format([LTDT],'&&/&&/&&'))) AS LTDT1
     , [DOFD]
     , [FPYD]
     , (DateDiff('d',CDate(Format(Nz([FPYD],10100),'&&/&&/&&')),
                     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   (
    SELECT TOP 200 *
    FROM   [PAYD]
    WHERE  [DOFD]>0
    ) AS subQ
 
 
WHERE  ([DOFD]>0)
  AND  (DateDiff('d',CDate(Format(Nz([FPYD],10100),'&&/&&/&&')),
                     CDate(Format(Mid(Nz([DOFD],99991231),5,4),'&&/&&/') & Left(Nz([DOFD],9999),4)))<120)

[toadmaster]

Neopa,

You are awesome, your script does work with the filtering(when used in .mdb), however when I apply to access 2007 which is the format in which the database is in, I keep get the data mismatch error message again. There shouldn't be match difference between the two should there?

[NeoPa]

Code-wise no, but it's perfectly possible for the data to present in a different order. My use of the TOP predicate to limit the query to the first 200 lines excluded erroneous data within the database I had. This may well be your problem with testing on the full database.

I suggest you check (manually) a number of the records that appear at the top of your list and change the TOP predicate in your query to match whatever number you've checked.

[toadmaster]

Neopa cannot thank you enough, I really do appreciate you spending your time to help me out with this. You are awesome.

Thank you

[NeoPa]

It was a bit of a struggle eventually, but we managed to find the issue in the end. I'm really pleased. Sometimes the obscure ones give you extra satisfaction :)