Create a substring from field

[Chris Brotz]

I have a field that is text. Like:

"LF20" for example. I want to separate the 20 into a separate numeric field
if there is a function to do this...

Thank you,
Chris

 

[lizmcgurty]

Create a loop for the length of your text value. For each
character, check to see if it is IsNumeric. For as long
as you find a numeric, append that value to a string.
When the search fails, then place the gathered value into
a int variable.

Dim myInt as Integer
myInt = Int(GatheringString)

This should work.

 

[Marshall Barton]

I have a field that is text. Like:

"LF20" for example. I want to separate the 20 into a separate numeric field
if there is a function to do this...

Well, you could use the Mid function:

Me.numericfield = Mid(Me.textfield, 2)

if the numeric part always starts in the 3rd character
position.

If the numeric part can come after any number of alpha
characters, then

For Pos = 1 to Len(Me.textfield)
If Mid(Me.textfield, Pos,1) Like "[0-9]" Then Exit For
Next Pos
Me.numericfield = Mid(Me.textfield, Pos)

 

[Chris Brotz]

This function worked to split up the field, but it is still text. Do you
know how to change it to numeric?

InsAmt: Mid([InsCode],3)

Thanks for your help!
Chris

I have a field that is text. Like:

"LF20" for example. I want to separate the 20 into a separate numeric field
if there is a function to do this...

Well, you could use the Mid function:

Me.numericfield = Mid(Me.textfield, 2)

if the numeric part always starts in the 3rd character
position.

If the numeric part can come after any number of alpha
characters, then

For Pos = 1 to Len(Me.textfield)
If Mid(Me.textfield, Pos,1) Like "[0-9]" Then Exit For
Next Pos
Me.numericfield = Mid(Me.textfield, Pos)

 

[Duane Hookom]

InsAmt: Val(Mid([InsCode],3))



--
Duane Hookom
MS Access MVP

This function worked to split up the field, but it is still text. Do you
know how to change it to numeric?

InsAmt: Mid([InsCode],3)

Thanks for your help!
Chris

I have a field that is text. Like:

"LF20" for example. I want to separate the 20 into a separate numeric field
if there is a function to do this...

Well, you could use the Mid function:

Me.numericfield = Mid(Me.textfield, 2)

if the numeric part always starts in the 3rd character
position.

If the numeric part can come after any number of alpha
characters, then

For Pos = 1 to Len(Me.textfield)
If Mid(Me.textfield, Pos,1) Like "[0-9]" Then Exit For
Next Pos
Me.numericfield = Mid(Me.textfield, Pos)