Get date and compare to another date field

[Guest]

Hello. I've got an Access query that is based on the following logic:

If the day and year of the information was entered is later than the day and
year of the billing due date, the next date to be billed will be one month
after the date the information was entered. If the day and year are earlier,
the next billing date will be the same month as the date the infomation was
entered.

My problem is I can't get the expression right. I keep getting compile
errors. This is what I have.

Next Due Date: IIf(Format([Date Picked Up Table].[Date Picked Up],"mm") &
Format([Next Payment Date],"/dd/yy")<[Date Picked Up Table].[Date Picked
Up],Format(Format([Date Picked Up Table].[Date Picked Up],"mm")+1 &
Format([Next Payment Date],"/dd/yy"),"mm/dd/yy"),Format([Date Picked Up
Table].[Date Picked Up],"mm") & Format([Next Payment Date],"/dd/yy"))

The formula was working before, but I had to change it because it wasn't
working correctly. Example:

Date Picked Up Last Date Billed Next Due Date will be:
12/01/04 08/25/04 12/25/04
05/15/04 03/05/04 06/05/04

Hopefully the examples I gave will help someone here understand my problem.

 

[George Nicholson]

The formula was working before, but I had to change it because it wasn't

working correctly.

Um, it was either working before or it wasn't....

Next Due Date: IIf([DatePickedUp]>[NextPaymentDate], DateAdd("m", 1,
[DatePickedUp]), DateSerial(Year([DatePickedUp]),Month([DatePickedUp]),25)

Hope this helps, or at least gives you some ideas...
--
George Nicholson

Remove 'Junk' from return address.

Hello. I've got an Access query that is based on the following logic:

If the day and year of the information was entered is later than the day
and
year of the billing due date, the next date to be billed will be one month
after the date the information was entered. If the day and year are
earlier,
the next billing date will be the same month as the date the infomation
was
entered.

My problem is I can't get the expression right. I keep getting compile
errors. This is what I have.

Next Due Date: IIf(Format([Date Picked Up Table].[Date Picked Up],"mm") &
Format([Next Payment Date],"/dd/yy")<[Date Picked Up Table].[Date Picked
Up],Format(Format([Date Picked Up Table].[Date Picked Up],"mm")+1 &
Format([Next Payment Date],"/dd/yy"),"mm/dd/yy"),Format([Date Picked Up
Table].[Date Picked Up],"mm") & Format([Next Payment Date],"/dd/yy"))

The formula was working before, but I had to change it because it wasn't
working correctly. Example:

Date Picked Up Last Date Billed Next Due Date will be:
12/01/04 08/25/04 12/25/04
05/15/04 03/05/04 06/05/04

Hopefully the examples I gave will help someone here understand my
problem.

 

[Guest]

What I meant was, it was working, but I wasn't getting the right data back,
i.e. if my next payment date was supposed to be 6/25/04, I would get 5/25/04.
I added the other parts in to make it work but then the formula was too long
and Access chopped the end of it off. I'm tryin' to make it shorter,
basically. I'll try your suggestion though.