Case statement - Case is null

[kpal29]

I need to determine if a value of a field is null in a Case statement - I can't figure out the correct syntax
Here is my code:

Select Case rs!reference_no
Case "FILES REMAINING OPEN FROM THE PERIOD"
rs.Delete

Case Is = Null
rs.Delete

End Select

Please advise
Thanks

 

[cLFlaVA]

If you mean empty string, use
Case ""

If you mean null, use
Case Null

*cLFlaVA
----------------------------
Breaking the habit...

 

[SkipVought]

Hi,

Realize that in VB, "" and NULL mean TWO DIFFERENT THINGS.

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[kpal29]

Have tried both w/o success - here is my whole code:

Sub DeleteGarbage()
Dim DB As dao.Database
Dim rs As dao.REcordset
Set DB = DBEngine.Workspaces(0).Databases(0)
Set rs = DB.OpenRecordset("Import")
Do While Not rs.EOF

Select Case rs![REFERENCE_NO]
Case "FILES REMAINING OPEN FROM THE PERIOD", "TOTAL FOR FILES REMAINING OPEN FROM THE PERIOD", _
"CLOSED FILES", "PRIOR PERIOD POST-CLOSING EXCEPTIONS OCCURING DURING THE PERIOD", "Totals", "TOTAL CLOSED FILES"
rs.Delete

Case null rs.Delete

End Select

rs.MoveNext
Loop
rs.Close
End Sub

 

[cLFlaVA]

Are you getting an error message of any sort? Can you pop a messagebox to display the actual value in rs![REFERENCE_NO]?

You might also want to try trimming first:

Select Case Trim(rs![REFERENCE_NO])

*cLFlaVA
----------------------------
Breaking the habit...

 

[SkipVought]

    Select Case rs![REFERENCE_NO]
    Case Null, "", "FILES REMAINING OPEN FROM THE PERIOD", "TOTAL FOR FILES REMAINING OPEN FROM THE PERIOD", _
    "CLOSED FILES", "PRIOR PERIOD POST-CLOSING EXCEPTIONS OCCURING DURING THE PERIOD", "Totals", "TOTAL CLOSED FILES"
        rs.Delete

    End Select

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[kpal29]

I am not getting an error - I debug.printed in the immediate window all values where Reference_no = null and they showed up as null - Can't figure this one out?

 

[kpal29]

Also, this file is a .csv imported with a transfertext method. When I open the.csv in Excel and select an empty reference_no cell and press f2 - I can see there are 20 spaces in the cell - I tried trim and "" with 20 spaces in between also.

 

[SkipVought]

Did you try

Case  ""

???

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}