function with 2 parameters and nested

Question

Table with 3000 records with 21 column fields

I need to loop thru the records using the 2 column fields (Field2 and Field3) where there are a null values to Produced a New column field (Field4) I started with this function below but I am stuck since case select cannot accept null- or I may need to use nested iifs which I am not good at.

SampleTable

Name(Field1)  Parts(Field2)   Categories(Field3)  New Field(Field4)
1              A1              R1                 Inventory
2              A1              Null               Inventory
3              B1              R1                 Processing
4              B1              R4                 Processing
5              B1              Null               Unknown 
6              Null            Null               Unknown

My function:

Function MachineCheck(Field2, Field3) As String

Dim newValue As String

Select Case (Field2)
     Case "A1":
        Select Case (Field3)
            Case "R1"
                newValue = "Inventory"
            Case Is null
                newValue = "Inventory"
            Case "R1"
                newValue = "Inventory"
            Case “ “
                newValue = "Inventory"

       Select Case (Field2)
         Case “B1”
End Select

MachineCheck = newValue
End Function

Thanks for your help!

You don't have to go through the collection of records, you have to write proper query! — Maciej Los, Jun 26 '16 at 18:09

score 0 · Answer 1 · edited May 23 '17 at 12:32

0

I'd suggest to write query this way:

SELECT Field1 AS [Name], Field2 AS Parts, Field3 AS Categories, 
      SWITCH( Trim(Field3) & Trim(Field4) = "A1", "Inventory",
              Trim(Field3) & Trim(Field4) = "A1R1", "Inventory", 
              Trim(Field3) & Trim(Field4) = "B1", "Unknown",
              Trim(Field3) & Trim(Field4) = "B1R1", "Processing",
              Trim(Field3) & Trim(Field4) = "", "Unknown") As [Status]
FROM YourTable;

FOr further information, please see: What is the equivalent of Select Case in Access SQL?

edited May 23 '17 at 12:32

Community

1
1

answered Jun 26 '16 at 18:53

Maciej Los

8,468
1
20
35

Thanks so much for your help- I tried using the query and this works however I have some nulls for "both fields" and this does work- I cannot see the Unknown – pinkrose Jun 26 '16 at 20:29
So, try to replace: `=""` with `Is NULL`. – Maciej Los Jun 27 '16 at 05:50

function with 2 parameters and nested

1 Answers1