Need to see everything in field except the last two characters

  • Thread starter Thread starter Lmanns
  • Start date Start date
L

Lmanns

Hello, I have a field with varying lengths of data ranging from 3 to 12
characters. I have a formula in my query to return the right two characters
as these characters desginate a specific location. The other characters
represent an account number. I need to break the account number out into its
own field as well.

For example:
98098012 is location 12, account number 980980
9088 is location 88, account number 90

Is there a way to break out the account number into a separate field
automatically with a query?

Thanks in advance for your help!
 
The expression is

Left([AccountNumber],Len([AccountNumber])-2)

That will error if Account number is not at least 2 characters long,
although it will handle nulls correctly

For the truly paranoid
IIF(Len([AccountNumber] & "")<2,Null,
Left([AccountNumber],Len([AccountNumber])-2))

Of course, you can replace Null with
[AccountNumber] to return the original value
"N/A" to return N/A when the AccountNumber is Null or less than 2
characters long
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Has anyone told you today that you're the best? If not, you're the best!
Thanks for answering so quickly!

KARL DEWEY said:
Account: Left([YourField], Len([YourField]) -2)

--
KARL DEWEY
Build a little - Test a little


Lmanns said:
Hello, I have a field with varying lengths of data ranging from 3 to 12
characters. I have a formula in my query to return the right two characters
as these characters desginate a specific location. The other characters
represent an account number. I need to break the account number out into its
own field as well.

For example:
98098012 is location 12, account number 980980
9088 is location 88, account number 90

Is there a way to break out the account number into a separate field
automatically with a query?

Thanks in advance for your help!
Click to expand...
Click to expand...
 
Great information! The first solution will work for me because the account
number is a min of 2 characters. Your solution will help to catch other
issues with the account numbers such as when it doesn't exist at all. Thanks
so much!

John Spencer said:
The expression is

Left([AccountNumber],Len([AccountNumber])-2)

That will error if Account number is not at least 2 characters long,
although it will handle nulls correctly

For the truly paranoid
IIF(Len([AccountNumber] & "")<2,Null,
Left([AccountNumber],Len([AccountNumber])-2))

Of course, you can replace Null with
[AccountNumber] to return the original value
"N/A" to return N/A when the AccountNumber is Null or less than 2
characters long
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Lmanns said:
Hello, I have a field with varying lengths of data ranging from 3 to 12
characters. I have a formula in my query to return the right two
characters
as these characters desginate a specific location. The other characters
represent an account number. I need to break the account number out into
its
own field as well.

For example:
98098012 is location 12, account number 980980
9088 is location 88, account number 90

Is there a way to break out the account number into a separate field
automatically with a query?

Thanks in advance for your help!
Click to expand...
Click to expand...
 
Back
Top