Month plus format

[BobNTN]

I saw this somewhere on here but can't find it.

=Format(NOW(),"mmm yyyy")
gives the current month(Feb) and current year(2008), how do I get next month(Mar) and current year(2008) ?

Thanks in advance

 

[pbaldy]

You can use the DateAdd function to add a month to the current date.

 

[BobNTN]

Well I tried =DateAdd("mmm",1,Date())
and diff variations but to no luck. It works with "yyyy" instead of "mmm" ???

What I need is in Feb to show Mar 2008, and so on.

 

[BobNTN]

Well I tried =DateAdd("mmm",1,Date())
and diff variations but to no luck. It works with "yyyy" instead of "mmm" ???

What I need is in Feb to show Mar 2008, and so on.

It appears I need to put these two together but can't figure how

=DateAdd("m",1,Date()) and
=Format(NOW(),"mmm yyyy")

 

[pbaldy]

Replace Now() with the DateAdd function.

 

[BobNTN]

and it works

=Format(DateAdd("m",1,Date()),("mmm yyyy"))



Thanks Paul (again)

 

[boblarson]

and it works

=Format(DateAdd("m",1,Date()),("mmm yyyy"))

Just an FYI for you -

You don't need the parentheses around the date format:

=Format(DateAdd("m",1,Date()),"mmm  yyyy")

is quite okay.

 

[BobNTN]

Thanks Bob

Now that works.
Is there a way to format that will ...
If the current month is in qtr1, it will show as the qtr2 months as Apr May Jun, or if in qtr2 will show Jul Aug Sep and so on ?

 

[boblarson]

Do you only want the records for the current quarter?

 

[BobNTN]

No, I'm using these formats in a text field on a form and report as...
If bills are printed in say Feb, then I'm using this field to automatically show that the service period is for the upcoming month since service is billed in advance.
Then for the quarterly bills, if bills are done in March, the field would have Apr May Jun. Just an idea I had. I place the field on the form / report just after the text "Service period".

 

[boblarson]

I'm sure you can, I just can't think of it at the moment.

 

[BobNTN]

How about

=Format(DateAdd("Q",1,Date()),"mmm mmm mmm yyyy")

????


No, That gave me May May May 2008

 

[BobNTN]

Well I've tried everything on this.
Don't think this can be done.

 

[burrcm]

=IIf([your datefield] Between #01/01/08# And #31/01/08#,"Apr May Jun",IIf([your datefield] Between #01/04/08# And #30/06/08#,"Jul Aug Sep",IIfyour datefield] Between #01/07/08# And #30/09/08#,"Oct Nov Dec","Jan Feb Mar")))

Not pretty but it will display the required text values

Chris B

 

[BobNTN]

Thanks Chris. But I would have to take the year out of that wouldn't I or have to edit it for the next year every Oct 1 ?
I suppose the question is to start with, that I don't know, is there any format available that will display the current or any quarter's specific three months ??

 

[burrcm]

Try this - =IIf(Month([MyDate]) Between 1 And 3,"Apr May Jun",IIf(Month([MyDate]) Between 4 And 6,"Jul Aug Sep",IIf(Month([MyDate]) Between 7 And 9,"Oct Nov Dec","Jan Feb Mar")))

Should work forever.

Chris B

 

[BobNTN]

Thanks Chris
I modified it to this and it appears to work automatically:

=IIf(Month(Date()) Between 1 And 3,"Apr May Jun",IIf(Month(Date()) Between 4 And 6,"Jul Aug Sep",IIf(Month(Date()) Between 7 And 9,"Oct Nov Dec","Jan Feb Mar")))

For anyone 'listening' out there, I just put this in the Control Source for the text box. Based on the current date, it displays the next quarter's months.

 

[boblarson]

Way to go Chris!

 

[BobNTN]

I agree and mana is deserved!

Bob (or whoever),
If I open forms thru a command button, do they need to be specifically closed before opening another or does Access automatically close them as you open another ?

 

[boblarson]

I agree and mana is deserved!

Bob (or whoever),
If I open forms thru a command button, do they need to be specifically closed before opening another or does Access automatically close them as you open another ?

You need to account for them, Access won't do it for you.