Checking the current date against a range of dates

S

steve1951

I have written and use a database to control a maintenance contract. I have
written a bit of code below to remind the user that a report needs to be
compiled.
Function Reminder()
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "You need to submit the ASIPT Quarterly Return by the last working day
of this month! Do you wish to compile the report now?" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 'Define buttons.
Title = "ASIPT Reminder" ' Define title.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbNo Then Call ReminderNo ' user chose no
If Response = vbYes Then Call ReminderYes ' User chose Yes.

End Function
Function ReminderYes()
DoCmd.OpenForm "ASIPT Return", acNormal, "", "", , acNormal
DoCmd.Maximize
End Function
Function ReminderNo()
DoCmd.OpenForm "Main menu", acNormal, "", "", , acNormal
DoCmd.Maximize

End Function
Function TestDate()
'Tests current date against the periods the reports are due,
'if it is the reminder function is run
If Now >= ("28 August") And Now <= ("3 September") Or Now >= ("28 November")
And Now <= ("3 December") Or Now >= ("26 February") And Now <= ("3 March") Or
Now >= ("28 May") And Now <= ("3 June") Then Call Reminder Else Call
ReminderNo
End Function
The function test date is called up from the autoexec macro and it all works
fine for all years!

I have been trying to not display the reminder if the report has been run.
The report is generated from vb and amongst other things it puts the Now
value into a table................... should I use Date() instead?

I have tried to use the function below to test the ReportDate but everything
goes pearshaped!!!
Function CheckDate()
Dim ReportDate As Date
ReportDate = Forms!ReportDate!ReportDate

If ReportDate >= ("28 August") And ReportDate <= ("3 September") Or
ReportDate >= ("28 November") And ReportDate <= ("3 December") Or ReportDate
= ("26 February") And ReportDate <= ("3 March") Or ReportDate >= ("28 May")
Click to expand...
And ReportDate <= ("3 June") Then Call ReminderNo Else Call Reminder

End Function

Please has anyone got a neat idea for this????????????
Steve
 
K

Klatuu

Steve,
You have several problems that need to be addressed.
First, all your variables are Dimmed as Variant data types. Variant data
types are usfule, but should be used judiciously. Specifically typing your
variables is more efficient. You have to explicitly define the data type for
each variable you Dim. for example:

Dim SomeVariable, AnotherVariable, AnyVariable As String

Does not dim all three as String. only AnyVariable is dimmed as string.
The others are variants. The correct form would be:

Dim SomeVariable As String, AnotherVariable As String, AnyVariable As String

Although that is technically correct, I personally prefer each variable be
dimmed on a line by itself. I find it easier to read and change, if
necessary.

As to your question whether to use Date or Now. Now should only be used if
you need to reference the Time portion of a date. That is, if you need to
know if it is after 1:00 PM, for example. If you only care about a certain
day, use Date. The problem you will have is that when comparing dates to
date fields with a time is that you may not include something you want. For
example, except at exactly midnight, down to the second, ?Day < Now will
always return True. Day does contain a time component, but it is always
12:00:00 AM

You are also comparing Now to a string that does translate to a date, but
you will not get what you are expecting. Here is an example that will show
you the results:

?Date < ("28 August")
False
?Date = ("28 August")
False
?Date > ("28 August")
True

?Date < DateValue("28 August")
True
?Date = DateValue("28 August")
False
?Date > DateValue("28 August")
False

Here is a suggestion for you:

Function Reminder()
Dim strFormToOpen As String

If Not CheckDate Then
strform = "Main menu"
Else
If MsgBox("You need to submit the ASIPT Quarterly Return by the " & _
"last working day of this month! " & vbNewLine & _
"Do you wish to compile the report now?", _
vbYesNo + vbCritical + vbDefaultButton2, _
"ASIPT Reminder") = vbYes Then
strFormToOpen = "ASIPT Return"
Else
strFormToOpen = "Main menu"
End If
End If
DoCmd.OpenForm strFormToOpen

End Function

Function CheckDate() As Boolean

Select Case Forms!ReportDate!ReportDate
Case IsBetween(DateValue("26 February"), DateValue("3 March"))
CheckDate = True
Case IsBetween(DateValue("28 May"), DateValue("3 June"))
CheckDate = True
Case IsBetween(DateValue("28 August"), DateValue("3 September"))
CheckDate = True
Case IsBetween(DateValue("28 November"), DateValue("3 December"))
CheckDate = True
Case Else
CheckDate = False
End Select

End Function

