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.
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)
35 7589 @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).
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 32,556
Expert Mod 16PB
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.
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
@toadmaster
Try changing your actual Format String to: - CVDate(Format([FPYD],"mm/dd/yy"))
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
-
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
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 32,556
Expert Mod 16PB - (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 32,556
Expert Mod 16PB
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)
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.
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 32,556
Expert Mod 16PB 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?
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 32,556
Expert Mod 16PB
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.
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 32,556
Expert Mod 16PB
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.
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 32,556
Expert Mod 16PB
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.
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 32,556
Expert Mod 16PB
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 32,556
Expert Mod 16PB
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]?
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 32,556
Expert Mod 16PB
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 : - 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.
- 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.
- If the process depends on any linked tables then make local copies in your database to replace the linked tables.
- 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.
- Compile the database (From the Visual Basic Editor select Debug / Compile {Project Name}).
- Compact the database (Tools / Database Utilities / Compact and Repair Database...).
- Compress the database into a ZIP file.
- 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.
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 32,556
Expert Mod 16PB
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.
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
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 32,556
Expert Mod 16PB
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 32,556
Expert Mod 16PB
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 32,556
Expert Mod 16PB
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)
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 32,556
Expert Mod 16PB
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.
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 32,556
Expert Mod 16PB
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 :)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Laurel |
last post by:
this is driving me crazy. i need to use a form control as a criteria
in a select query, and the control's value is set depending upon what
a user selects in an option group on the form. the query...
|
by: Chicken Kebab Abdullah |
last post by:
Does anyone know why I get the error 3464 Data type mismatch from the
following code.
I have a form with a combo(to choose a consumable) and 2 list boxes on
it.
list on left is all printers...
|
by: news.paradise.net.nz |
last post by:
I have been developing access databases for over 5 years.
I have a large database and I have struck this problem with it before
but can find nothing in help or online. Access 2000
I have a query...
|
by: ArcadeJr |
last post by:
Good morning all!
I have been getting a Run-time Error message #3464 - Data Type mismatch
in criteria expression. While trying to run a query.
I have a database where the field Asset_Number...
|
by: Jake |
last post by:
I am currently trying to create my own Point Of Sale software for my
retail store. I wrote the program with the UPC field as Long integer.
When I started to add the products by UPC code, I got a...
|
by: amitbadgi |
last post by:
I am getting the following error while converting an asp application
to asp.net
Exception Details: System.Runtime.InteropServices.COMException: Data
type mismatch in criteria expression.
...
|
by: igor.barbaric |
last post by:
Hello!
I have created a very simple query like this:
SELECT Tasks.Name, DurationHrs(,) AS
Duration
FROM Tasks INNER JOIN Log ON Tasks.TaskID=Log.TaskID;
The above query works fine....
|
by: psychomad |
last post by:
Please, can someone help me out to solve this error, i've been
searching throughout my codes and yet i didnt succeed in finding the
error!!!!
The Error is:
Server Error in '/' Application....
|
by: Lysander |
last post by:
I have written a query that takes three integers representing day,month and year, forms a date from them and compares this date to the date the record was entered and returns any records where the...
|
by: Bruce Lawrence |
last post by:
I've got a form that when it opens it prompts the user for 2 peices of
information. The Asset and the Date.
getasset = InputBox("Enter the Asset Number")
If getasset = "" Then
Exit Sub
Else...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |