Can someone help me I'm having some problem with this. I am supposed to make a sytem that will alert if the medicine was expired.

here is my code.

 Private Sub DataGrid1_Click()
    On Error Resume Next
    Command2.Enabled = True
    With frmmed1
        .Combo1 = Adodc1.Recordset.Fields("MedicineName")
        .Text3.Text = Adodc1.Recordset.Fields("StockQuantity")
        .Combo2 = Adodc1.Recordset.Fields("Expmonth")
        .Combo3 = Adodc1.Recordset.Fields("Expday")
        .Combo4 = Adodc1.Recordset.Fields("Expyear")
    End With

    Dim currentdate As Date
    Dim expirationdate
    currentdate = DateValue(Now)
    expirationdate = Combo2 & Combo3 & Combo4

  datepicker.value = Format(Now, "MMM-DD-yyyy")

    If datepicker < expirationdate Then (-----I'm having trouble in this line---)
        MsgBox "OK! =)", , "Working"
         Text2.Enabled = True

        MsgBox "Medicine Expired! Click OK to delete.", vbExclamation, "   Warning!!!"
    Text2.Enabled = False

        If MsgBox("Are you want to delete " & Combo1 & "?", vbQuestion + vbYesNo, "Message") = vbYes Then
    ElseIf vbNo Then Exit Sub
    End If
    End If

    End Sub

Edited by micca.jhai: typo error

3 Years
Discussion Span
Last Post by BitBlt

Can't see how you declared 'expirationdate'. It looks! like 'datepicker.value' is a date where as expirationdate is string?


When you create your expirationdate string from the values from combo2, combo3 and combo4, it comes out with no formatting. The default value returned by the datepicker is a formatted date. Here's a little code snippet that you can use to demonstrate this:

Debug.Print "Datepicker: " & datepicker
Debug.Print "expirationdate: " & expirationdate

The results come out like this:

Datepicker: 12/13/2013
expirationdate: 12132013

So, you need to include formatting when you concatenate "expirationdate" together, like so:

expirationdate = Me.Combo2 & "/" & Me.Combo3 & "/" & Me.Combo4

Now, you have to make sure that you're using date comparison rather than string comparison. for instance, the string "12/14/2012" is greater than the string "12/13/2013" but you can obviously see that as dates, the first is definitely less than the second. Try using something like:

If DateValue(datepicker) < DateValue(expirationdate) then...

Hope that helps! Good luck, and happy coding!

Edited by BitBlt

This article has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.