DateDiff Problem

  • Thread starter Thread starter DavidW
  • Start date Start date
D

DavidW

I am trying to use:

If DateDiff("m", Me.pumpda, (DMax("[pumpda]", "[usage]"))) > 1 Then

What I was hoping For this to do is to look at a table "usage", field
"pumpda"(which is a date) and compare it to the form (which has the same
recordsource), and find out if the date that is being entered is greater
than 1 month.
Problem-
Its not working, it thinks that any date is not greater than whats in the
table.
I am wanting to limit the reports by month. They need to be month by month.
What I hope to avoid is someone skipping a month by accident?
Is this the wrong approach?
Thanks
David
 
Forgot to add, the format is mmmm yyyy of the textbox, I dont need to show
the day of the month in the reports because it is a monthly report and up
till now this has been working with the rest of all of the vba. I did this
intensionally to keep someone from putting the first day of the month in,
then have someone else come along and put the second day of the month in.
By default it selects the first day of the month which is fine.
Sorry
David
 
DavidW said:
I am trying to use:

If DateDiff("m", Me.pumpda, (DMax("[pumpda]", "[usage]"))) > 1 Then

What I was hoping For this to do is to look at a table "usage", field
"pumpda"(which is a date) and compare it to the form (which has the
same recordsource), and find out if the date that is being entered is
greater than 1 month.
Problem-
Its not working, it thinks that any date is not greater than whats in
the table.
I am wanting to limit the reports by month. They need to be month by
month. What I hope to avoid is someone skipping a month by accident?
Is this the wrong approach?
Thanks
David
Click to expand...

If your DMax expression returns a date that is earlier than Me.pumpda,
then the result of the DateDiff will be negative. Either swap the order
of the arguments:

If DateDiff("m", DMax("[pumpda]", "[usage]"), Me.pumpda) > 1 Then

or test for a negative difference:

If DateDiff("m", Me.pumpda, DMax("[pumpda]", "[usage]")) < -1 Then

or test for an absolute value of the difference:

If Abs(DateDiff("m", Me.pumpda, DMax("[pumpda]", "[usage]"))) > 1
Then
 
Back
Top