By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,422 Members | 1,615 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,422 IT Pros & Developers. It's quick & easy.

Data Type Mismatch - CVDate

P: 45
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.

Expand|Select|Wrap|Line Numbers
  1. 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]
  2. FROM MAIN RIGHT JOIN PAYD ON MAIN.ACCT = PAYD.ACCT
  3. 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.
Aug 2 '10 #1

✓ answered by 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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT [ACCT] & '-' & [SUFF] AS [ACCT+SUFF]
  2.      , [ACCT]
  3.      , [SUFF]
  4.      , [LTDT]
  5.      , IIf(Len(NZ([LTDT],0))=1,Null,CDate(Format([LTDT],'&&/&&/&&'))) AS LTDT1
  6.      , [DOFD]
  7.      , [FPYD]
  8.      , (DateDiff('d',CDate(Format(Nz([FPYD],10100),'&&/&&/&&')),
  9.                      CDate(Format(Mid(Nz([DOFD],99991231),5,4),'&&/&&/') & Left(Nz([DOFD],9999),4)))<120) AS [PAST DUE]
  10.      , CDate(Format(Nz([FPYD],''),'&&/&&/&&')) AS FYD
  11.      , CDate(Format(Mid(Nz([DOFD],'99991231'),5,4),'&&/&&/') & Left(Nz([DOFD],'9999'),4)) AS DOFD1
  12.      , [DOFD1]-[FYD] AS PS
  13.  
  14. FROM   (
  15.     SELECT TOP 200 *
  16.     FROM   [PAYD]
  17.     WHERE  [DOFD]>0
  18.     ) AS subQ
  19.  
  20.  
  21. WHERE  ([DOFD]>0)
  22.   AND  (DateDiff('d',CDate(Format(Nz([FPYD],10100),'&&/&&/&&')),
  23.                      CDate(Format(Mid(Nz([DOFD],99991231),5,4),'&&/&&/') & Left(Nz([DOFD],9999),4)))<120)

Share this Question
Share on Google+
35 Replies


ADezii
Expert 5K+
P: 8,627
@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).
Aug 2 '10 #2

P: 45
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
Expand|Select|Wrap|Line Numbers
  1. 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] 
Aug 2 '10 #3

NeoPa
Expert Mod 15k+
P: 31,438
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.
Aug 2 '10 #4

P: 45
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.

Expand|Select|Wrap|Line Numbers
  1. (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
Aug 2 '10 #5

ADezii
Expert 5K+
P: 8,627
@toadmaster
Try changing your actual Format String to:
Expand|Select|Wrap|Line Numbers
  1. CVDate(Format([FPYD],"mm/dd/yy"))
Aug 2 '10 #6

P: 26
You could also try this:

Expand|Select|Wrap|Line Numbers
  1. clng(DateDiff("d",Nz(CVDate(Format([FPYD],"&&/&&/&&")))),Nz(CVDate(Mid$([DOFD],5,2) & "/" & Mid$([DOFD],7,2) & "/" & Left$([DOFD],4))))))<120
  2.  
Aug 2 '10 #7

ADezii
Expert 5K+
P: 8,627
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
Aug 2 '10 #8

P: 45
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
Expand|Select|Wrap|Line Numbers
  1. 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).

Expand|Select|Wrap|Line Numbers
  1. 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
Aug 3 '10 #9

NeoPa
Expert Mod 15k+
P: 31,438
Expand|Select|Wrap|Line Numbers
  1. (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.
Aug 3 '10 #10

NeoPa
Expert Mod 15k+
P: 31,438
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 :
Expand|Select|Wrap|Line Numbers
  1. (DateDiff('d',CDate(Format(Nz([FPYD],''),'&&/&&/&&')),CDate(Format(Mid(Nz([DOFD],'99991231'),5,4),'&&/&&/') & Left(Nz([DOFD],'9999'),4)))<120)
Aug 3 '10 #11

P: 45
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.
Aug 3 '10 #12

thelonelyghost
100+
P: 109
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:
Expand|Select|Wrap|Line Numbers
  1. ______________________________
  2. __________POST 3:_____________
  3. --Variables:--
  4. If Len([FPYD]) = 5 Then
  5.   Month = Left(CStr([FPYD]),1)
  6. Else
  7.   Month = Left(CStr([FPYD]),2)
  8. End If
  9. Day = Left(Right(CStr([FPYD]),4),2)
  10. Year = Right(CStr([FPYD]),2)
  11.  
  12. --Equation:--
  13. CVDate( Month & "/" & Day & "/" & Year) AS [FIRST PAYMENT DATE]
toadmaster:
Expand|Select|Wrap|Line Numbers
  1. ______________________________
  2. __________POST 5:_____________
  3. --Variables:--
  4. strFPYDDate = Format([FPYD], "&&/&&/&&")
  5. dtFPYDDate = Nz(CVDate(strFPYDDate))
  6. DOFDMonth = Mid([DOFD], 5, 2)
  7. DOFDDay = Mid([DOFD], 7, 2)
  8. DOFDYear = Left([DOFD], 4)
  9. dtDFODDate = Nz(CVDate(DOFDMonth & "/" & DOFDDay & "/" & DOFDYear))
  10.  
  11. --Equation:--
  12. DateDiff("d", dtFPYDDate, dtDFODDate)
paradux:
Expand|Select|Wrap|Line Numbers
  1. ______________________________
  2. __________POST 7:_____________
  3. --Variables:--
  4. strFPYDDate = Format([FPYD], "&&/&&/&&")
  5. dtFPYDDate = Nz(CVDate(strFPYDDate))
  6. DOFDMonth = Mid([DOFD], 5, 2)
  7. DOFDDay = Mid([DOFD], 7, 2)
  8. DOFDYear = Left([DOFD], 4)
  9. dtDFODDate = Nz(CVDate(DOFDMonth & "/" & DOFDDay & "/" & DOFDYear))
  10.  
  11. --Equation:--
  12. CLng(DateDiff("d", dtFPYDDate, dtDFODDate)) < 120
toadmaster:
Expand|Select|Wrap|Line Numbers
  1. ______________________________
  2. _______POST 9 (part1):________
  3. --Variables:--
  4. Day = Left(Right(CStr([FPYD]), 4), 2)
  5. Year = Right(CStr([FPYD]), 2)
  6. If Len([FPYD]) = 5 Then
  7.   Month = Left(CStr([FPYD]), 1)
  8. Else
  9.   Month = Left(CStr([FPYD]), 2)
  10. End If
  11.  
  12. --Equation:--
  13. CVDate(Month & "/" & Day & "/" & Year)
Expand|Select|Wrap|Line Numbers
  1. ______________________________
  2. _______POST 9 (part2):________
  3. --Variables:--
  4. dtFPYDDate = Nz(CVDate(FPYDMonth & "/" & FPYDDay & "/" & FPYDYear))
  5.   FPYDDay = Left(Right(CStr([FPYD]), 4), 2)
  6.   FPYDYear = Right(CStr([FPYD]), 2)
  7.   If Len([FPYD]) = 5 Then
  8.     FPYDMonth = Left(CStr([FPYD]), 1)
  9.   Else
  10.     FPYDMonth = Left(CStr([FPYD]), 2)
  11.   End If
  12.  
  13. dtDOFDDate = Nz(CVDate(DOFDMonth & "/" & DOFDDay & "/" & DOFDYear))
  14.   DOFDDay = Mid([DOFD], 7, 2)
  15.   DOFDYear = Left{[DOFD], 4)
  16.   DOFDMonth = Mid([DOFD], 5, 2)
  17.  
  18. --Equation:--
  19. CLng(DateDiff("d", dtFPYDDate, dtDOFDDate)) < 120
NeoPa:
Expand|Select|Wrap|Line Numbers
  1. ______________________________
  2. __________POST 11:____________
  3. --Variables:--
  4. strFPYDDate = Format(Nz([FPYD], ''), '&&/&&/&&')
  5. dtFPYDDate = CDate(strFPYDDate)
  6.  
  7. strDOFDMonthDay = Format(Mid(Nz([DOFD], '99991231'), 5, 4), '&&/&&/')
  8. strDOFDYear = Left(Nz([DOFD], '9999'), 4)
  9. dtDOFDDate = strDOFDMonthDay & strDOFDYear
  10.  
  11. --Equation:--
  12. 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)
Aug 3 '10 #13

NeoPa
Expert Mod 15k+
P: 31,438
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?
Aug 4 '10 #14

P: 45
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
Aug 4 '10 #15

NeoPa
Expert Mod 15k+
P: 31,438
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT ...
  2.      , [FPYD]
  3.      , [DOFD]
  4.      , DateDiff('d',
  5.                 CDate(Format(Nz([FPYD],''),'&&/&&/&&')),
  6.                 CDate(Format(Mid(Nz([DOFD],'99991231'),5,4),'&&/&&/') &
  7.                       Left(Nz([DOFD],'9999'),4))) AS DaysDiff
  8.      , ...
It will be easier then to determine which records are failing the check and why.
Aug 4 '10 #16

