Select statement (Access 2002)

Topic

New Reply

I have a problem with a Select Case situation.
As the report opens there is a parameter box that opens asking for the Month of the year. That information is stored in the Month textbox.
At the bottom of the report, in the footer, I have a textbox called TxtTotalUOS.
I need the following computation [LengthofService Grand Total Sum] / ((DLookup(“AnnualUOSTotal”, “tblCompanyFacts”) / 12) * X) X will be a number between 1 to 12.
The results need to be stored in the txtTotalUOS unbound textbox.
I put the following in the forms On Open event.

Private Sub Report_Open(Cancel As Integer)
‘ Figure TotalUOS/Expected
Dim strTotalUOS As Integer
On Error Resume Next
strTotalUOS = Month
Select Case Month
Case “July”
txtTotalUOS = ([LengthofService Grand Total Sum] / ((DLookup(“AnnualUOSTotal”, “tblCompanyFacts”) / 12) * 1))
Case “August”
txtTotalUOS = ([LengthofService Grand Total Sum] / ((DLookup(“AnnualUOSTotal”, “tblCompanyFacts”) / 12) * 2))
Case “September”
txtTotalUOS = ([LengthofService Grand Total Sum] / ((DLookup(“AnnualUOSTotal”, “tblCompanyFacts”) / 12) * 3))
Case “October”
txtTotalUOS = ([LengthofService Grand Total Sum] / ((DLookup(“AnnualUOSTotal”, “tblCompanyFacts”) / 12) * 4))
End Select
End Sub

Thank you! Fay

Reply | Quote

Replies

I admit to NOT being an expert, by any stretch, when it comes to Reports but I think you want to put your Select Case code in the On Print event of the footer. And be sure that you have the ‘Month’ in a field on the Report so the code can read it…

hth,
Jack

Reply | Quote

At the Open event of the report, you’re asking for input, but you should be storing that in a variable. Then use the variable in your select case You didn’t explain why you were using a Month textbox in the first place, and what are you planning to do if they enter something like the month number or an abbreviation?

Reply | Quote

I am really out of my element here. I think I Dim the Month text entry correctly. Originally the Month textbox way placed on the report so that the Month would be placed at the top of the report to identify what month the data was summarizing. I guess you are asking if I will be doing an error message box if a number or abbreviation is entered. Yes. But one problem (new skill) at a time please.

Private Sub Report_Open(Cancel As Integer)
‘ Figure TotalUOS/Expected
Dim Month As String
Dim strTotalUOS As Integer
On Error Resume Next
strTotalUOS = Month
Select Case Month
Case “July”
txtTotalUOS = ([LengthofService Grand Total Sum] / ((DLookup(“AnnualUOSTotal”, “tblCompanyFacts”) / 12) * 1))
Case “August”
txtTotalUOS = ([LengthofService Grand Total Sum] / ((DLookup(“AnnualUOSTotal”, “tblCompanyFacts”) / 12) * 2))
Case “September”
txtTotalUOS = ([LengthofService Grand Total Sum] / ((DLookup(“AnnualUOSTotal”, “tblCompanyFacts”) / 12) * 3))
Case “October”
txtTotalUOS = ([LengthofService Grand Total Sum] / ((DLookup(“AnnualUOSTotal”, “tblCompanyFacts”) / 12) * 4))
End Select
End Sub

Should this be done on the Open event or would it be better to create a Public Sub?

Thank you. Fay

Reply | Quote

I won’t make an issue of naming conventions here, but I would strongly recommend you read up on them and start using them because you’ll save yourself and everyone else who tries to read your code a lot of headaches.

You’re declaring the Month variable as a string and your declaring strTotalUOS as an integer (OK, which did you want–a string as the name suggests or an integer as the datatype insists?) and then you’re setting strTotalUOS = Month. That makes no sense at all because you’re trying to set the value of an integer variable to an empty string.

*Then* you’re trying to do a select case on Month. As far as I can see, you’ve never populated Month in this routine, so either one or more of those “Months” is something other than a string variable or you shifted gears in mid code without going back and changing earlier bits of it. You absolutely cannot use the same name for controls and variables and fields and what not without confusing yourself and Access. If you have a textbox named Month (a bad idea anyhow, since that is also the name of a built in function), rename it to txtMonth both on your report and in your code. Rename your string variable strMonth. Now, what have you got left over?

Reply | Quote