• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 445
  • Last Modified:

Arrays - help with passing 2 variables

I need to create an array that will pass 2 values and look both theses values to determine what value is to be returned and being called with  a Append query.

I need to determine the status type and this will depend on status value can equal:
A = Active
H = Historical
L = Leave of Absence

This status type comes from 1 external source where as the Status type in the internal database means something else so I need to be able to convert it the local version.

Local version Status types equal a numeric value (counter field).  Note the other value that comes into play is the Sponsor field (external database) whether it is null or not, hence the need to pass 2 values within the array.

Here is what I have so far but I was hoping not to use a recordset to check the values.

Note BEMSID = Employee ID
Table Name = "tblNewHirePossible_temp"

I need to look thru evey BEMSID to determine the Status code and Sponsor value.

Thanks,
Karen

Function StatusType(n As Integer)
Dim curDB As Database
Dim strSql As String
Dim rs As Recordset
Dim n, x As Integer

Set curDB = CurrentDb()

strSql = "Select bemsid, status, sponsor from tblNewHirePossible_temp Where AddIn = -1"
Set rs = curDB.OpenRecordset(strSql)
    rs.MoveFirst
    Do Until rs.EOF
        If rs.Fields("status") = "A" And IsNull(rs.Fields("sponsor")) Then
            x = 1
        ElseIf rs.Fields("status") = "A" And Not IsNull(rs.Fields("sponsor")) Then
            x = 4
        ElseIf rs.Fields("status") = "L" And IsNull(rs.Fields("sponsor")) Then
            x = 5
        End If
        StatusType = x
    rs.MoveNext
    Loop
End Function

Open in new window

0
Karen Schaefer
Asked:
Karen Schaefer
  • 7
  • 2
  • 2
1 Solution
 
HainKurtSr. System AnalystCommented:
i could not get why do you loop all records and changing status for each record without consuming it? whats the purpose of n in code? how do you use it?
0
 
HainKurtSr. System AnalystCommented:
also n is parameter passed to function, and you also define it in function as new variable!!!...

Just your function does not make any sense to me...
0
 
Karen SchaeferAuthor Commented:
Not sure why the loop - I was just trying other approaches.  How would you handle passing the Status type and the Sponsor  and convert the Status type from the Alpha value to the new numeric counter value.

looking for suggestions.

K
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
NorieCommented:
Where does the array come into it?

You don't need an array to pass 2 values to a function.

As for converting StatusType to a numeric value, as far as I can see in your code it's only ever given a numeric value.
0
 
Karen SchaeferAuthor Commented:
thanks for your input, however, I got it to work - here is my results:

Function StatusType(n As String, sp As String)
Dim curDB As Database
Dim strSql As String
Dim rs As Recordset
Dim x As Integer

Set curDB = CurrentDb()

        If n = "A" And IsNull(sp) Then
            x = 1
        ElseIf n = "A" And Not IsNull(sp) Then
            x = 4
        ElseIf n = "H" And IsNull(sp) Then
            x = 1
        ElseIf n = "H" And Not IsNull(sp) Then
            x = 1
        ElseIf n = "L" And IsNull(sp) Then
            x = 5
        End If
        StatusType = x
End Function
 being called from within a query.

Open in new window

0
 
Karen SchaeferAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for kfschaefer1's comment #a38347542

for the following reason:

figured it on my own.
0
 
NorieCommented:
So you didn't use an array.:)
0
 
Karen SchaeferAuthor Commented:
Still not working correctly - It is not looping thru each record in the query:

I need to be able to loop thru each record and determine what the new status value is>

sampledata
It seems to be returning a #Error when the sponsor is null

what do I need to do to handle the null values?

Query statement calling the functions:   StatusType([status],[sponsor])

Please help.

K
0
 
Karen SchaeferAuthor Commented:
Latest attempt without success:
Function StatusType(n As String, sp As String)
Dim curDB As Database
Dim strSql As String
Dim rs As Recordset
Dim x As Integer

Debug.Print n
Debug.Print sp
Set curDB = CurrentDb()

    If IsNull(sp) = True Then
        If n = "A" Or n = "H" Then
            x = 1
        ElseIf n = "L" Then
            x = 5
        End If
    Else
        If n = "A" Or n = "H" Then
            x = 4
        End If
    End If
        StatusType = x
End Function

Open in new window

0
 
Karen SchaeferAuthor Commented:
Ok, sorry for the confusion - however, I changed approaches and which field I am using to return the correct results and this is acceptable to the user.

Here is my results to avoid the null issue.
Function StatusType(n As String, sp As String)
Dim curDB As Database
Dim strSql As String
Dim rs As Recordset
Dim x As Integer

Set curDB = CurrentDb()

    If sp <> "Contract labor" Then
        If n = "A" Or n = "H" Then
            x = 1
        ElseIf n = "L" Then
            x = 5
        End If
    Else
        If n = "A" Or n = "H" Then
            x = 4
        End If
    End If
        StatusType = x
End Function

Open in new window

0
 
Karen SchaeferAuthor Commented:
to avoid nulls I decided to use different fields where null would not be an issue.

Thanks
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 7
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now