How do I show all characters before the letter A in a Query?

[Joe]

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

 

[Jerry Whittle]

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])

 

[John Spencer]

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]

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?

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

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

.

 

[Joe]

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.

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.

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

 

[John Spencer]

You should not be entering anything into the criteria area based on what I've
said. I proposed adding two calculated fields - you would add the expressions
in a blank field "Cell".

Reviewing my proposed solution, I see that I have a misplaced closing
parenthesis in the first expression.

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

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

Hope this helps and works for you.

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

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?

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

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

.

 

[John W. Vinson]

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?

Wrong place: this isn't a criterion!

Put John's two expressions into vacant Field cells in the query grid instead.