Trying to return a MsgBox if date x is after date y

[brendandaly]

I've got two text fields (they have to be text fields as they're in a dbf file) with dates in them in the format "yyyy-mm-dd" and I'm trying to have a message box pop up if one of the fields, lets call it LICEXPDATE, is the same or after the other, NEWEXPDATE. I'm not even sure if I've figured out how to format them correctly so they are recognized as dates. I've got:

Dim LICEXPDATE As Date
Dim NEWEXPDATE As Date

LICEXPDATE = Format(LICEXPDATE, "yyyy-mm-dd")
NEWEXPDATE = Format(NEWEXPDATE, "yyyy-mm-dd")

If LICEXPDATE >= NEWEXPDATE Then
MsgBox ("Updated Expiration Date must be after Original Expiration Date. Please Re-Check.")
Check19 = False
End If

so far. Any help would me greatly greatly appreciated. Thanks in advance.

 

[pbaldy]

You're setting a variable to the formatted value of itself. You need to use the value from the table/form/whatever. With the text date in that format, you may be able to use CDate() on it.

 

[brendandaly]

Ok, I'm checking out CDate() but everything I can find on it has it working on specific date expressions. I'm trying to fool around with it to figure it out, but can you provide me an example?

 

[pbaldy]

From an Immediate window test:

?cdate("2012-11-30")
11/30/2012

 

[brendandaly]

Yeah, that's exactly the problem. I need it to work on the LICEXPDATE field, not a specific date in an expression.

By the way, you've helped me before. Again, I can not possibly thank you enough for what you do.

 

[pbaldy]

Have you tried? Using a specific date is no different than a text field containing the same value.

 

[brendandaly]

Dim LICEXPDATE As String
Dim LDate As Date
Dim NEWEXPDATE As String
Dim NDate As String

LDate = CDate(LICEXPDATE)
NDate = CDate(NEWEXPDATE)

If LDate >= NDate Then
MsgBox ("Updated Expiration Date must be after Original Expiration Date. Please Re-Check.")
Check19 = False
End If

It give me a type mismatch error...

 

[nanscombe]

If you have two text fields, and they are already in yyyy-mm-dd format, why not just say

If LICEXPDATE & vbNullString >= NEWEXPDATE & vbNullString Then ' in case either is empty
  MsgBox ("Updated Expiration Date must be after Original Expiration Date. Please Re-Check.")
  Check19 = False
End If

 

[brendandaly]

Because it return the msgbox whether the date original license date is after the new one or not.

 

[brendandaly]

I figured it out. Thanks any way!

 

[brendandaly]

Dim LICEXPDATE As String
Dim LDate As Date
Dim NEWEXPDATE As String
Dim NDate As Date

LDate = CDate(Me.LICEXPDATE)
NDate = CDate(Me.NEWEXPDATE)

If LDate >= NDate Then
MsgBox ("Updated Expiration Date must be after Original Expiration Date. Please Re-Check.")
Check19 = False
End If

This worked. I needed the 'Me.' before the variable.

AWESOME. THANK YOU SO SO SO SO SO much.

 

[pbaldy]

Glad you got it sorted out. One tweak you may want to add is the Nz() function, as I think the CDate() function will error on a Null entry.