Include first 3 characters in results

[Ben]

Hi - I have fields "Major", "Minor", "Acct_nbr", and "Name" in table "ABC".
The Acct_nbr field is always 9 characters. I am running a query using the
LIKE criteria to find my results. Is there a way that I can also show the
first 3 characters in the Acct_nbr field in my results/query so I can use
that to join to another table? Essentially, the first 3 characters
identifies the type of account. I then want to add the name of the type of
account from my other table.

Thanks, Ben

 

[raskew via AccessMonster.com]

If in fact it's a number (versus a string)

x = 123456789
? int(x/10^6)
123

HTH - Bob

 

[Ben]

The field contains alpha-numeric characters. The first 3 characters can have
alpha characters where as the last 6 characters are only numeric.

Ben

 

[KARL DEWEY]

Account_Type: Left([Acct_nbr], 3)

 

[Ben]

I'm a Design View guy - not really good at SQL. How would I do that in
Design View?
Thanks - Ben

Account_Type: Left([Acct_nbr], 3)
--
KARL DEWEY
Build a little - Test a little

Hi - I have fields "Major", "Minor", "Acct_nbr", and "Name" in table "ABC".
The Acct_nbr field is always 9 characters. I am running a query using the
LIKE criteria to find my results. Is there a way that I can also show the
first 3 characters in the Acct_nbr field in my results/query so I can use
that to join to another table? Essentially, the first 3 characters
identifies the type of account. I then want to add the name of the type of
account from my other table.

Thanks, Ben

 

[John Spencer]

That is how you would include it in design view.

In a Field "box" type
Account_Type: Left([YourTableName].[YourFieldName],3)

That will return the first 3 characters of YourFieldName and will title
the column "Account_Type".

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

I'm a Design View guy - not really good at SQL. How would I do that in
Design View?
Thanks - Ben

Account_Type: Left([Acct_nbr], 3)
--
KARL DEWEY
Build a little - Test a little

Hi - I have fields "Major", "Minor", "Acct_nbr", and "Name" in table "ABC".
The Acct_nbr field is always 9 characters. I am running a query using the
LIKE criteria to find my results. Is there a way that I can also show the
first 3 characters in the Acct_nbr field in my results/query so I can use
that to join to another table? Essentially, the first 3 characters
identifies the type of account. I then want to add the name of the type of
account from my other table.

Thanks, Ben