Prev: Not In (SELECT DISTINCTROW
Next: How do I display the most recent of three dates in an Access quer.
From: John Spencer on 26 Mar 2010 15:10
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

Joe wrote:
> 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: John W. Vinson on 26 Mar 2010 15:35
On Fri, 26 Mar 2010 10:13:02 -0700, Joe <Joe(a)discussions.microsoft.com> wrote:

>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.
--

John W. Vinson [MVP]
First  |  Prev  | 
Pages: 1 2
Prev: Not In (SELECT DISTINCTROW
Next: How do I display the most recent of three dates in an Access quer.