And this little utility from my Bag Of Tricks (put it in a standard module
and you can use it anywhere in your app:

Public Function IsBetween(varCheckVal As Variant, varLowVal As Variant, _
varHighVal As Variant) As Boolean
IsBetween = (varCheckVal >= varLowVal And varCheckVal <= varHighVal)
End Function

Note. put the Docmd.Maximize in the Activate event of the forms you are
opening.
 
S

steve1951

Klatuu said:
Steve,
You have several problems that need to be addressed.
First, all your variables are Dimmed as Variant data types. Variant data
types are usfule, but should be used judiciously. Specifically typing your
variables is more efficient. You have to explicitly define the data type for
each variable you Dim. for example:

Dim SomeVariable, AnotherVariable, AnyVariable As String

Does not dim all three as String. only AnyVariable is dimmed as string.
The others are variants. The correct form would be:

Dim SomeVariable As String, AnotherVariable As String, AnyVariable As String

Although that is technically correct, I personally prefer each variable be
dimmed on a line by itself. I find it easier to read and change, if
necessary.

As to your question whether to use Date or Now. Now should only be used if
you need to reference the Time portion of a date. That is, if you need to
know if it is after 1:00 PM, for example. If you only care about a certain
day, use Date. The problem you will have is that when comparing dates to
date fields with a time is that you may not include something you want. For
example, except at exactly midnight, down to the second, ?Day < Now will
always return True. Day does contain a time component, but it is always
12:00:00 AM

You are also comparing Now to a string that does translate to a date, but
you will not get what you are expecting. Here is an example that will show
you the results:

?Date < ("28 August")
False
?Date = ("28 August")
False
?Date > ("28 August")
True

?Date < DateValue("28 August")
True
?Date = DateValue("28 August")
False
?Date > DateValue("28 August")
False

Here is a suggestion for you:

Function Reminder()
Dim strFormToOpen As String

If Not CheckDate Then
strform = "Main menu"
Else
If MsgBox("You need to submit the ASIPT Quarterly Return by the " & _
"last working day of this month! " & vbNewLine & _
"Do you wish to compile the report now?", _
vbYesNo + vbCritical + vbDefaultButton2, _
"ASIPT Reminder") = vbYes Then
strFormToOpen = "ASIPT Return"
Else
strFormToOpen = "Main menu"
End If
End If
DoCmd.OpenForm strFormToOpen

End Function

Function CheckDate() As Boolean

Select Case Forms!ReportDate!ReportDate
Case IsBetween(DateValue("26 February"), DateValue("3 March"))
CheckDate = True
Case IsBetween(DateValue("28 May"), DateValue("3 June"))
CheckDate = True
Case IsBetween(DateValue("28 August"), DateValue("3 September"))
CheckDate = True
Case IsBetween(DateValue("28 November"), DateValue("3 December"))
CheckDate = True
Case Else
CheckDate = False
End Select

End Function

And this little utility from my Bag Of Tricks (put it in a standard module
and you can use it anywhere in your app:

Public Function IsBetween(varCheckVal As Variant, varLowVal As Variant, _
varHighVal As Variant) As Boolean
IsBetween = (varCheckVal >= varLowVal And varCheckVal <= varHighVal)
End Function

Note. put the Docmd.Maximize in the Activate event of the forms you are
opening.
Click to expand...
"Klatuu"
many thanks for this it works like a dream and very helpful advice. On the
advice front can you recommend a VB book for the absolute beginner as I am
very much self taught ......... badly!!!
Regards
Steve
 
S

steve1951

steve1951 said:
"Klatuu"
Click to expand...
Hi I have just tried the above on my laptop Vista and access 2003 I am
running the reminder function from the autoexec macro but it fails on the
checkdate function with the IsBetween function call highlighted and an
Argument not optional compile error message. I have put the IsBetween in its
own module and tried running it with the ReportDate Form open and closed but
no joy at all......... Sorry but totally lost, obviously need a lesson in
VB! Steve
 
K

Klatuu

I don't know of a good VBA beginner book. That is why I am working on one.
But, here is a site that you may find helpful:

http://www.allenbrowne.com/casu-22.html

As to the InBetween function, my mistake. It needs 3 arguments.
The first is the value to check, the second is the low end of the range, and
the third is the high value of the range. This should correct the problem:

Function CheckDate() As Boolean
Dim dtmCheckDate as Date

dtmCheckDate = Forms!ReportDate!ReportDate

Select Case dtmCheckDate
Case IsBetween(dtmCheckDate, DateValue("26 February"), DateValue("3
March"))
CheckDate = True
Case IsBetween(dtmCheckDate, DateValue("28 May"), DateValue("3 June"))
CheckDate = True
Case IsBetween(dtmCheckDate, DateValue("28 August"), DateValue("3
September"))
CheckDate = True
Case IsBetween(dtmCheckDate, DateValue("28 November"), DateValue("3
December"))
CheckDate = True
Case Else
CheckDate = False
End Select

End Function
 
S

steve1951

Klatuu said:
I don't know of a good VBA beginner book. That is why I am working on one.
But, here is a site that you may find helpful:

http://www.allenbrowne.com/casu-22.html

As to the InBetween function, my mistake. It needs 3 arguments.
The first is the value to check, the second is the low end of the range, and
the third is the high value of the range. This should correct the problem:

Function CheckDate() As Boolean
Dim dtmCheckDate as Date

dtmCheckDate = Forms!ReportDate!ReportDate

Select Case dtmCheckDate
Case IsBetween(dtmCheckDate, DateValue("26 February"), DateValue("3
March"))
CheckDate = True
Case IsBetween(dtmCheckDate, DateValue("28 May"), DateValue("3 June"))
CheckDate = True
Case IsBetween(dtmCheckDate, DateValue("28 August"), DateValue("3
September"))
CheckDate = True
Case IsBetween(dtmCheckDate, DateValue("28 November"), DateValue("3
December"))
CheckDate = True
Case Else
CheckDate = False
End Select

End Function
Click to expand...
Sorry about this it's me again with another problem.......
Now it comes up with Automation Erroe The object invoked has disconnected
from its clients. I the debug the IsBetween function has this line
highlighted............ IsBetween = (varCheckVal >= varLowVal And varCheckVal
<= varHighVal)
Apologies once again
Regards
Steve
 
K

Klatuu

No need to apologize.
I don't ever remember seeing that error at all. Very strange.
Have you compiled the VBA code?
From the VBE menu Debug, Compile
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

how to write VBA for DELETE RECORD? 3
Compare shorter date range against longer date range 2
Multiple supervisors over a date range in query 1
Insert Multiple Files into a field 3
passing an array to another subroutine 2
Automation of report, daily, monthly then yearly as per need 1
Code Stops Due to No Values In Query 5
Beep in a MsgBox 2

Top