Drop 1st field value if its a number 1-4

[Schon731]

I have a question. I have a field being pulled and some of the values begin with a number. I would like to display this value but if the value begins with a 1,2,3 or 4 then I want it to DROP that leading number. This is a text field.



ex I have 1P in the field but I want to display as P

if the field is 6HM then I want to display 6HM

Thanks in advance.

[June7]

Try:

IIf(Left([fieldname],1)<5, Mid([fieldname],2), [fieldname])

[Dal Jeanis]

Here's what I would do - I'd have the real value being tied to a text field that I would make invisible.

Then I would have a visible field whose value comes from the hidden field but drops the leading 1-4 if present. Something like this -

=IIF(Left(txtHidden,1)<"1" OR Left(txtHidden,1)>"4", txtHidden, Right(txtHidden,Len(txtHidden)-1))

That code seems a little clunky, but should work.

In a query, you'd replace txtHidden with the actual name of the source field, and name the resulting expression with a fieldname something like "AS myMaskedFieldName".

Looks like June7 beat me to the enter key.

[Dal Jeanis]

June7 - Is "< 5" going to work in all implementations? I hate having to remember whether "A" or "a" is less than or greater than "1".

[June7]

Numbers always sort before letters (ascending order).

[Dal Jeanis]

Oh, the joys of being a PC person. In EBCDIC (the IBM mainframe collating sequence) "1" is x'F1', "A" is x'C1' and "a" is x'81'.

Straddling PC and mainframe worlds, I can't count on numbers being before letters. I have to look it up every time, so I always code it so I never have to know.

By the way, which is less, "5" or 5?

[June7]

Test expression in the VBA immediate window and they are equal, also "15" is greater than 2 but not greater than "2".

Numbers in a text field will sort alphabetically - "15" will sort before "2" as well as "a".

So Schon, you are okay with my expression as long as the number prefix remains one digit.

[Dal Jeanis]

Good to know.

[Schon731]

Test expression in the VBA immediate window and they are equal, also "15" is greater than 2 but not greater than "2".

Numbers in a text field will sort alphabetically - "15" will sort before "2" as well as "a".

So Schon, you are okay with my expression as long as the number prefix remains one digit.

I will try this monday. Even if the 2nd field is a number it can display as such. In other words if its 24h i want to display 4h so I think your code will work. Thanks.

[Schon731]

This doesnt exactly work 100%. I tried both ways shown above. It only works when the first character is a number but I have some instances where the first character is a letter like AIC. In those cases I want to see AIC but it just says #Error in those fields. Sorry it took so long to try it out. Thanks.

[June7]

Consistency of structure is critical for string manipulation.

#Error may be due to 'Type Mismatch'. Odd, my testing indicates the expression should work.

Try enclosing the 5 in quote marks

IIf(Left([fieldname],1)<"5", Mid([fieldname],2), [fieldname])