using calculated date in if statement

  • Thread starter Thread starter KarenM
  • Start date Start date
K

KarenM

I would like to use a variable that is a date, strStartDate = "#01/01/" &
Year(Now) - 7 & "#" in an if statement. Do I need to include the # again or
is it sufficient to include it in the variable?

For example:
If ActiveXCtl26.Value < strStartDate Then
MsgBox "Please select a date later than " & strStartDate
ActiveXCtl26.Value = strStartDate
Exit Sub
End If

Thank you in advance for your help,
Karen
 
Assuming ActiveXCtl26 is a calendar control, you'd be better off using a
Date field, not a String field:

dtmStartDate = DateSerial(Year(Now) - 7, 1, 1)
 
It depends.

In your sample code, you're actually trying to compare dtmStartDate to the
value of ActiveXCtl26, so all you'd need should be

If ActiveXCtl26.Value < dtmStartDate Then
MsgBox "Please select a date later than " & dtmStartDate
ActiveXCtl26.Value = dtmStartDate
Exit Sub
End If

If you were using the value of dtmStartDate in a SQL statement, then you'd
want to add the # delimiters. As well, in case the application is used by
others whose Short Date format cannot be known, you'd be best off ensuring
that the string representation of the date is in a format Access will
respect, so you'd be best off using

Format(dtmStartDate, "\#yyyy\-mm\-dd\#")
 
Back
Top