Display previous month 1

[kmclane]

I have a form with an unbound text box. I have this code in the On Format event of the header:

Me.Head = "Settlement Report - " & Format(Date, "mmmm") & " " & Format(Date, "yyyy")

This displays "Settlement Report - June 2004", which is great, however, I will be always be generating these reports for the previous month in the current month. I have tried many different ways, and cannot get it to display "Settlement Report - May 2004". I basically need this month -1, but cannot find how to get it. I would like this to be automatic, so I don't need to worry that a change didn't get made. Thanks, Ken.

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[glalsop]

Hi Mitch -

Try something like this:

If DatePart("m", Date) = 1 Then
 Me.Head "Settlement Report - " & MonthName(DatePart("m", Date)-1) & " " & Year(Date) - 1
Else
 Me.Head "Settlement Report - " & MonthName(DatePart("m", Date)-1) & " " & Year(Date)
End If

-Gary

 

[kmclane]

Gary, I tried your code but A97 doesn't know what MonthName is, I get a "Sub or Function not defined".
Ken(not Mitch, Mitch is the funny guy)

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[BobJacksonNCI]

Ken or Mitch, whoever you are right now ,
try this:

Me.Head = "Settlement Report - " & Format(DateSerial(Year(Date()), Month(Date())-1,1) , "mmmm yyyy")

HTH,
Bob

 

[glalsop]

Sorry Ken, didn't notice the quote

Since MonthName is not available in 97, try this:

If DatePart("m", Date) = 1 Then
    Me.Head = "Settlement Report - " & Format(DateSerial(Year(Date) - 1, 12, 1), "mmmm yyyy")
Else
    Me.Head = "Settlement Report - " & Format(DateSerial(Year(Date), Month(Date) - 1, 1), "mmmm yyyy")
End If

I have really never used Access 97, so I can't guarantee that the dateserial function is available either. Let me know.

As an alternative, you could write your own MonthName function, along the lines of:

Public Function GetMonthName(MonthNumber As Integer) As String

   Select Case MonthNumber
      Case 1: GetMonthName = "January"
      Case 2: GetMonthName = "February"
      ...
   End Select
End Function

Good luck

-Gary

 

[kmclane]

It worked, have a star! I swear I tried that code, but I probaly had a ) out of place and Access likes to highlight a whole line of code. Thanks. Mitch Hedburg is a comic, he did that joke and said that he would like to see it used as a quote, so I do.
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg