Help with passing an int to a funct and returning a str for field name

[KSimm]

I've been trying to solve this problem all week, and will welcome any suggestions. I've searched the forums and MSDN library, and googled, but I must be missing *something*

I'm using Access 2000 and VBA

I would like to pass an integer variable (that corresponds to calendar months) to a function to return a string (with month name). I would like to use the string variable to refer to field names in a recordset.

I can call the FindMonthName function just as "FindMonthName(x)" when I'm not trying to return anything... but if I try to assign it to something I get a byRef argument type mis-match error.

I'm pasting in my code so far (I'm sure it's kind of spaghetti, this is my first time coding something that isn't a homework assignment! ):

Private Sub cmdBedDayBuckets_Click()

Dim dtmAdmitDate As Date
Dim dtmDischargeDate As Date
Dim intBedDayMonth As Integer
Dim intAdmitMonth As Integer
Dim intDischargeMonth As Integer
Dim strPatNum As String
Dim strRefNum As String
Dim strPlanType As String
Dim strAdmitClass As String
Dim arrLOS(1 To 12) As Integer
Dim strLOSmonth As String
Dim intMonthdiff As Integer
Dim intCounter As Integer

Dim varArray As Variant 'Variable array to hold recordset rows
Dim varLOSArray As Variant 'Variable array to hold LOS values

'declare DAO databases and recordsets
Dim BedDays As DAO.Database
Dim rstBedDays As DAO.Recordset
Dim rstLOSCY2004 As DAO.Recordset
Dim fldX As DAO.Field
strSQL = "tblTESTLOSBuckets"
strLOS = "tblLOSCY2004"
Set BedDays = CurrentDb()
'Open BedDays table recordset
Set rstBedDays = BedDays.OpenRecordset(strSQL, dbOpenSnapshot)
Set rstLOSCY2004 = BedDays.OpenRecordset(strLOS, dbOpenDynaset)
'Point to first record in recordset
rstBedDays.MoveFirst

Set varArray = rstBedDays.Clone
Set varLOSArray = rstLOSCY2004.Clone

'********************************************************************
'this section will need to loop while not EOF
Do While rstBedDays.EOF = False

    'assign field values to vb variables
    dtmAdmitDate = rstBedDays!AdmitDate
    dtmDischargeDate = rstBedDays!DischargeDate
    intAdmitMonth = rstBedDays!AdmitMonth
    intDischargeMonth = rstBedDays!DischargeMonth
    strPatNum = rstBedDays!PatNum
    strRefNum = rstBedDays!ReferralNum
    strPlanType = rstBedDays!PlanType
    strAdmitClass = rstBedDays!AdmitClass

    'compare Admission month and Discharge month to see if time
    'does not extend over multiple months
    If intAdmitMonth = intDischargeMonth Then
        'If admit month and dishcharge month are the same
        'LOS is equal to admit month
        x = intAdmitMonth
        arrLOS(x) = DateDiff("d", dtmAdmitDate, dtmDischargeDate)
        'try converting x (integer data type)to string data type
        strMonth = CStr(x)
       'refer to field name in rstLOSCY2004 with a variable that is directly related to x
        fldX = FindMonthName(strMonth)
        rstLOSCY2004!fldX.Value = arrLOS(x)
        'need to assign value in arrLOS to correct field in tblLOSCY2004
        'dont forget to insert strPatNum and strRefNum
    
        Else
            intMonthdiff = DateDiff("m", dtmAdmitDate, dtmDischargeDate)
            intCounter = 0
            Do Until intCounter = intMonthdiff
                x = intAdmitMonth + intCounter
                If x = intAdmitMonth Then
                    arrLOS(x) = DateDiff("d", dtmAdmitDate, FindLastDayInMonth(dtmAdmitDate, 0)) + 1
                    
                    Else
                    x = x - 12
                    arrLOS(x) = DateDiff("d", FindFirstDayInMonth(dtmAdmitDate, intCounter), FindLastDayInMonth(dtmAdmitDate, intCounter)) + 1
                    End If
                    intCounter = intCounter + 1
                    Loop
                    x = intDischargeMonth
                    arrLOS(x) = DateDiff("d", FindFirstDayInMonth(dtmDischargeDate, 0), dtmDischargeDate)
                    
    End If
    rstBedDays.MoveNext
Loop


'**********************************************************************

End Sub
        
'Find First Day of Month Function
Function FindFirstDayInMonth(dtmdate As Date, intMonthcount As Integer) As Date
    'Return the first day in the specified month
    FindFirstDayInMonth = DateSerial(Year(dtmdate), Month(dtmdate) + intMonthcount, 1)
End Function

'Find Last Day of Month Function
Function FindLastDayInMonth(dtmdate As Date, intMonthcount As Integer) As Date
    'Return the last day in the specified month
    FindLastDayInMonth = DateSerial(Year(dtmdate), Month(dtmdate) + intMonthcount + 1, 0)
End Function

'Find Month Name Function
Function FindMonthName(strMonth As String) As String
    Select Case (strMonth)
        Case 1
        FindMonthName = Jan
        Case 2
        FindMonthName = Feb
        Case 3
        FindMonthName = Mar
        Case 4
        FindMonthName = Apr
        Case 5
        FindMonthName = May
        Case 6
        FindMonthName = Jun
        Case 7
        FindMonthName = Jul
        Case 8
        FindMonthName = Aug
        Case 9
        FindMonthName = Sep
        Case 10
        FindMonthName = Oct
        Case 11
        FindMonthName = Nov
        Case 12
        FindMonthName = Dec
    End Select
End Function

Thanks in advance for the help...

 

[pbaldy]

Function FindMonthName(strMonth As String) As String

 

[KSimm]

pbaldy -

I have that in the function already (it's at the end of the code window)

as this:

'Find Month Name Function
Function FindMonthName(strMonth As String) As String
    Select Case (strMonth)
        Case 1
        FindMonthName = Jan
        Case 2
        FindMonthName = Feb
        Case 3
        FindMonthName = Mar
        Case 4
        FindMonthName = Apr
        Case 5
        FindMonthName = May
        Case 6
        FindMonthName = Jun
        Case 7
        FindMonthName = Jul
        Case 8
        FindMonthName = Aug
        Case 9
        FindMonthName = Sep
        Case 10
        FindMonthName = Oct
        Case 11
        FindMonthName = Nov
        Case 12
        FindMonthName = Dec
    End Select
End Function

Or am I missing a typo or something? I've been looking at this for so long I don't trust my own eyes anymore . . .

Thx.

 

[pbaldy]

I know you have it in there; that's the problem. You said "I would like to pass an integer variable", so you need to make it:

Function FindMonthName(strMonth As Integer) As String

and I'd probably change the variable name to match its type.

 

[KSimm]

Thank you!