Display prior month in a text box in an Access report?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Adriana - 12 Mar 2007 20:18 GMT

I have a report that will be based on different queries which I will insert
as subreports. For the month on the report, I need it to be one month prior
and current year. I read other user's questions in the community, but
nothing works. What is the date formula for something like this?

Reply to this Message

fredg - 12 Mar 2007 22:15 GMT

> I have a report that will be based on different queries which I will insert
> as subreports. For the month on the report, I need it to be one month prior
> and current year. I read other user's questions in the community, but
> nothing works. What is the date formula for something like this?

To get the prior month and year from today's date is quite easy but
we're not mind readers.
How would any of us know what you've read or tried, so why possibly
give you the same information all over again?

Also, your post is not very clear.
Exactly what is the "it" in 'I need it to be one month prior....'?

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

Adriana - 12 Mar 2007 22:21 GMT

"It" means that I will insert a text box with a date formula for one month
prior and the current year. An unbound text box, of course. Sorry for the
vague description.

> > I have a report that will be based on different queries which I will insert
> > as subreports. For the month on the report, I need it to be one month prior
[quoted text clipped - 8 lines]
> Also, your post is not very clear.
> Exactly what is the "it" in 'I need it to be one month prior....'?

Reply to this Message

fredg - 13 Mar 2007 01:19 GMT

> "It" means that I will insert a text box with a date formula for one month
> prior and the current year. An unbound text box, of course. Sorry for the
[quoted text clipped - 12 lines]
>> Also, your post is not very clear.
>> Exactly what is the "it" in 'I need it to be one month prior....'?

OK, so you have a Date field (DateTime datatype) and you wish to
display just the Month Year previous to what ever that date is?

Let's display the month as a 3 character value, i.e. Jan Feb Mar etc.

= Format(DateAdd("m",-1,[DateFieldName]),"mmm yyyy")

will display as Jan 2007 if the date value was anytime in February
2007.

Change [DateFieldName] to whatever the actual name of your date field
is.

If you want to show the month previous to the current month, then you
would use:

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

will display Feb 2007 this month.

Note: the year will automatically be correct, i.e. a January 2007 date
will display as Dec 2006.

If you wish the month name in full then use:
"mmmm yyyy"

If you wish just the month as a number value, then use
"mm yyyy"

I hope this helps.

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

Adriana - 13 Mar 2007 15:38 GMT

Thank you very much, this formula worked the best: =
Format(DateAdd("m",-1,Date()),"mmm yyyy")
I had something similar from a query I used, but it was too complicated and
quite longer and I kept coming up with an error. You simplified it for me, so
now I understand the order. Thanks again fredg!

> > "It" means that I will insert a text box with a date formula for one month
> > prior and the current year. An unbound text box, of course. Sorry for the
[quoted text clipped - 43 lines]
>
> I hope this helps.

Reply to this Message