It's time to ask the experts for help. I have a table with EHT_Stage variable filled in and based on the value in this field it supposed to grab the initials from a different column in the table and display it in query.
For example
if EHT_Stage has value of 6 then go to column "EHT_Designed_By" and show designer initials stored in this field
if EHT_Stage has value of 0 then display "N/A"
I came up with function but what it does it loops trough record sets and shows only "N/A" and ignores the records when I am expecting to see initials. Below is output from my query, vba code and sql Qry.
Thanks for your help
EHT_By |
P_Iso_Dwg |
EHT_Stage |
N/A |
47-9001-01 |
4 |
|
47-9001-02 |
5 |
N/A |
47-9001-03 |
1 |
|
47-9001-04 |
6 |
Code:
Option Compare Database
Option Explicit
Public Function funcEhtBy(EHT_Stage As String)
Dim EHT_Re_Designed_By, EHT_Designed_By, EHT_Drafted_By, EHT_Extracted_By, EHT_Modeled_By, EHT_Checked_By, EHT_Back_Drafted_By, EHT_Back_Checked_By As Variant
Dim strEHT_By As String
Dim RsEHT_By As DAO.Recordset
'set default return value
funcEhtBy = "0"
'get initials from table
strEHT_By = "select EHT_Stage, EHT_Re_Designed_By, EHT_Designed_By, EHT_Drafted_By, EHT_Extracted_By, EHT_Modeled_By, EHT_Checked_By, EHT_Back_Drafted_By, " & _
"EHT_Back_Checked_By from tbl_Tracking"
Set RsEHT_By = CurrentDb.OpenRecordSet(strEHT_By)
With RsEHT_By
If Not .BOF And Not .EOF Then
.MoveLast
.MoveFirst
While (Not .EOF)
Select Case EHT_Stage
Case "0", "1", "2", "3", "4", "16", "17"
funcEhtBy = "N/A"
Case "5"
funcEhtBy = EHT_Re_Designed_By
Case "6"
funcEhtBy = EHT_Designed_By
Case "7"
funcEhtBy = EHT_Designed_By
Case "8"
funcEhtBy = EHT_Drafted_By
Case "9"
funcEhtBy = EHT_Extracted_By
Case "10"
funcEhtBy = EHT_Modeled_By
Case "11"
funcEhtBy = EHT_Drafted_By
Case "12"
funcEhtBy = EHT_Checked_By
Case "13"
funcEhtBy = EHT_Checked_By
Case "14"
funcEhtBy = EHT_Back_Drafted_By
Case "15"
funcEhtBy = EHT_Back_Checked_By
Case Else
funcEhtBy = ""
End Select
.MoveNext
Wend
End If
End With
RsEHT_By.Close
Set RsEHT_By = Nothing
End Function
sql query
Code:
SELECT funcEhtBy([EHT_Stage]) AS EHT_By, tbl_Tracking.P_Iso_Dwg, tbl_Tracking.EHT_StageFROM tbl_Tracking;