Date Checking Problem

[James Bowyer]

I am trying to create a warning if a course is due to expire in less than 2 months. I have tried this code:

TodaysDate = Now
Dim rsc As DAO.Recordset
Dim CSCSCourseExpiry As Long
Dim DayCheck As String
DayCheck = TodaysDate.Value + 62
CSCSCourseExpiry = DCount("*", "TblFittersLocal", "[CSCSExp]" < DayCheck)
If CSCSCourseExpiry > 0 Then
MsgBox CSCSCourseExpiry & " CSCS Certifications are due to expire in less than 2 months"
Else
End If

But, I am not getting the results I should. I also need to get it to ignore blanks.

[James Bowyer]

Upon reading my post, it doesn't make a huge ammount of sense, so look at this code instead

Dim DayCheck As String
DayCheck = TodaysDate.Value + 62

MsgBox DayCheck

Dim PIACourse As String
PIACourse = DLookup("[PIAExp]", "TblFittersLocal", "[ID] = SelectFitter.value")

MsgBox PIACourse

If PIACourse < DayCheck Then
MsgBox "Less"
Else
MsgBox "More"
End If

This always gets a result of "less" even when its more.

Why?!

PS. I have also tried Changing the "Now" for "Date" to see if that makes a difference, and it doesn't.

[James Bowyer]

I have revised the code to include the datediff function as follows:

CSCSCourseExpired = DCount("[CSCSExp]", "TblFittersLocal", "DateDiff('d'," & TodaysDate & ",[CSCSExp]) < 0")

But, this still doesn't work properly, either finding all the cells with filled in date values (>0) or stating that all dates are ok (<0) when they aren't.

What am I doing wrong??

[James Bowyer]

At a risk of talking to myself... I fixed it!

As follows

CSCSCourseExpired = DCount("[CSCSExp]", "TblFittersLocal", "[CSCSExp] < #" & DayCheck1 & "#")

Hopefully this will help someone else!

[NeoPa]

Now() is not today's date James. Now() is now (date and time). You can come unstuck if you try to treat one as the other. In fact, it's one of the most frequently occurring confusions we come across.

For today's date always use Date().