LEFT or RIGHT functions

I am brand-new to Access. I am using it at work for the first time and am
learning on the go. In the long term, I have the opportunity for formal
training, but in the short term I am working on a project with Access that
needs to be completed ASAP.

Here is my question: I have used the LEFT or RIGHT function in Excel and
know you can do a similar operation in Access to reduce data. In my current
project, I have a column with order numbers. Some have a three digit prefix
(like PRE-) with a dash, followed by a seven digit number. I need to make
these the same format as entries that do not have the prefix. This is for
the purpose of comparing these orders to entries in another table that does
not support the prefix.

I am hoping someone can provide some newbie-friendly advice on how to
accomplish a task I am sure is very simple once someone turns the light bulb
on for me...

Thanks!

I assume you want the right 7 digits. Just use the Right function like this --
Order_Num: Right([YourField],7)

If there are 7 digits or 11 it will work the same.

--
KARL DEWEY
Build a little - Test a little


"Wildcats" wrote:

I am brand-new to Access. I am using it at work for the first time and am
learning on the go. In the long term, I have the opportunity for formal
training, but in the short term I am working on a project with Access that
needs to be completed ASAP.

Here is my question: I have used the LEFT or RIGHT function in Excel and
know you can do a similar operation in Access to reduce data. In my current
project, I have a column with order numbers. Some have a three digit prefix
(like PRE-) with a dash, followed by a seven digit number. I need to make
these the same format as entries that do not have the prefix. This is for
the purpose of comparing these orders to entries in another table that does
not support the prefix.

I am hoping someone can provide some newbie-friendly advice on how to
accomplish a task I am sure is very simple once someone turns the light bulb
on for me...

Thanks!

If you are trying to use this for matching in a query, you may first
want to create a query that has all of the fields you want and one
more generated field that will have something like this:

OrderNumber: iif(left([TableName].[OrderNumberFieldName],
4)="Pre-",Right([TableName].[OrderNumberFieldName],7),[TableName].
[OrderNumberFieldName])

Then do the linking between this query and the other table using this
OrderNumber field.


Ron

On Thu, 21 Aug 2008 06:47:01 -0700, Wildcats wrote:

I am brand-new to Access. I am using it at work for the first time and am
learning on the go. In the long term, I have the opportunity for formal
training, but in the short term I am working on a project with Access that
needs to be completed ASAP.

Here is my question: I have used the LEFT or RIGHT function in Excel and
know you can do a similar operation in Access to reduce data. In my current
project, I have a column with order numbers. Some have a three digit prefix
(like PRE-) with a dash, followed by a seven digit number. I need to make
these the same format as entries that do not have the prefix. This is for
the purpose of comparing these orders to entries in another table that does
not support the prefix.

I am hoping someone can provide some newbie-friendly advice on how to
accomplish a task I am sure is very simple once someone turns the light bulb
on for me...

Thanks!

Here are some examples of adding or truncating text, using a known
value ("Pre-") as well as using an unknown value (one that contains a
"-").

Do you wish to 'permanently' add "Pre-" to those other records?
Run an Update Query.
It would look like this (change the table and field names as needed):

Update YourTable Set YourTable.[FieldName] = "Pre-" & [FieldName]
Where Left([FieldName],4) "Pre-"

Do you wish to 'permanently' remove the "Pre-" from those records that
contain it?

Update YourTable Set YourTable.[FieldName] = Mid([FieldName],5) Where
Left([FieldName]4) = "Pre-"

If the prefix can be something other than "Pre-" but would still
include the hyphen (i.e. "ahmde-", you could use, to remove the
prefix:

Update YourTable Set YourTable.[FieldName] =
Mid([FieldName],InStr([FieldName],"-")+1)

Regardless of the length of the prefix, this will return all text
following the "-".

Look up the Left, Right, Mid, and InStr functions in VBA help files.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

It kills me to have ask ask questions like this, but should I format like this:

Order_Num: Right(REFNO,7)

Where "REFNO" is the field in my table which contains the order numbers? Or
do I need to replace Order_Num with REFNO?

And do I plug this into a query or elsewhere?

As someone who is smart and can usually pick up new computer apps quickly, I
have been very frustrated by trying to learn Access on the fly, so I
appreciate the help and the simple terms.

"KARL DEWEY" wrote:

I assume you want the right 7 digits. Just use the Right function like this --
Order_Num: Right([YourField],7)

If there are 7 digits or 11 it will work the same.

--
KARL DEWEY
Build a little - Test a little


"Wildcats" wrote:

I am brand-new to Access. I am using it at work for the first time and am
learning on the go. In the long term, I have the opportunity for formal
training, but in the short term I am working on a project with Access that
needs to be completed ASAP.

Here is my question: I have used the LEFT or RIGHT function in Excel and
know you can do a similar operation in Access to reduce data. In my current
project, I have a column with order numbers. Some have a three digit prefix
(like PRE-) with a dash, followed by a seven digit number. I need to make
these the same format as entries that do not have the prefix. This is for
the purpose of comparing these orders to entries in another table that does
not support the prefix.

I am hoping someone can provide some newbie-friendly advice on how to
accomplish a task I am sure is very simple once someone turns the light bulb
on for me...

Thanks!

Order_Num: is what is known as an alias. If you just type:

Right(REFNO,7)

into a column, it will add the default Expr1: as the alias. Look at the SQL
view and you'll see something like:

Select Right(REFNO,7) As Oreder_Num From YourTableName
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Wildcats" wrote in message
...
It kills me to have ask ask questions like this, but should I format like
this:

Order_Num: Right(REFNO,7)

Where "REFNO" is the field in my table which contains the order numbers?
Or
do I need to replace Order_Num with REFNO?

And do I plug this into a query or elsewhere?

As someone who is smart and can usually pick up new computer apps quickly,
I
have been very frustrated by trying to learn Access on the fly, so I
appreciate the help and the simple terms.

"KARL DEWEY" wrote:

I assume you want the right 7 digits. Just use the Right function like
this --
Order_Num: Right([YourField],7)

If there are 7 digits or 11 it will work the same.

--
KARL DEWEY
Build a little - Test a little


"Wildcats" wrote:

I am brand-new to Access. I am using it at work for the first time and
am
learning on the go. In the long term, I have the opportunity for
formal
training, but in the short term I am working on a project with Access
that
needs to be completed ASAP.

Here is my question: I have used the LEFT or RIGHT function in Excel
and
know you can do a similar operation in Access to reduce data. In my
current
project, I have a column with order numbers. Some have a three digit
prefix
(like PRE-) with a dash, followed by a seven digit number. I need to
make
these the same format as entries that do not have the prefix. This is
for
the purpose of comparing these orders to entries in another table that
does
not support the prefix.

I am hoping someone can provide some newbie-friendly advice on how to
accomplish a task I am sure is very simple once someone turns the light
bulb
on for me...

Thanks!

Arvin:

I opened a new query in Design View. I selected my table "E-commerce." In
the Field, I entered:

=Right(REFNO,7)

Then I looked at the SQL view and it appeared just as you note it would in
your post. However, the query returns an empty dataset. Any ideas?

"Arvin Meyer [MVP]" wrote:

Order_Num: is what is known as an alias. If you just type:

Right(REFNO,7)

into a column, it will add the default Expr1: as the alias. Look at the SQL
view and you'll see something like:

Select Right(REFNO,7) As Oreder_Num From YourTableName
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Wildcats" wrote in message
...
It kills me to have ask ask questions like this, but should I format like
this:

Order_Num: Right(REFNO,7)

Where "REFNO" is the field in my table which contains the order numbers?
Or
do I need to replace Order_Num with REFNO?

And do I plug this into a query or elsewhere?

As someone who is smart and can usually pick up new computer apps quickly,
I
have been very frustrated by trying to learn Access on the fly, so I
appreciate the help and the simple terms.

"KARL DEWEY" wrote:

I assume you want the right 7 digits. Just use the Right function like
this --
Order_Num: Right([YourField],7)

If there are 7 digits or 11 it will work the same.

--
KARL DEWEY
Build a little - Test a little


"Wildcats" wrote:

I am brand-new to Access. I am using it at work for the first time and
am
learning on the go. In the long term, I have the opportunity for
formal
training, but in the short term I am working on a project with Access
that
needs to be completed ASAP.

Here is my question: I have used the LEFT or RIGHT function in Excel
and
know you can do a similar operation in Access to reduce data. In my
current
project, I have a column with order numbers. Some have a three digit
prefix
(like PRE-) with a dash, followed by a seven digit number. I need to
make
these the same format as entries that do not have the prefix. This is
for
the purpose of comparing these orders to entries in another table that
does
not support the prefix.

I am hoping someone can provide some newbie-friendly advice on how to
accomplish a task I am sure is very simple once someone turns the light
bulb
on for me...

Thanks!

Ok so as I learn more I figure out more. It looks like my original REFNO
Field includes extra spaces at the end, which is why the Query is returning
an empty dataset (As a sanity check, I ran a LEFT function in a query and
that returned results). Here is what I think I need to do ultimately:

1. Trim the extra spaces from the REFNO column.
2. Run the Right function to eliminate the prefix that exists on some of my
order numbers.
3. I would like to have my original table (about 25 fields) update with the
"new" order numbers, which at this point would no longer have prefixes.
4. Then I can use this table to compare to another table which contains the
data I need to compare to these orders (this second table only contains
orders WITHOUT prefixes, which is the point of this exercise to begin with.)

Can I accomplish 1, 2, and 3 in an Update Query with the proper SQL
functions built in? If so, does that Update Query need to include all fields
or just the REFNO field? And how do I run both the Trim function and the
Right function together?

"Wildcats" wrote:

Arvin:

I opened a new query in Design View. I selected my table "E-commerce." In
the Field, I entered:

=Right(REFNO,7)

Then I looked at the SQL view and it appeared just as you note it would in
your post. However, the query returns an empty dataset. Any ideas?

"Arvin Meyer [MVP]" wrote:

Order_Num: is what is known as an alias. If you just type:

Right(REFNO,7)

into a column, it will add the default Expr1: as the alias. Look at the SQL
view and you'll see something like:

Select Right(REFNO,7) As Oreder_Num From YourTableName
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Wildcats" wrote in message
...
It kills me to have ask ask questions like this, but should I format like
this:

Order_Num: Right(REFNO,7)

Where "REFNO" is the field in my table which contains the order numbers?
Or
do I need to replace Order_Num with REFNO?

And do I plug this into a query or elsewhere?

As someone who is smart and can usually pick up new computer apps quickly,
I
have been very frustrated by trying to learn Access on the fly, so I
appreciate the help and the simple terms.

"KARL DEWEY" wrote:

I assume you want the right 7 digits. Just use the Right function like
this --
Order_Num: Right([YourField],7)

If there are 7 digits or 11 it will work the same.

--
KARL DEWEY
Build a little - Test a little


"Wildcats" wrote:

I am brand-new to Access. I am using it at work for the first time and
am
learning on the go. In the long term, I have the opportunity for
formal
training, but in the short term I am working on a project with Access
that
needs to be completed ASAP.

Here is my question: I have used the LEFT or RIGHT function in Excel
and
know you can do a similar operation in Access to reduce data. In my
current
project, I have a column with order numbers. Some have a three digit
prefix
(like PRE-) with a dash, followed by a seven digit number. I need to
make
these the same format as entries that do not have the prefix. This is
for
the purpose of comparing these orders to entries in another table that
does
not support the prefix.

I am hoping someone can provide some newbie-friendly advice on how to
accomplish a task I am sure is very simple once someone turns the light
bulb
on for me...

Thanks!

Yes. Backup the database!
Create a select query with field REFNO twice. Edit the second to look like
this ---
Expr1: Right(Trim([REFNO]),7)
Run query and review results.
If it look ok then change query to an Update query.
Copy all after Expr1: and paste in the Update To row of the
design view grid under field REFNO.
Run the query.
Change query back to select, run, review results.

--
KARL DEWEY
Build a little - Test a little


"Wildcats" wrote:

Ok so as I learn more I figure out more. It looks like my original REFNO
Field includes extra spaces at the end, which is why the Query is returning
an empty dataset (As a sanity check, I ran a LEFT function in a query and
that returned results). Here is what I think I need to do ultimately:

1. Trim the extra spaces from the REFNO column.
2. Run the Right function to eliminate the prefix that exists on some of my
order numbers.
3. I would like to have my original table (about 25 fields) update with the
"new" order numbers, which at this point would no longer have prefixes.
4. Then I can use this table to compare to another table which contains the
data I need to compare to these orders (this second table only contains
orders WITHOUT prefixes, which is the point of this exercise to begin with.)

Can I accomplish 1, 2, and 3 in an Update Query with the proper SQL
functions built in? If so, does that Update Query need to include all fields
or just the REFNO field? And how do I run both the Trim function and the
Right function together?

"Wildcats" wrote:

Arvin:

I opened a new query in Design View. I selected my table "E-commerce." In
the Field, I entered:

=Right(REFNO,7)

Then I looked at the SQL view and it appeared just as you note it would in
your post. However, the query returns an empty dataset. Any ideas?

"Arvin Meyer [MVP]" wrote:

Order_Num: is what is known as an alias. If you just type:

Right(REFNO,7)

into a column, it will add the default Expr1: as the alias. Look at the SQL
view and you'll see something like:

Select Right(REFNO,7) As Oreder_Num From YourTableName
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Wildcats" wrote in message
...
It kills me to have ask ask questions like this, but should I format like
this:

Order_Num: Right(REFNO,7)

Where "REFNO" is the field in my table which contains the order numbers?
Or
do I need to replace Order_Num with REFNO?

And do I plug this into a query or elsewhere?

As someone who is smart and can usually pick up new computer apps quickly,
I
have been very frustrated by trying to learn Access on the fly, so I
appreciate the help and the simple terms.

"KARL DEWEY" wrote:

I assume you want the right 7 digits. Just use the Right function like
this --
Order_Num: Right([YourField],7)

If there are 7 digits or 11 it will work the same.

--
KARL DEWEY
Build a little - Test a little


"Wildcats" wrote:

I am brand-new to Access. I am using it at work for the first time and
am
learning on the go. In the long term, I have the opportunity for
formal
training, but in the short term I am working on a project with Access
that
needs to be completed ASAP.

Here is my question: I have used the LEFT or RIGHT function in Excel
and
know you can do a similar operation in Access to reduce data. In my
current
project, I have a column with order numbers. Some have a three digit
prefix
(like PRE-) with a dash, followed by a seven digit number. I need to
make
these the same format as entries that do not have the prefix. This is
for
the purpose of comparing these orders to entries in another table that
does
not support the prefix.

I am hoping someone can provide some newbie-friendly advice on how to
accomplish a task I am sure is very simple once someone turns the light
bulb
on for me...

Thanks!

Karl, thank you for the help. It worked perfectly!

Any recommendations for comparing Table A to Table B using the common data
of order numbers? I have external shipment data in one table and internal
data in another and want to match the records for an order number in each
table so that I can compare what we paid to ship something to what we charged
our customer for shipping.

Now that I have the order numbers cleaned up in my "A" table, they match the
order numbers in my "B" table. I'm sure Access has some sort of tool to look
at Table A and pick out all the lines where an order number matches one found
in Table B. Am I right?

"KARL DEWEY" wrote:

Yes. Backup the database!
Create a select query with field REFNO twice. Edit the second to look like
this ---
Expr1: Right(Trim([REFNO]),7)
Run query and review results.
If it look ok then change query to an Update query.
Copy all after Expr1: and paste in the Update To row of the
design view grid under field REFNO.
Run the query.
Change query back to select, run, review results.

--
KARL DEWEY
Build a little - Test a little


"Wildcats" wrote:

Ok so as I learn more I figure out more. It looks like my original REFNO
Field includes extra spaces at the end, which is why the Query is returning
an empty dataset (As a sanity check, I ran a LEFT function in a query and
that returned results). Here is what I think I need to do ultimately:

1. Trim the extra spaces from the REFNO column.
2. Run the Right function to eliminate the prefix that exists on some of my
order numbers.
3. I would like to have my original table (about 25 fields) update with the
"new" order numbers, which at this point would no longer have prefixes.
4. Then I can use this table to compare to another table which contains the
data I need to compare to these orders (this second table only contains
orders WITHOUT prefixes, which is the point of this exercise to begin with.)

Can I accomplish 1, 2, and 3 in an Update Query with the proper SQL
functions built in? If so, does that Update Query need to include all fields
or just the REFNO field? And how do I run both the Trim function and the
Right function together?

"Wildcats" wrote:

Arvin:

I opened a new query in Design View. I selected my table "E-commerce." In
the Field, I entered:

=Right(REFNO,7)

Then I looked at the SQL view and it appeared just as you note it would in
your post. However, the query returns an empty dataset. Any ideas?

"Arvin Meyer [MVP]" wrote:

Order_Num: is what is known as an alias. If you just type:

Right(REFNO,7)

into a column, it will add the default Expr1: as the alias. Look at the SQL
view and you'll see something like:

Select Right(REFNO,7) As Oreder_Num From YourTableName
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Wildcats" wrote in message
...
It kills me to have ask ask questions like this, but should I format like
this:

Order_Num: Right(REFNO,7)

Where "REFNO" is the field in my table which contains the order numbers?
Or
do I need to replace Order_Num with REFNO?

And do I plug this into a query or elsewhere?

As someone who is smart and can usually pick up new computer apps quickly,
I
have been very frustrated by trying to learn Access on the fly, so I
appreciate the help and the simple terms.

"KARL DEWEY" wrote:

I assume you want the right 7 digits. Just use the Right function like
this --
Order_Num: Right([YourField],7)

If there are 7 digits or 11 it will work the same.

--
KARL DEWEY
Build a little - Test a little


"Wildcats" wrote:

I am brand-new to Access. I am using it at work for the first time and
am
learning on the go. In the long term, I have the opportunity for
formal
training, but in the short term I am working on a project with Access
that
needs to be completed ASAP.

Here is my question: I have used the LEFT or RIGHT function in Excel
and
know you can do a similar operation in Access to reduce data. In my
current
project, I have a column with order numbers. Some have a three digit
prefix
(like PRE-) with a dash, followed by a seven digit number. I need to
make
these the same format as entries that do not have the prefix. This is
for
the purpose of comparing these orders to entries in another table that
does
not support the prefix.

I am hoping someone can provide some newbie-friendly advice on how to
accomplish a task I am sure is very simple once someone turns the light
bulb
on for me...

Thanks!