Create a substring from field

  • Thread starter Thread starter Chris Brotz
  • Start date Start date
C

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
 
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.
 
Chris said:
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...
Click to expand...


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)
 
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
Marshall Barton said:
Chris said:
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...
Click to expand...


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)
Click to expand...
 
InsAmt: Val(Mid([InsCode],3))



--
Duane Hookom
MS Access MVP


Chris Brotz said:
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
Marshall Barton said:
Chris said:
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...
Click to expand...


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)
Click to expand...
Click to expand...
 
Back
Top