If statement in vba using Date

[Secret Squirrel]

Can anyone tell me why this isn't working? Is there something special I need
to do to compare dates to see if one is greater than the other?

If Me.DueDate <= Me.MaterialDueDate Then
MsgBox "This order will be late based on the customer due date vs
material due date."
End If

 

[Dirk Goldgar]

Can anyone tell me why this isn't working? Is there something special I
need
to do to compare dates to see if one is greater than the other?

If Me.DueDate <= Me.MaterialDueDate Then
MsgBox "This order will be late based on the customer due date vs
material due date."
End If

I don't see anything obviously wrong with it, so long as DueDate and
MaterialDueDate are both date/time fields, and neither is Null. *Are* they
both date/time fields? Note that simple unbound controls won't be
understood as date fields unless you have applied one of the date/time
formats to them. And you probably should allow for the possibility that one
or the other is Null.

What do you mean by "not working"? What happens when the code runs, and
what are the values of the controls at the time?

 

[BruceM]

Do you mean you are not seeing a message box under any circumstances? How
are you running the code? Did you try stepping through the code? What
happens if you substitute > for <= ? Are both fields date/time fields?

 

[Secret Squirrel]

They are both bound fields set to date/time format and they do have date
values in them. I tested it by putting the material due date greater than the
due date to trigger the msgbox but I don't get the msgbox. This code fires in
the after update event of the material due date control.

 

[Dirk Goldgar]

They are both bound fields set to date/time format and they do have date
values in them. I tested it by putting the material due date greater than
the
due date to trigger the msgbox but I don't get the msgbox. This code fires
in
the after update event of the material due date control.

So you entered a date in DueDate, and then typed a later date into
MaterialDueDate, and you didn't get the message box? If that's true, the
next thing to do would be to find out if the event procedure is executing at
all. Set a breakpoint in that procedure -- on the Sub header line, or maybe
on the If statement -- and then try entering a value in the MaterialDueDate
control. Is the breakpoint triggered? If so, step through the code from
there, checking the values of the controls, and see what happens. If the
breakpoint doesn't fire, then make sure that (a) the MaterialDueDate
control's AfterUpdate property is set to "[Event Procedure]", and (b) VBA
code is enabled in this database. Does other VBA code work?

One thing to note: if you are assigning a value MaterialDueDate using code
(or, I think, a macro), the control's AfterUpdate event won't fire. Could
that be what's going on?

 

[Secret Squirrel]

Found the problem. I'm using a popup calendar to select the date for the
material due date control. When I select the date from the calendar it
populates the control with the date I selected. But if I type the date in the
control manually then the code fires. Why would it not work when I select a
date from the popup calendar?

They are both bound fields set to date/time format and they do have date
values in them. I tested it by putting the material due date greater than
the
due date to trigger the msgbox but I don't get the msgbox. This code fires
in
the after update event of the material due date control.

So you entered a date in DueDate, and then typed a later date into
MaterialDueDate, and you didn't get the message box? If that's true, the
next thing to do would be to find out if the event procedure is executing at
all. Set a breakpoint in that procedure -- on the Sub header line, or maybe
on the If statement -- and then try entering a value in the MaterialDueDate
control. Is the breakpoint triggered? If so, step through the code from
there, checking the values of the controls, and see what happens. If the
breakpoint doesn't fire, then make sure that (a) the MaterialDueDate
control's AfterUpdate property is set to "[Event Procedure]", and (b) VBA
code is enabled in this database. Does other VBA code work?

One thing to note: if you are assigning a value MaterialDueDate using code
(or, I think, a macro), the control's AfterUpdate event won't fire. Could
that be what's going on?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Found the problem. I'm using a popup calendar to select the date for the
material due date control. When I select the date from the calendar it
populates the control with the date I selected. But if I type the date in
the
control manually then the code fires. Why would it not work when I select
a
date from the popup calendar?

As I said at the end of my previous message:

When you assign a value to a control with VBA code, the control's
BeforeUpdate and AfterUpdate events don't fire. They only fire when the
control's value is changed by direct user interaction with the control.
Therefore, if you assign the value using your poopup calendar, you must
follow the statement that assigns the value with another one that explicitly
calls the control's AfterUpdate event procedure.

I don't know exactly how your popup calendar works, but here's an example
where we just assign a literal value to MaterialDueDate, then call the event
procedure:

Me.MaterialDueDate = #1/1/2010#
Call MaterialDueDate_AfterUpdate

 

[Larry Linson]

Found the problem. I'm using a popup calendar ...
if I type the date in the control manually then the
code fires. Why would it not work when I select
a date from the popup calendar?

The After Update Event does not fire when the value of the Control is set
from VBA code.

So, if the popup calendar is one of the many available implemented with
Access Forms and VBA code, that would explain it. If, on the other hand, it
is an ActiveX Control, it has its own Events and would have to interact with
the Text Box Events, which it seems not to have been implemented to do.

Larry Linson
Microsoft Office Access MVP

 

[Secret Squirrel]

The popup calendar is in fact one that was implements with Access Forms. I
totally understand now why it won't work when I use it. I will find a
work-a-round for it to make it work. Thanks Larry & Dirk.

SS

 

[Rakesh Sharma]

The popup calendar is in fact one that was implements with Access Forms. I
totally understand now why it won't work when I use it. I will find a
work-a-round for it to make it work. Thanks Larry & Dirk.

SS









- Show quoted text -

Hi,

I think when we use the calender control for picking date value......
it put the values in mm/dd/yyyy hh:mm:ss format ( including time). You
need to get the date out of the complete date & time value

DATESERIAL(YEAR(Me.MaterialDueDate ),MONTH(Me.MaterialDueDate ),DATE
(Me.MaterialDueDate ))

after getting the date value, you can compare the date.

Regards
Rakesh

 

[John W. Vinson]

I think when we use the calender control for picking date value......
it put the values in mm/dd/yyyy hh:mm:ss format ( including time). You
need to get the date out of the complete date & time value

DATESERIAL(YEAR(Me.MaterialDueDate ),MONTH(Me.MaterialDueDate ),DATE
(Me.MaterialDueDate ))

That'll work, but so (obscurely) will Datevalue(Me.MaterialDueDate)