Converting to a Function

[gchichester]

I'm using this Select Case code in conjunction with a Option Group on a multi-tab form with about 40 run report buttons. The code works fine when included in the Click Event code of a button. I would like to convert it to a function for obvious reasons. I have tried a few variation of a function with no success. What is the best approach for converting a procedure to a function.

Private Sub WhatYear()
Dim strCriteria As String
Select Case Me.Seasons
Case 2009
    strCriteria = "[ExpDate] Between #09/01/2009# and #09/01/2010#"
Case 2010
    strCriteria = "[ExpDate] Between #09/01/2010# and #09/01/2011#"
Case 2011
    strCriteria = "[ExpDate] Between #09/01/2011# and #09/01/2012#"
End Select
 
   DoCmd.OpenReport "rptWeeklyStatus_Asia_Qry", acViewPreview, , strCriteria
 

Thanks for any and all suggestions
Gil

[ADezii]

1. Copy-N-Paste the following Code into the Declarations Section of the Form's Code Module. This must be the Form that contains the [Seasons] and [Expdate] Fields, and the Function should be declared Privately. Notice a couple of minor additions to the Code also.
   

   Private Function fWhatYear()
   Dim strCriteria As String
    
   If IsNull(Me.Seasons) Or Not IsNumeric(Me.Seasons) Then Exit Function
    
   Select Case Me.Seasons
     Case 2009
       strCriteria = "[ExpDate] Between #09/01/2009# and #09/01/2010#"
     Case 2010
       strCriteria = "[ExpDate] Between #09/01/2010# and #09/01/2011#"
     Case 2011
       strCriteria = "[ExpDate] Between #09/01/2011# and #09/01/2012#"
     Case Else
       MsgBox "No Report exists for the specified Year", vbExclamation, "Invalid Year"
         Exit Function
   End Select
    
   DoCmd.OpenReport "rptWeeklyStatus_Asia_Qry", acViewPreview, , strCriteria
   End Function

2. To Execute the Function:
   

   Call fWhatYear

[gchichester]

ADezii,
Thanks for the prompt response.
I added your changed code as a private function of the form code module. Selecting a different year still has no effect on the date range of the report.


[code]
Private Function fWhatYear()
Dim strCriteria As String

If IsNull(Me.Seasons) Then Exit Function

Select Case Me.Seasons
Case 2009
strCriteria = "[ExpDate] Between #09/01/2009# and #09/01/2010#"
Case 2010
strCriteria = "[ExpDate] Between #09/01/2010# and #09/01/2011#"
Case 2011
strCriteria = "[ExpDate] Between #09/01/2011# and #09/01/2012#"
Case Else
MsgBox "No Report exists for the specified Year", vbExclamation, "Invalid Date Range"
Exit Function
End Select
End Function
--------------------------------------------------------------------------------
Private Sub cmdAsiaBillOfLadingCount_Click()
On Error GoTo cmdAsiaBillOfLadingCount_Click_Err

fWhatYear

DoCmd.OpenReport "rptBillOfLadingCount_Asia_Tbl", acViewPreview, , strCriteria

PrintQuestion

cmdAsiaBillOfLadingCount_Click_Exit:
Exit Sub

cmdAsiaBillOfLadingCount_Click_Err:
MsgBox Error$
Resume cmdAsiaBillOfLadingCount_Click_Exit

End Sub

I saw where included the DoCmd.OpenReport "rptWeeklyStatus_Asia_Qry", acViewPreview, , strCriteria in the function, Was that your intension?
It's part of Click Event code, I included it as a reference to how srtCriteria is being used. Sorry for a confusion.
Gil

[ADezii]

I saw where included the DoCmd.OpenReport "rptWeeklyStatus_Asia_Qry", acViewPreview, , strCriteria in the function, Was that your intension?

My fault, thought that it may be part of the overall Code, thus the inclusion. Also forgot about the Option Group, that's what happens as you get older ! (LOL)

[gchichester]

No problem, I deal with the same problem.
So, Do you have any other suggestions on how to convert
this code to a function? Or should I just add it to each button?

Gil

[ADezii]

What code are you referring to, Gil?

[gchichester]

The "WhatYear" function code.
The changes you suggested did not work.

[ADezii]

Code has been tested prior to Posting and does work.

[gchichester]

Sorry but I'm confused again!
Did you test the code with or without the DoCmd.OpenReport & the Option Group?

[ADezii]

Move the Declaration for strCriteria from the Function to the Form's Code Module, General Declarations Section.

'Should be in the General Declarations Section of the involved Form
Dim strCriteria As String