Removing Alpha from field

  • Thread starter Thread starter DS
  • Start date Start date
D

DS

I need to remove any alpha from a field...such as
11a = 11
2 = 2
141b = 141
23c = 23
4a = 4

The alpha is always at the end..
Sometimes there is an alpha, sometimes not.

How can I do this simply, I've been looking around and the solutions
seem very complicated with looping and all. I don't need to loop it's
for only one field andone record at a time.
Thanks
DS
 
DS said:
I need to remove any alpha from a field...such as
11a = 11
2 = 2
141b = 141
23c = 23
4a = 4

The alpha is always at the end..
Sometimes there is an alpha, sometimes not.

How can I do this simply, I've been looking around and the solutions
seem very complicated with looping and all. I don't need to loop it's
for only one field andone record at a time.
Thanks
DS
Click to expand...
Solution Found!
Val(Me.TxtField)
This works great since the Alpha part is to the right of the number,
however; it doesn't work if the alpha part is to the left of the number.
Thanks
DS
 
DS said:
I need to remove any alpha from a field...such as
11a = 11
2 = 2
141b = 141
23c = 23
4a = 4

The alpha is always at the end..
Sometimes there is an alpha, sometimes not.

How can I do this simply, I've been looking around and the solutions
seem very complicated with looping and all. I don't need to loop it's
for only one field andone record at a time.
Click to expand...


Where is this field? In a table, displayed on a form, as a
user enters the field's value?

Here's one way that should work in a query:

IIf(Right(field,1) Like "#",field,Left(field,Len(field)-1)
 
DS said:
Solution Found!
Val(Me.TxtField)
This works great since the Alpha part is to the right of the number,
however; it doesn't work if the alpha part is to the left of the number.
Click to expand...


Be careful of Val, it is more generous about what
constitutes a number than your examples indicate is
desireable. Val is quite capable of converting a string to
a floating point number so if your field has something like
123D or 456E, Val will recognize that as an ill formed
floating point number in scientific notation and generate an
error.

There are many more combinations of characters that Val can
process (sucessfully or not) that are probably not of
concern to you now but might cause trouble in other
situations. For example, Val(687-345-1342) returns -1000
 
Marshall said:
DS wrote:





Where is this field? In a table, displayed on a form, as a
user enters the field's value?

Here's one way that should work in a query:

IIf(Right(field,1) Like "#",field,Left(field,Len(field)-1)
Click to expand...
Cool! That works as well!
Thanks
DS
 
Marshall said:
DS wrote:





Be careful of Val, it is more generous about what
constitutes a number than your examples indicate is
desireable. Val is quite capable of converting a string to
a floating point number so if your field has something like
123D or 456E, Val will recognize that as an ill formed
floating point number in scientific notation and generate an
error.

There are many more combinations of characters that Val can
process (sucessfully or not) that are probably not of
concern to you now but might cause trouble in other
situations. For example, Val(687-345-1342) returns -1000
Click to expand...
Thanks Marshall, but I hope I should be OK since I only have this
condition, 1A or 1AA. I will be cautious with other applications of Val
though. Once again Thank You.
DS
 
Back
Top