Mtd Question

I have a built queries to give me monthly totals, but the problem is that by
using Month(Now()), I get the current month when it is the 1st day of the
month instead of last month's data. I imagine that I will have the same
problem with the Ytd data. I got around the Daily data problem by using the
Date()-1 function, but I can't seem to find how to use it for month and year.
Any suggestions would be appreciated.

Thank you
Eric

Eric,

Now()-Day(Now())

will always return the last day of the previous month, so:

Month(Now()-Day(Now()))

will always return the month of the previous month.

HTH,
Nikos

"CR" wrote in message
news
I have a built queries to give me monthly totals, but the problem is that
by
using Month(Now()), I get the current month when it is the 1st day of the
month instead of last month's data. I imagine that I will have the same
problem with the Ytd data. I got around the Daily data problem by using
the
Date()-1 function, but I can't seem to find how to use it for month and
year.
Any suggestions would be appreciated.

Thank you
Eric

That works great thank you, however, if it isn't the 1st day of the month, I
need the that month's total, and this only gives me last month's total if
today is the 1st day of the month, I it is let's say the 15th of August, I
should see from the 1st to the 14th inclusively, not the month of july. I
know i need an Iff statement, but I'm not sure how to phrase it.

"CR" wrote:

I have a built queries to give me monthly totals, but the problem is that by
using Month(Now()), I get the current month when it is the 1st day of the
month instead of last month's data. I imagine that I will have the same
problem with the Ytd data. I got around the Daily data problem by using the
Date()-1 function, but I can't seem to find how to use it for month and year.
Any suggestions would be appreciated.

Thank you
Eric

Try these:

For the last day of the month:

=IIF(Day(Date())=1, Day(Date())-1, Day(Date()))


For last month on the 1st of the new month:

=IIF(Day(Date())=1, Month(Date())-1, Month())

For last year on Jan 1st:

=IIF(Month(Date())=1 and Day(Date())=1, Year(Date())-1, Year(Date()))

HTH
Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)



"CR" wrote:

That works great thank you, however, if it isn't the 1st day of the month, I
need the that month's total, and this only gives me last month's total if
today is the 1st day of the month, I it is let's say the 15th of August, I
should see from the 1st to the 14th inclusively, not the month of july. I
know i need an Iff statement, but I'm not sure how to phrase it.

"CR" wrote:

I have a built queries to give me monthly totals, but the problem is that by
using Month(Now()), I get the current month when it is the 1st day of the
month instead of last month's data. I imagine that I will have the same
problem with the Ytd data. I got around the Daily data problem by using the
Date()-1 function, but I can't seem to find how to use it for month and year.
Any suggestions would be appreciated.

Thank you
Eric

Excellent, tyvm guys, I was close, but wasn't putting the (Date) functions in

Thx

"SteveS" wrote:

Try these:

For the last day of the month:

=IIF(Day(Date())=1, Day(Date())-1, Day(Date()))


For last month on the 1st of the new month:

=IIF(Day(Date())=1, Month(Date())-1, Month())

For last year on Jan 1st:

=IIF(Month(Date())=1 and Day(Date())=1, Year(Date())-1, Year(Date()))

HTH
Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)



"CR" wrote:

That works great thank you, however, if it isn't the 1st day of the month, I
need the that month's total, and this only gives me last month's total if
today is the 1st day of the month, I it is let's say the 15th of August, I
should see from the 1st to the 14th inclusively, not the month of july. I
know i need an Iff statement, but I'm not sure how to phrase it.

"CR" wrote:

I have a built queries to give me monthly totals, but the problem is that by
using Month(Now()), I get the current month when it is the 1st day of the
month instead of last month's data. I imagine that I will have the same
problem with the Ytd data. I got around the Daily data problem by using the
Date()-1 function, but I can't seem to find how to use it for month and year.
Any suggestions would be appreciated.

Thank you
Eric