DATE ADD Function Not working Properly?? 1

[Gregro]

Thank you for your assistance with my previous query. I have now successfully used the DATEADD function, but it appears that it is not adding correctly, ie

DateAdd("m", -1, [BenchMonth]), where [BenchMonth] = "2002/02/28" returns "2002/01/28"

Any assistance in getting it to always show the last day of the month would be appreciated.

 

[GHolden]

Hi,

It looks as though it is working properly...

DateAdd("m", -1, [BenchMonth])

This adds minus one months (ie. current month minus one)

Therefore 2002/02/28 would become 2002/01/28



There are two ways to write error-free programs; only the third one works.

 

[cmmrfrds]

Make the day the 1st of the month and subtract 1 day, that will give the last of the previous month.

 

[BobJacksonNCI]

Kind of past tense, but maybe Gregro needs a specific answer to finding the last day of the previous month.

Dec 04, 2002 - Microsoft Knowledge Base Article – 210604
ACC2000: Functions for Calculating and Displaying Date/Time Values

Displaying Specific Dates

To display specific dates, you can use the DateSerial() function to manipulate the day, month, and year portions of a date. For example, you can use the following expressions in the ControlSource property of a text box or in the Immediate window to return specific dates:

· The last day of the previous month:
DateSerial(Year(Date()), Month(Date()),0)

AND, FOR FUTURE REFERENCE:

· The current month:
DateSerial(Year(Date()), Month(Date()), 1)

· The next month:
DateSerial(Year(Date()), Month(Date()) + 1, 1)

· The last day of the current month:
DateSerial(Year(Date()), Month(Date()) + 1, 0)

· The last day of the next month:
DateSerial(Year(Date()), Month(Date()) + 2, 0)

· The first day of the previous month:
DateSerial(Year(Date()), Month(Date())-1,1)

· The first day of the current quarter:
DateSerial(Year(Date()), Int((Month(Date()) - 1) / 3) * 3 + 1, 1)

· The last day of the current quarter:
DateSerial(Year(Date()), Int((Month(Date()) - 1) / 3) * 3 + 4, 0)

· The first day of the current week (assuming Sunday = day 1):
Date() - WeekDay(Date()) + 1

· The last day of the current week:
Date() - WeekDay(Date()) + 7

· The first day of the current week (using settings in Options dialog box):
Date() - WeekDay(Date(), 0) + 1

· The last day of the current week:
Date() - WeekDay(Date(), 0) + 7

 

[rccline]

What is wrong with this?

In Query:

DueDate: Format(DateAdd([d],90,[tblOrders]![Date]))

Robert

 

[scriverb]

Gregro: Common courtesy should prevail here. Five members have attempted to help you with your problem since you posted this thread on Jul 9th. There are good suggestions here and I am sure the answer to your problem. Please respond with some sort of acknowledgement as to you have received the information you were requesting or five people just don't understand your problem.

Since members took the time to answer your thread you should have the courtesy to respond back. Thank you.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[BobJacksonNCI]

What is wrong with this?

In Query:

DueDate: Format(DateAdd([d],90,[tblOrders]![Date]))

Robert

Try this:

DueDate: Format(DateAdd("d","90",[tblOrders]![Date]))

Bob