P: 45
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
Expand|Select|Wrap|Line Numbers
  1. (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
Aug 4 '10 #17

NeoPa
Expert Mod 15k+
P: 31,438
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.
Aug 4 '10 #18

P: 45
I hope I am not confused, this is the entire code

Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM MAIN RIGHT JOIN PAYD ON MAIN.ACCT = PAYD.ACCT
  3. 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

Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM MAIN RIGHT JOIN PAYD ON MAIN.ACCT = PAYD.ACCT
  3. 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
Aug 4 '10 #19

NeoPa
Expert Mod 15k+
P: 31,438
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.
Aug 4 '10 #20

P: 45
Neopa please find the information as requested, thanks again

Expand|Select|Wrap|Line Numbers
  1. DOFD            FPYD    DaysDiffIndicator
  2. 19970808    80897    0    Include
  3. 20080515    51508    0    Include
  4. 20031214    121403    0    Include
  5. 20040501    50104    0    Include
  6. 20050805    80505    0    Include
  7. 20061205    120506    0    Include
  8. 19950728    93194    #Error    #Error
  9. 19981224    23196    #Error    #Error
  10. 19980128    23197    #Error    #Error
  11. 20040501    23004    #Error    #Error
  12. 19960912    81296    31    Include
  13. 20001111    101100    31    Include
  14. 20070701    40107    91    Include
  15. 20100731    50110    91    Include
  16. 19970124    102496    92    Include
  17. 20090430    12109    99    Include
  18. 20081130    81908    103    Include
  19. 20090331    121608    105    Include
  20. 20090331    121508    106    Include
  21. 20081130    81508    107    Include
  22. 20090630    31509    107    Exclude
  23. 20020905    21995    2755    Exclude
  24. 20020423    110992    3452    Exclude
  25. 20060705    102796    3538    Exclude
  26. 20010810    12890    4212    Exclude
  27. 20100531    32208    800    Exclude
  28. 20100731    52108    801    
  29. 20080515    51508    0    #Error
  30. 20010131    0    #Error    #Error
  31.  
Aug 4 '10 #21

NeoPa
Expert Mod 15k+
P: 31,438
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.
Aug 5 '10 #22

NeoPa
Expert Mod 15k+
P: 31,438
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]?
Aug 5 '10 #23

P: 45
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

Expand|Select|Wrap|Line Numbers
  1. CDate(Format(Nz([FPYD],''),'&&/&&/&&'))
  2.  
Expand|Select|Wrap|Line Numbers
  1. 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.
Aug 5 '10 #24

NeoPa
Expert Mod 15k+
P: 31,438
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.
Aug 5 '10 #25

P: 45
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).

Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM PAYD
  3. WHERE (((PAYD.DOFD)>0));
As I said in my earlier post this is an inherited database that is managed by a vendor.

Thanks again
Aug 6 '10 #26

NeoPa
Expert Mod 15k+
P: 31,438
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.
Aug 6 '10 #27

P: 45
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
Attached Files
File Type: zip CDATEMDB.zip (719.8 KB, 58 views)
Aug 7 '10 #28

ADezii
Expert 5K+
P: 8,627
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.
Aug 7 '10 #29

NeoPa
Expert Mod 15k+
P: 31,438
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.
Aug 9 '10 #30

NeoPa
Expert Mod 15k+
P: 31,438
It seems you also took out the selection criteria in the latest SQL for checking the number of days < 120. Had you overlooked this?
Aug 9 '10 #31

NeoPa
Expert Mod 15k+
P: 31,438
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT [ACCT] & '-' & [SUFF] AS [ACCT+SUFF]
  2.      , [ACCT]
  3.      , [SUFF]
  4.      , [LTDT]
  5.      , IIf(Len(NZ([LTDT],0))=1,Null,CDate(Format([LTDT],'&&/&&/&&'))) AS LTDT1
  6.      , [DOFD]
  7.      , [FPYD]
  8.      , (DateDiff('d',CDate(Format(Nz([FPYD],10100),'&&/&&/&&')),
  9.                      CDate(Format(Mid(Nz([DOFD],99991231),5,4),'&&/&&/') & Left(Nz([DOFD],9999),4)))<120) AS [PAST DUE]
  10.      , CDate(Format(Nz([FPYD],''),'&&/&&/&&')) AS FYD
  11.      , CDate(Format(Mid(Nz([DOFD],'99991231'),5,4),'&&/&&/') & Left(Nz([DOFD],'9999'),4)) AS DOFD1
  12.      , [DOFD1]-[FYD] AS PS
  13.  
  14. FROM   (
  15.     SELECT TOP 200 *
  16.     FROM   [PAYD]
  17.     WHERE  [DOFD]>0
  18.     ) AS subQ
  19.  
  20.  
  21. WHERE  ([DOFD]>0)
  22.   AND  (DateDiff('d',CDate(Format(Nz([FPYD],10100),'&&/&&/&&')),
  23.                      CDate(Format(Mid(Nz([DOFD],99991231),5,4),'&&/&&/') & Left(Nz([DOFD],9999),4)))<120)
Aug 9 '10 #32

P: 45
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?
Aug 10 '10 #33

NeoPa
Expert Mod 15k+
P: 31,438
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.
Aug 11 '10 #34

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

Thank you
Aug 11 '10 #35

NeoPa
Expert Mod 15k+
P: 31,438
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 :)
Aug 11 '10 #36

Post your reply

Sign in to post your reply or Sign up for a free account.