Same Day, Different Month

[ctgrib]

I need to calculate a date field that takes the current date, subtracts a month and then returns a date equal to "new month/20/year". For instance, the date I would return for today is 02/20/04. Any help is appreciated!!

 

[PerkinsSlave]

I'm doing something identical in a program I am developing.

Private Sub Whatever()

Dim MyMonth As Integer

MyMonth = Month(Date)
MyMonth = MyMonth - 1

End Sub

 

[Joe4]

If you are trying to return the date in a calculated query field, simply use:
MyDate: DateSerial(Year(Now()),Month(Now())-1,20)

 

[ctgrib]

Thanks, the second suggestion does exactly what I needed. Curious, can I use this same language to add a month (substituting "+" for "-")? I tested and cannot get it to work . . .

 

[ctgrib]

Let me give you some additional information . . .I am not using the Now() as I am actually trying to create the new date from a date already populated. So, I am trying to add one month, and change the day using:

MyDate: DateSerial(Year([Contract Effective Date]), Month([Contract Effective Date]+1),20)

For a contract effective date of 04/01/04, it is returning MyDate as 04/20/04. Any ideas?

 

[Joe4]

You have a parenthesis is the wrong place in the month portion. Try:

MyDate: DateSerial(Year([Contract Effective Date]), Month([Contract Effective Date])+1,20)

As you had it written, it would add 1 day to the Contract Effective Day and take that month. Adding 1 day to 4/1/04 would give you 4/2/04, and taking that month would give you 4.

 

[mdmilner]

-this is really a tip, and if your above works, it is unnecesary.

Something else to try should your date fields go awry.
I never had the curiosity to look to see why this worked, but I noticed that sometimes, when pulling a date from a field functions that should work did not...until...I used the Trim() function before comparing it or performing calculations.

So something like:

MyDate: DateSerial(Year([Contract Effective Date]), Month([Contract Effective Date]+1),20)

Would become:

MyDate: DateSerial(Year(TRIM([Contract Effective Date])), Month(TRIM([Contract Effective Date])+1),20)

Again, I'm not sure what odd condition this fixes (sometimes). It might be something quirky about how the field was created, etc etc.

Just something to try when it appears that it *should* work.

Mike

 

[Joe4]

Mike,

Interesting. But it appears that the problem is simply a problem with an incorrect formula (see my previous post).

 

[PerkinsSlave]

Thanks for the replies everyone (even though this wasn't my question!) Using the following statement:

DateSerial(Year(Now()),Month(Now()),1-1)

Solves a problem I had in setting the correct date for the last day of the previous month... and eliminates a LOT of extraneous If...Then code!