Need to see everything in field except the last two characters

[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!

 

[KARL DEWEY]

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

 

[John Spencer]

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]

Has anyone told you today that you're the best? If not, you're the best!
Thanks for answering so quickly!

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

--
KARL DEWEY
Build a little - Test a little

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!

 

[Lmanns]

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!

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

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!