Date Comparison in Access 2002 using VBA

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Karl Gibbon - 21 Feb 2005 10:46 GMT

Hi There,
I currently have a database in Access 2002 with several
forms. I would like to restrict access to one form in perticular until
November 1st every year. My current method (attempted method) is a
heavy handed, no nonsense, direct date comparison using VBA.

Function NovemberOnwards()
'Use with 'On Avtivate' on Orders form

CurrentDate = Date 'I cannot decide which function is best to use
'to get the current date

If CurrentDate < #11/1/2005# Then
Forms![Orders].Visible = False
End If
End Function

Any suggestions are welcome and thanks is given in advance,

Karl Gibbon

Reply to this Message

Wayne Morgan - 21 Feb 2005 10:59 GMT

This should work and will automatically fill in the year for you.

If Date < DateSerial(Year(Date), 11, 1) Then

Also, your method will hide the form, but still allow it to be opened. If
you want to prevent it from even being opened, you could make the same check
in the form's Open event and set Cancel=True if the date doesn't fit.

Example:
Cancel = (Date < DateSerial(Year(Date), 11, 1))

Signature

Wayne Morgan
MS Access MVP

> Hi There,
> I currently have a database in Access 2002 with several
[quoted text clipped - 16 lines]
>
> Karl Gibbon

Reply to this Message

Karl Gibbon - 22 Feb 2005 09:17 GMT

> This should work and will automatically fill in the year for you.
>
[quoted text clipped - 6 lines]
> Example:
> Cancel = (Date < DateSerial(Year(Date), 11, 1))

Indeed your method does work, however there seems to be slight flaw in
it somewhere. Upon opening the database i am greeted by the
switchboard which contains several buttons to open different forms,
one of which is the 'Orders' form which is the one i am concerend with
preventing access to before 1st November every year. I click on the
button to open this 'Orders' form and for some reason it is allowed to
open. I then open the module to view the code, and run it directly
from there using the 'Run' button and the recently opened 'Orders'
form is closed. The code will then work every time after that (I'm not
sure on this but i think it might be because the Date function can't
get the date for some reason unless the code is directly run ). I
don't see the point however, in having a piece of code to prevent
access to a particular form if it will only work if the code has
already been directly run.

Thanks for your suggestion Wayne and hope you can help me with this
new little problem.

Thanks also to anyone else who is kind enough to reply,

Karl Gibbon

Reply to this Message

Wayne Morgan - 22 Feb 2005 10:41 GMT

I can't reproduce it here. The code works, even if I try to open the form
from the switchboard immediately after opening the database. Do you have any
other code in the Open event of the form that may be causing the problem?

Signature

Wayne Morgan
MS Access MVP

> Indeed your method does work, however there seems to be slight flaw in
> it somewhere. Upon opening the database i am greeted by the
[quoted text clipped - 10 lines]
> access to a particular form if it will only work if the code has
> already been directly run.

Reply to this Message