type mismatch error

[roubesh14]

Hi guys. I' am getting a problem concerning date.

dateborrowed = pbRst!borrowdate 'where dateborrowed is STRING and 'borrowdate is of type medium date in the mdb
               
expecteddate = dateborrowed + rst!VCDayslimit   'Where expecteddate is 'STRING VCDayslimit is of type number in the mdb

When executed i get a type mismatch error

How can i fix this please
btw iam on vb6,database:ACCESS and connection adodb

thank u

[nagasrikanth]

dateborrowed 'n' expecteddate should be of DATE dataType...

[roubesh14]

Thanks nagasrikanth my problem is solved but i'am getting another error

Set pbRst = RstSel("select * from borrow where serialno = '" & pvserial & "'")
    If Not pbRst.EOF And Not pbRst.BOF Then
        If pbRst!expecteddate > pbRst!datereturn Then
            MsgBox "This film has been overdue! PENALTY AWARDED!", vbOKOnly + vbExclamation
            lateness = pbRst!datereturned - pbRst!expecteddate
            lcsql = "update borrow set lateness = '" & lateness & "',Status = 'RETURNED'  where serialno = '" & pvserial & "'"
            SqlEXE (lcsql)
            Set rst = RstSel("SELECT * FROM CATALOGUE INNER JOIN MEDIA ON CATALOGUE.CatId=MEDIA.CatId WHERE media.serialno = '" & pvserial & "'")
            If rst!mediatype = "VCD" Then
                lcsql = "insert into penalty values '" & pvserial & "','" & cboMembershipId.Text & "','" & rst!VCPnRATE & "','UNPAID' ,'" & txtDateReturned.Text & "'"
                SqlEXE (lcsql)
            ElseIf rst!mediatype = "DVD" Then
                lcsql = "insert into penalty values '" & pvserial & "','" & cboMembershipId.Text & "','" & rst!DVPnRATE & "','UNPAID' ,'" & txtDateReturned.Text & "'"
                SqlEXE (lcsql)
            Else
            End If
                       
        Else
        End If
    End If

when this line:

If pbRst!expecteddate > pbRst!datereturn Then

is executed pbRst!expecteddate has value:5/15/2006 and pbRst!datereturn has value 5/17/2006

but the problem is that it bybasses the statement just after the condition and goes directly to else endif

whats wrong with this guys?


Thanks for your coorperation...

[si_the_geek]

It's the same as: "If '5/15/2006' > '5/17/2006' Then".. as the 15th is earlier than the 17th, the If condition isn't true.

[roubesh14]

ok thanks.....lol

[roubesh14]

man i'am getting another problem

lateness = pbRst!datereturned - pbRst!expecteddate

pbRst!datereturned = 5/30/2006 and pbRst!expecteddate =5/17/2006

when the above is exeuted lateness becomes 1/12/1900

how do i do o get the difference in number of days ie 13

thanks for your replies guys coz u r helping me a lot

[si_the_geek]

You need to use the DateDiff function, eg:

lateness = datediff("d",pbRst!datereturned,pbRst!expecteddate)

(if the value is negative, just swap the fields around - or use the Abs function).

[roubesh14]

thanks man

[Al42]

And there's no reason to have that Else in there just before the End. Execution will go to the End if the condition is false. You're just creating a mess of unused (but executed) code in the exe. ("If condition = false then goto" type instructions, and the labels for them to go to.)