Format Date on a Command Button

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using the VBA code below on my frmSwitchboard to set the caption on a
cmdButton. It works perfect, except, I cannot format the date to display only
the Month and Year. I've tried "format(txtMaxMonth, mmmm yyyy" with and
without brackets, quotations etc. I've tried it in different places in the
code. (txtMaxMonth is a calculated control on the frmSwitchboard and is
formatted to show the Month and Year)

Private Sub Form_Open(Cancel As Integer)
Dim stNextDate As String
stNextDate = DateAdd("m", 1, txtMaxMonth)
Me.CmdNextMonthReports.Caption = "Click to Begin Working on Reports for " &
stNextDate
End Sub

Thanks in advance Doug
 
I am using the VBA code below on my frmSwitchboard to set the caption on a
cmdButton. It works perfect, except, I cannot format the date to display only
the Month and Year. I've tried "format(txtMaxMonth, mmmm yyyy" with and
without brackets, quotations etc. I've tried it in different places in the
code. (txtMaxMonth is a calculated control on the frmSwitchboard and is
formatted to show the Month and Year)

Private Sub Form_Open(Cancel As Integer)
Dim stNextDate As String
stNextDate = DateAdd("m", 1, txtMaxMonth)
Me.CmdNextMonthReports.Caption = "Click to Begin Working on Reports for " &
stNextDate
End Sub

Thanks in advance Doug
Click to expand...


What is txtNextMonth?
Is it a field in the form's recordsource? A valid Date datatype?

Me!CmdNexMonthReports.Caption =
Format(DateAdd("m",1,[txtNextMonth]),"mmmm yyyy")
 
Private Sub Form_Open(Cancel As Integer)

Dim stNextDate As String

stNextDate = DateAdd("m", 1, txtMaxMonth)
Me.CmdNextMonthReports.Caption = _
"Click to Begin Working on Reportsfor " & stNextDate

End Sub
Click to expand...


You have, I'm afraid, a couple of missing steps:

First you have validate and convert txtMaxMonth to a valid date. Now, if
this is a text box and it contains something helpful like 01/02/03 then
it's easy:

stNextDate = DateAdd("m", 2, CDate(txtMaxMonth))

I'm a bit worried about this Month business, though. If the text box
looks like "January, 2005" then you may have a bigger job converting this
into a real date. You may be better off with a combo box that will force
the user into providing a legal value. Try a rowsource like: 1;Jan
2005;2;Feb 2005;3;Mar 2005...13;Jan 2006;... etc etc. and then hide hide
the first column. It's a tiny bit of algebra to build a text string from
an integer value.

Hope that helps


Tim F
 
Dheinze57 said:
I am using the VBA code below on my frmSwitchboard to set the caption on a
cmdButton. It works perfect, except, I cannot format the date to display only
the Month and Year. I've tried "format(txtMaxMonth, mmmm yyyy" with and
without brackets, quotations etc. I've tried it in different places in the
code. (txtMaxMonth is a calculated control on the frmSwitchboard and is
formatted to show the Month and Year)

Private Sub Form_Open(Cancel As Integer)
Dim stNextDate As String
stNextDate = DateAdd("m", 1, txtMaxMonth)
Me.CmdNextMonthReports.Caption = "Click to Begin Working on Reports for " &
stNextDate
End Sub
Click to expand...


This should work:

Dim stNextDate As Date '<<<<<<<<<<<<<<<
stNextDate = DateAdd("m", 1, txtMaxMonth)
Me.CmdNextMonthReports.Caption = "Click to Begin Working on
Reports for " & Format(stNextDate, "mmmm yyyy")
 
Thanks! That worked Perfect! I had tried the "mmmm" "yyyy" etc. but nothing
as a single string.

Doug
 
Back
Top