Need Part of a field

  • Thread starter Thread starter LMB
  • Start date Start date
L

LMB

In my employee database, I have the field SS number. When determining seniority for employees who were hired on the same date, the last 4 digits of the SS number is used to break the tie. The person with the higher number has more seniority. When I made my first database, I had 2 fields in my table SSNumber and SSNumberLastFour. Is there a way in a query to extract the last four and use it for the tie breaker or do I need this separate field in the table? It sure was easy to do the way I had it set up before but since I am turning a new leaf I thought I would ask before I did it. <g>

Thanks,
Linda
 
If SS is text, you can use Right([SS], 4) to get the last 4 characters.

If it's numeric, you might want to use type conversion functions to have
better control, like
CLng(Right(CStr([SS]), 4)), or you can use [SS] Mod 10000



--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


In my employee database, I have the field SS number. When determining
seniority for employees who were hired on the same date, the last 4 digits
of the SS number is used to break the tie. The person with the higher
number has more seniority. When I made my first database, I had 2 fields in
my table SSNumber and SSNumberLastFour. Is there a way in a query to
extract the last four and use it for the tie breaker or do I need this
separate field in the table? It sure was easy to do the way I had it set up
before but since I am turning a new leaf I thought I would ask before I did
it. <g>

Thanks,
Linda
 
Linda,

Access has 3 functions you can use for various purposes; Left(), Right(),
Mid(). The one you need to use in this case is Right().

SELECT Right(myfield, 4) As LastFour
FROM tblMyTable

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------
In my employee database, I have the field SS number. When determining
seniority for employees who were hired on the same date, the last 4 digits
of the SS number is used to break the tie. The person with the higher
number has more seniority. When I made my first database, I had 2 fields in
my table SSNumber and SSNumberLastFour. Is there a way in a query to
extract the last four and use it for the tie breaker or do I need this
separate field in the table? It sure was easy to do the way I had it set up
before but since I am turning a new leaf I thought I would ask before I did
it. <g>

Thanks,
Linda
 
Hi Linda,
Yes, it's very simple to have a calculated field in a query.
Here's on that returns only the last 4 didgits of your field.

Select Right([SSnumber],4) As TieBreaker From yourTable

--
HTH
Dan Artuso, Access MVP


In my employee database, I have the field SS number. When determining seniority for employees who were hired on the same date, the
last 4 digits of the SS number is used to break the tie. The person with the higher number has more seniority. When I made my
first database, I had 2 fields in my table SSNumber and SSNumberLastFour. Is there a way in a query to extract the last four and
use it for the tie breaker or do I need this separate field in the table? It sure was easy to do the way I had it set up before but
since I am turning a new leaf I thought I would ask before I did it. <g>

Thanks,
Linda
 
I can make it whatever I want. I am setting up the tables now. If I use the Right ([ss], 4) will I then be able to sort by that in the query?

Thanks,
Linda


If SS is text, you can use Right([SS], 4) to get the last 4 characters.

If it's numeric, you might want to use type conversion functions to have
better control, like
CLng(Right(CStr([SS]), 4)), or you can use [SS] Mod 10000



--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


In my employee database, I have the field SS number. When determining
seniority for employees who were hired on the same date, the last 4 digits
of the SS number is used to break the tie. The person with the higher
number has more seniority. When I made my first database, I had 2 fields in
my table SSNumber and SSNumberLastFour. Is there a way in a query to
extract the last four and use it for the tie breaker or do I need this
separate field in the table? It sure was easy to do the way I had it set up
before but since I am turning a new leaf I thought I would ask before I did
it. <g>

Thanks,
Linda
 
Yes, you'll be able to sort on it.

If you create a query that adds SSNumberLastFour as a computed field then
look at the SQL, you'll see it'll be something like:

SELECT Field1, Field2, Right([SS], 4) AS SSNumberLastFour
FROM MyTable
ORDER BY Right([SS], 4)

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


I can make it whatever I want. I am setting up the tables now. If I use
the Right ([ss], 4) will I then be able to sort by that in the query?

Thanks,
Linda


If SS is text, you can use Right([SS], 4) to get the last 4 characters.

If it's numeric, you might want to use type conversion functions to have
better control, like
CLng(Right(CStr([SS]), 4)), or you can use [SS] Mod 10000



--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


In my employee database, I have the field SS number. When determining
seniority for employees who were hired on the same date, the last 4 digits
of the SS number is used to break the tie. The person with the higher
number has more seniority. When I made my first database, I had 2 fields in
my table SSNumber and SSNumberLastFour. Is there a way in a query to
extract the last four and use it for the tie breaker or do I need this
separate field in the table? It sure was easy to do the way I had it set up
before but since I am turning a new leaf I thought I would ask before I did
it. <g>

Thanks,
Linda
 
Thanks. :^)
Yes, you'll be able to sort on it.

If you create a query that adds SSNumberLastFour as a computed field then
look at the SQL, you'll see it'll be something like:

SELECT Field1, Field2, Right([SS], 4) AS SSNumberLastFour
FROM MyTable
ORDER BY Right([SS], 4)

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


I can make it whatever I want. I am setting up the tables now. If I use
the Right ([ss], 4) will I then be able to sort by that in the query?

Thanks,
Linda


If SS is text, you can use Right([SS], 4) to get the last 4 characters.

If it's numeric, you might want to use type conversion functions to have
better control, like
CLng(Right(CStr([SS]), 4)), or you can use [SS] Mod 10000



--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


In my employee database, I have the field SS number. When determining
seniority for employees who were hired on the same date, the last 4 digits
of the SS number is used to break the tie. The person with the higher
number has more seniority. When I made my first database, I had 2 fields in
my table SSNumber and SSNumberLastFour. Is there a way in a query to
extract the last four and use it for the tie breaker or do I need this
separate field in the table? It sure was easy to do the way I had it set up
before but since I am turning a new leaf I thought I would ask before I did
it. <g>

Thanks,
Linda
 
Back
Top