Parsing Text Field In Access and using NZ

  • Thread starter Thread starter Keith Leslein
  • Start date Start date
K

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!
 
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!
 
Back
Top