Query Left Command Help

[mattc66 via AccessMonster.com]

Hi All,

I want to find the first 2 or 3 letter in the field. The data is the users
initial followed by a number. Sometimes they enter there 2 or 3 letter
initials. What can I do to find the first 2 or 3 letters and omit the numbers?


Thanks
Matt

 

[Jeff Boyce]

Matt

One approach might be to build a procedure that starts with the first
character, tests to see if it's numeric, then moves over one and re-tests
until the test fails because the nth character IS a number.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[John Spencer]

You could try the following UNTESTED expression.

Left([FieldName],IIF(IsNumeric(Mid([FieldName),3,1),2,3))

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[mattc66 via AccessMonster.com]

I get a message saying "wrong number of arguments" after the ,1)

You could try the following UNTESTED expression.

Left([FieldName],IIF(IsNumeric(Mid([FieldName]),3,1),2,3))

Hi All,

[quoted text clipped - 5 lines]

Thanks
Matt

 

[J_Goddard via AccessMonster.com]

I think it should be:

Left([FieldName],IIF(IsNumeric(Mid([FieldName),3,1)),2,3)

John

I get a message saying "wrong number of arguments" after the ,1)

You could try the following UNTESTED expression.

[quoted text clipped - 5 lines]

 

[mattc66 via AccessMonster.com]

Below the syntax. Thank you
Left([FieldName],IIf(IsNumeric(Mid([FieldName],3,1)),2,3))

I think it should be:

Left([FieldName],IIF(IsNumeric(Mid([FieldName),3,1)),2,3)

John

I get a message saying "wrong number of arguments" after the ,1)

[quoted text clipped - 3 lines]