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.
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)
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
StatusType = x