Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Select statement (Access 2002)

    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

  2. #2
    Star Lounger
    Join Date
    Nov 2001
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select statement (Access 2002)

    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

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Select statement (Access 2002)

    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?
    Charlotte

  4. #4
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select statement (Access 2002)

    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

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Select statement (Access 2002)

    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?
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •