find a character in a string

  • Thread starter Thread starter Ned
  • Start date Start date
N

Ned

I am trying to match codes that users data enter in a
particular field against a separate list of codes. The
field is a text field, as it contains both numeric and
alpha characters. My problem is that the codes can be
further specified by add a decimal point and additional
characters, yet the separate list of codes is a summary
list that does not contain any decimals. For instance, a
user could enter "v76.12", yet in my separate list, all I
have is "v76". My question is, how can I match these
two? My expectation was to locate the decimal and use the
left function to extract only the first part of the code.
I noticed a FindFirst function, but Access says this
function is not defined. Help would be greatly
appreciated. Thank you!

Ned
 
Ned said:
I am trying to match codes that users data enter in a
particular field against a separate list of codes. The
field is a text field, as it contains both numeric and
alpha characters. My problem is that the codes can be
further specified by add a decimal point and additional
characters, yet the separate list of codes is a summary
list that does not contain any decimals. For instance, a
user could enter "v76.12", yet in my separate list, all I
have is "v76". My question is, how can I match these
two?
Click to expand...

Use the InStr function to locate a character in a string:

Left(code, InStr(code, ".") -1)

will work if the code field always has a dot and is never
Null.

A more general approach is to create your own function to
cover all the cases:

Public Function GetCode(strCode As Variant) As Variant
Dim lngPos As Long

If IsNull(strCode) Then Exit Function

lngPos = InStr(code, ".")
If lngPos > 0 Then
GetCode = Left(strCode, lngPos - 1)
Else
GetCode = strCode
End If
End Function
 
Back
Top