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.
Your urgent help will be greatly appreciated. Thank you in advance.
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.
Code:
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))
Comment