Prev: Not In (SELECT DISTINCTROW
Next: How do I display the most recent of three dates in an Access quer.
From: Joe on 25 Mar 2010 15:31
I have a table that has a field which contains numeric and or alphanumeric
values. I am wanting to develop a query from which the field "TAG" can be
searched and for any records that has a letter A,B,C,D etc at the end, that
letter is placed in another field.
Example
TAG Field1 Field2
0206 0206
0206A 0206 A
0206D 0206 D
206A 206 A
026 026
From: Jerry Whittle on 25 Mar 2010 16:00
Field1: IIF(IsNumeric(Right([TAG],1)), Null, Right([TAG],1))

If you don't need the leading 0s, then it's easy to get the number:

Field2: Val([TAG])
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"Joe" wrote:

> I have a table that has a field which contains numeric and or alphanumeric
> values. I am wanting to develop a query from which the field "TAG" can be
> searched and for any records that has a letter A,B,C,D etc at the end, that
> letter is placed in another field.
> Example
> TAG Field1 Field2
> 0206 0206
> 0206A 0206 A
> 0206D 0206 D
> 206A 206 A
> 026 026
From: John Spencer on 26 Mar 2010 08:44
If you want to do this in a query I believe you can use the following expressions.

Assumption: TAG has at most one letter at the end.

Field1: IIF([TAG] like "*[a-z]",Left([TAG],Len([TAG]-1)),[TAG])

Field2: IIF([TAG] like "*[a-z]",Right([TAG],1),Null)


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Joe wrote:
> I have a table that has a field which contains numeric and or alphanumeric
> values. I am wanting to develop a query from which the field "TAG" can be
> searched and for any records that has a letter A,B,C,D etc at the end, that
> letter is placed in another field.
> Example
> TAG Field1 Field2
> 0206 0206
> 0206A 0206 A
> 0206D 0206 D
> 206A 206 A
> 026 026
From: Joe on 26 Mar 2010 13:13
I may be doing something wrong but when I enter the code from the IIf
statement on into the criteria section I get a type mismatch error. I placed
the code exactly as stated in the Field section and still get type mismatch
error.
Is there something I am missing being a nubee?

"John Spencer" wrote:

> If you want to do this in a query I believe you can use the following expressions.
>
> Assumption: TAG has at most one letter at the end.
>
> Field1: IIF([TAG] like "*[a-z]",Left([TAG],Len([TAG]-1)),[TAG])
>
> Field2: IIF([TAG] like "*[a-z]",Right([TAG],1),Null)
>
>
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
>
> Joe wrote:
> > I have a table that has a field which contains numeric and or alphanumeric
> > values. I am wanting to develop a query from which the field "TAG" can be
> > searched and for any records that has a letter A,B,C,D etc at the end, that
> > letter is placed in another field.
> > Example
> > TAG Field1 Field2
> > 0206 0206
> > 0206A 0206 A
> > 0206D 0206 D
> > 206A 206 A
> > 026 026
> .
>
From: Joe on 26 Mar 2010 13:30
Hi Jerry
Thank you, I do need the leading zero, and it could be including the zero
either 3 or 4 digits in length, Your code works great for Field 1 but Field2
returns the value and if the frecord begins with a zero 0 appears.

Thak you again for your help.

"Jerry Whittle" wrote:

> Field1: IIF(IsNumeric(Right([TAG],1)), Null, Right([TAG],1))
>
> If you don't need the leading 0s, then it's easy to get the number:
>
> Field2: Val([TAG])
> --
> Jerry Whittle, Microsoft Access MVP
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>
> "Joe" wrote:
>
> > I have a table that has a field which contains numeric and or alphanumeric
> > values. I am wanting to develop a query from which the field "TAG" can be
> > searched and for any records that has a letter A,B,C,D etc at the end, that
> > letter is placed in another field.
> > Example
> > TAG Field1 Field2
> > 0206 0206
> > 0206A 0206 A
> > 0206D 0206 D
> > 206A 206 A
> > 026 026
 |  Next  |  Last
Pages: 1 2
Prev: Not In (SELECT DISTINCTROW
Next: How do I display the most recent of three dates in an Access quer.