calculate a report text box based on vba code in a form module

[Kris Neuhalfen]

I am trying to calculate the text in a Report text box based on user input from a form. I think I am just having trouble with the scope of the variable. In the VB editor the form module is named Form_NAPT by month. By the way, this would be easy in Access 2003 because it has the MonthName function included, I can't find it in Access 2000. Here is the code:

Private Sub NameOfMonth()
Dim MonthNameText As String
MonthNameText = "NAPT Log " & myMonthName(Month(Forms![NAPT By Month]!BeginningDate)) & " " & Year(Forms![NAPT By Month]!BeginningDate)
MsgBox (MonthNameText) 'used to see if the variable is calculated correctly. It is.

End Sub

Private Function myMonthName(MonthNum As Integer) As String 'calculate the name of the month based on user input from a form
Select Case MonthNum
Case 1
myMonthName = "January"
Case 2
myMonthName = "February"
Case 3
myMonthName = "March"
Case 4
myMonthName = "April"
Case 5
myMonthName = "May"
Case 6
myMonthName = "June"
Case 7
myMonthName = "July"
Case 8
myMonthName = "August"
Case 9
myMonthName = "September"
Case 10
myMonthName = "October"
Case 11
myMonthName = "November"
Case 12
myMonthName = "December"
End Select
End Function

This code runs when I click the submit button to make a report. The title of the Report is supposed to be "NAPT Log Month Year" with the "Month" and "Year" being calculated using the above code. The report name is rptMonthlyNAPT and the text box name is NAPTLogTitle. How do I get the Report text box to equal the MonthNameText variable from the form code?

Thanks,
Kris Neuhalfen

 

[Duane Hookom]

You can use Format(DateSerial(2000,[MthNum],1), "mmmm")

--
Duane Hookom
MS Access MVP

I am trying to calculate the text in a Report text box based on user input

from a form. I think I am just having trouble with the scope of the
variable. In the VB editor the form module is named Form_NAPT by month. By
the way, this would be easy in Access 2003 because it has the MonthName
function included, I can't find it in Access 2000. Here is the code:

Private Sub NameOfMonth()
Dim MonthNameText As String
MonthNameText = "NAPT Log " & myMonthName(Month(Forms![NAPT By

Month]!BeginningDate)) & " " & Year(Forms![NAPT By Month]!BeginningDate)

MsgBox (MonthNameText) 'used to see if the variable is calculated correctly. It is.

End Sub

Private Function myMonthName(MonthNum As Integer) As String 'calculate

the name of the month based on user input from a form

Select Case MonthNum
Case 1
myMonthName = "January"
Case 2
myMonthName = "February"
Case 3
myMonthName = "March"
Case 4
myMonthName = "April"
Case 5
myMonthName = "May"
Case 6
myMonthName = "June"
Case 7
myMonthName = "July"
Case 8
myMonthName = "August"
Case 9
myMonthName = "September"
Case 10
myMonthName = "October"
Case 11
myMonthName = "November"
Case 12
myMonthName = "December"
End Select
End Function

This code runs when I click the submit button to make a report. The title

of the Report is supposed to be "NAPT Log Month Year" with the "Month" and
"Year" being calculated using the above code. The report name is
rptMonthlyNAPT and the text box name is NAPTLogTitle. How do I get the
Report text box to equal the MonthNameText variable from the form code?

 

[Randy Harris]

My access 2000 has a MonthName function.

I am trying to calculate the text in a Report text box based on user input

from a form. I think I am just having trouble with the scope of the
variable. In the VB editor the form module is named Form_NAPT by month. By
the way, this would be easy in Access 2003 because it has the MonthName
function included, I can't find it in Access 2000. Here is the code:

Private Sub NameOfMonth()
Dim MonthNameText As String
MonthNameText = "NAPT Log " & myMonthName(Month(Forms![NAPT By

Month]!BeginningDate)) & " " & Year(Forms![NAPT By Month]!BeginningDate)

MsgBox (MonthNameText) 'used to see if the variable is calculated correctly. It is.

End Sub

Private Function myMonthName(MonthNum As Integer) As String 'calculate

the name of the month based on user input from a form

Select Case MonthNum
Case 1
myMonthName = "January"
Case 2
myMonthName = "February"
Case 3
myMonthName = "March"
Case 4
myMonthName = "April"
Case 5
myMonthName = "May"
Case 6
myMonthName = "June"
Case 7
myMonthName = "July"
Case 8
myMonthName = "August"
Case 9
myMonthName = "September"
Case 10
myMonthName = "October"
Case 11
myMonthName = "November"
Case 12
myMonthName = "December"
End Select
End Function

This code runs when I click the submit button to make a report. The title

of the Report is supposed to be "NAPT Log Month Year" with the "Month" and
"Year" being calculated using the above code. The report name is
rptMonthlyNAPT and the text box name is NAPTLogTitle. How do I get the
Report text box to equal the MonthNameText variable from the form code?