Parsing Text Field In Access and using NZ

[Keith Leslein]

Hello All,

I am parsing a text field from a table ( ex Data: A/ASEL)

I reviewed the MVPS.org/Access webpage to get a start.

Here is the function the parsed the field correctly:

Test:Right(Mid([FieldName],Instr([FieldName],"/")),Len([FieldName])-2)

This parses the field accurately, however I want to capture Null(or
perhaps it might be zero lenght strings?) values as well. Is the above
statement too complex to incorporate the NZ function as well?

Test:Nz(Right(Mid([FieldName],Instr([FieldName],"/")),Len([FieldName])-2),"No
Value")

When the field contains no entry's it populates a standard #Error in
the field. Could this be zero length strings as opposed to Null
Values? Any information would be greatly appreciated.

Thanks for any assistance!

 

[John Nurick]

Hi Keith,

It can be tricky to handle all the possible "wrong" data in an
expression. I usually just write a little custom VBA function. Something
like this should do your job:

Public Function PostSlash(V As Variant) As String
Dim Pos As Long
'Returns everything after the first /, or
'the empty string if there is no /

Pos = InStr(Nz(V, ""), "/")
If Pos > 0 Then
PostSlash = Mid(V, Pos + 1)
Else
PostSlash = ""
End If

End Function

Hello All,

I am parsing a text field from a table ( ex Data: A/ASEL)

I reviewed the MVPS.org/Access webpage to get a start.

Here is the function the parsed the field correctly:

Test:Right(Mid([FieldName],Instr([FieldName],"/")),Len([FieldName])-2)

This parses the field accurately, however I want to capture Null(or
perhaps it might be zero lenght strings?) values as well. Is the above
statement too complex to incorporate the NZ function as well?

Test:Nz(Right(Mid([FieldName],Instr([FieldName],"/")),Len([FieldName])-2),"No
Value")

When the field contains no entry's it populates a standard #Error in
the field. Could this be zero length strings as opposed to Null
Values? Any information would be greatly appreciated.

Thanks for any assistance!