CODEWITHSUNDEEP
vbams-access
user681413
user681413

Reputation: 49

Date check started failing

I have a Date check in a MS Access DB I built that has been fine for months but today is throwing out constant positives regardless of input. I've checked the outputs and they are displaying the correct week/year every time but the check is failing. It's like overnight it has just decided to read the dates differently.

If Not IsNull(Raised_For) Then
  If Not Format(Me.Raised_For, "WW/YYYY", vbMonday) > Format(Date, "WW/YYYY", vbMonday) Then
  MsgBox "You must enter a date beyond the current week", vbOKOnly, "Past Date"
  Cancel = True
 End If
End If

Example of the check 10/2018 > 9/2018 comes out as False.

I'm really stumped on why this has started happening today.

Upvotes: 1

Views: 47

Answers (3)

Gustav
Gustav

Reputation: 55906

Compare using DateDiff:

If Not IsNull(Me!Raised_For) Then
    If DateDiff("ww", Me.Raised_For, Date, vbMonday) <= 0 Then
        MsgBox "You must enter a date beyond the current week", vbOKOnly, "Past Date"
        Cancel = True
    End If
End If

Upvotes: 0

Vityata
Vityata

Reputation: 43595

As mentioned by @Erik von Asmuth, you are comparing strings, although the Format() part of the code. To compare the dates, try to convert the value to date and check it:

If Not Cdate(Me.Raised_For) > CDate(Date) Then

This should be enough. Consider an error catcher, to help you if Me.Raised_For is not a valid date.

Upvotes: 0

Erik A
Erik A

Reputation: 32682

Quite simple:

Since you're comparing strings, you're doing a lexical comparison. Since the first letter of your first string, 1, is less than the first letter of your second string, 9, the comparison returns false.

Use DateDiff to compare dates:

If DateDiff("ww",  Date(), Me.Raised_For, vbMonday) > 0 Then
   'Do Stuff
End If

Upvotes: 3

Related Questions