Function and Nested loop

[orchid67]

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 another way?

SampleTable

Name     Parts   Categories  New Field
(Field1) (Field2)(Field3)    (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

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

Query calls this function
Status: MachineCheck ([Field2],[Field3])

Thanks for your help as always!

[NeoPa]

You cannot compare a value to Null. Null is not a value as such - it is the state where no defined value exists. Thus, X = Null can never make sense.

To use Select Case to include checking for a variable or field being Null you can use :

Select Case True
Case IsNull(X)
    ...
Case X = "Blah"
    ...
Case X = "Blah blah"
    ...
End Select

[NeoPa]

I should add that working on your code in a word processor, as opposed to a text editor, is a very bad idea. When posted properly in the [CODE] tags you can easily see why.

[orchid67]

Thanks so much for your kindness. I will try to use word processor next time. I am still learning. Thanks for being patient.

[zmbd]

orchid67 actually, Neopa was suggesting something like Notepad instead of MS-Word or WordPad. :)
The former is a pure ASCII editor whereas the latter two use richtext or other formatting that doesn't pass thru to the post.
For Code, I tend to just use the VBA-Editor, the formatting holds properly when placing the [Code] tags. :)

[NeoPa]

Orchid:
I will try to use word processor next time. I am still learning.

I hope that ZMBD's post has clarified that a little.

In case it's still not clear though, it's the word processors (Like Word and WordPad) that give you the problems. Use a text editor instead for reliable results. Examples of text editors are :
Notepad - Comes with Windows.
Notepad++ - Available for free.
TextPad - Also free. I use this and it does a great job.

I've heard that Notepad++ does a fine job too, but I can't say that from my own experience.