Data Type Mismatch - CVDate

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • toadmaster
    New Member
    • Aug 2009
    • 45

    Data Type Mismatch - CVDate

    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.

    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))
    Your urgent help will be greatly appreciated. Thank you in advance.
  • ADezii
    Recognized Expert Expert
    • Apr 2006
    • 8834

    #2
    Originally posted by 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.

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

    Comment

    • toadmaster
      New Member
      • Aug 2009
      • 45

      #3
      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
      Code:
      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]
      Last edited by NeoPa; Aug 2 '10, 05:44 PM. Reason: Fixed code tag (which had been attempted)

      Comment

      • NeoPa
        Recognized Expert Moderator MVP
        • Oct 2006
        • 32633

        #4
        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.

        Comment

        • toadmaster
          New Member
          • Aug 2009
          • 45

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



          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.

          Code:
          (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
          Last edited by NeoPa; Aug 3 '10, 01:15 PM. Reason: Fixed attempt to use [CODE] tags

          Comment

          • ADezii
            Recognized Expert Expert
            • Apr 2006
            • 8834

            #6
            Originally posted by toadmaster
            I have been doing some research and came across this post,



            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.

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

            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
            Try changing your actual Format String to:
            Code:
            CVDate(Format([FPYD],"mm/dd/yy"))

            Comment

            • parodux
              New Member
              • Jul 2010
              • 26

              #7
              You could also try this:

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

              Comment

              • ADezii
                Recognized Expert Expert
                • Apr 2006
                • 8834

                #8
                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

                Comment

                • toadmaster
                  New Member
                  • Aug 2009
                  • 45

                  #9
                  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
                  Code:
                  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).

                  Code:
                  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

                  Comment

                  • NeoPa
                    Recognized Expert Moderator MVP
                    • Oct 2006
                    • 32633

                    #10
                    Code:
                    (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.

                    Comment

                    • NeoPa
                      Recognized Expert Moderator MVP
                      • Oct 2006
                      • 32633

                      #11
                      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 :
                      Code:
                      (DateDiff('d',CDate(Format(Nz([FPYD],''),'&&/&&/&&')),CDate(Format(Mid(Nz([DOFD],'99991231'),5,4),'&&/&&/') & Left(Nz([DOFD],'9999'),4)))<120)

                      Comment

                      • toadmaster
                        New Member
                        • Aug 2009
                        • 45

                        #12
                        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.

                        Comment

                        • thelonelyghost
                          New Member
                          • Jun 2010
                          • 109

                          #13
                          Summary

                          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:
                          Code:
                          ______________________________
                          __________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:
                          Code:
                          ______________________________
                          __________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:
                          Code:
                          ______________________________
                          __________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:
                          Code:
                          ______________________________
                          _______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)
                          Code:
                          ______________________________
                          _______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:
                          Code:
                          ______________________________
                          __________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)

                          Comment

                          • NeoPa
                            Recognized Expert Moderator MVP
                            • Oct 2006
                            • 32633

                            #14
                            Originally posted by toadmaster
                            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?

                            Comment

                            • toadmaster
                              New Member
                              • Aug 2009
                              • 45

                              #15
                              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

                              Comment

                              Working...