Using the Right Function

  • Thread starter Thread starter Iteach2
  • Start date Start date
I

Iteach2

I have a subreport control that generates an account number. I only want to
display the last three characters on the report. I tried using the Right
function in the query but it does not work.

Can you help me with this? Can it be solved in the query or does it require
VBA coding?

Please advise
 
I have a subreport control that generates an account number. I only want to
display the last three characters on the report. I tried using the Right
function in the query but it does not work.

Can you help me with this? Can it be solved in the query or does it require
VBA coding?

Please advise
Click to expand...

Just a helpful tip to help you get good responses when asking
questions in newsgroups.
Words like 'That didn't work' gives any potential reader who might
want to help you absolutely no useful information.
What didn't happen?
What did happen?
What did you expect to happen?
Where did you place the code?
What is the exact code you wrote (copied directly from your database
and pasted here so we can see if you didn't simply mis-write the
code)?
Answers to those questions would be helpful to us .... to help you!

As you haven't given the exact expression you used in the query, all I
can say is

NewColumn:Right([FieldName],3)

should return, in that query, the last 3 characters in a field.
If that query is the recordsource for the sub-report, use that query's
[NewColumn] field in the subreport and it should display the last 3
characters.
 
Hi,

That is odd as it works for me. What type of data is your account
number? Text? Numeric (integer, long, double, other)? If text are there
trailing spaces? For a specific account number, what do you get for a
result? Show us how you are trying to use the Right() function.

Clifford Bass
 
Fred,
Thanks for the advice.

I opened the query used by the report. In design view, I entered the
following text on the criteria row of the AccountNum field in the query grid:

Right([AccountNum],3)
When I ran the query I got an error message regarding the same field name in
2 tables in the query. I then entered this data:
Right([Month End Data.AccountNum],3)
No records returned

In the query there are two tables with the AccountNum field. It is a text
field with a field size of 20 that contains a 11-character account number
(comprised of numbers, not text) I only want to show the last 3 digits of
the account number.

Should the field size be changed? I did not design this database, I am only
making modifications to it.

Thanks,
The Teach

fredg said:
I have a subreport control that generates an account number. I only want to
display the last three characters on the report. I tried using the Right
function in the query but it does not work.

Can you help me with this? Can it be solved in the query or does it require
VBA coding?

Please advise
Click to expand...

Just a helpful tip to help you get good responses when asking
questions in newsgroups.
Words like 'That didn't work' gives any potential reader who might
want to help you absolutely no useful information.
What didn't happen?
What did happen?
What did you expect to happen?
Where did you place the code?
What is the exact code you wrote (copied directly from your database
and pasted here so we can see if you didn't simply mis-write the
code)?
Answers to those questions would be helpful to us .... to help you!

As you haven't given the exact expression you used in the query, all I
can say is

NewColumn:Right([FieldName],3)

should return, in that query, the last 3 characters in a field.
If that query is the recordsource for the sub-report, use that query's
[NewColumn] field in the subreport and it should display the last 3
characters.
Click to expand...
 
Clifford,
Thanks for the reply.

The data is numerical, 9 digits with 2 dashes. The field size is 20.
I inserted the Right() function in the criteria row of the query for the
report.

Should I use the function on the report instead?

Thanks
The Teach
 
Iteach2 said:
Right([AccountNum],3)
When I ran the query I got an error message regarding the same field name in
2 tables in the query. I then entered this data:
Right([Month End Data.AccountNum],3)
No records returned
Click to expand...

Since your table name includes spaces, you must bracket the table name
separately.

Right([Month End Data].AccountNum,3)

However, you can assign an alias for your table in the query's FROM clause:

FROM [Month End Data] AS med

Then you won't need to add brackets for that name elsewhere in your query:

Right(med.AccountNum,3)

But, personally, I avoid spaces in table (and field) names, so I would
change the table's name to Month_End_Data. I find that less painful in
the long run.

Good luck,
Hans
 
Hi,

Actually, then it is a text field/value. I see that Hans has given you
the correct way to deal with the situation. I would add that you can use it
in either the query or report. The choice is up to you. If in the query you
would do essentially what Fred gave you. If in the report you would create
an unbound text box and set its Control Source property to an equals sign
followed by what Hans gave you:

=Right([Month End Data].AccountNum,3)

Clifford Bass
 
Fred,
Thanks for the advice.

I opened the query used by the report. In design view, I entered the
following text on the criteria row of the AccountNum field in the query grid:

Right([AccountNum],3)
When I ran the query I got an error message regarding the same field name in
2 tables in the query. I then entered this data:
Right([Month End Data.AccountNum],3)
No records returned

In the query there are two tables with the AccountNum field. It is a text
field with a field size of 20 that contains a 11-character account number
(comprised of numbers, not text) I only want to show the last 3 digits of
the account number.

Should the field size be changed? I did not design this database, I am only
making modifications to it.

Thanks,
The Teach

fredg said:
I have a subreport control that generates an account number. I only want to
display the last three characters on the report. I tried using the Right
function in the query but it does not work.

Can you help me with this? Can it be solved in the query or does it require
VBA coding?

Please advise
Click to expand...

Just a helpful tip to help you get good responses when asking
questions in newsgroups.
Words like 'That didn't work' gives any potential reader who might
want to help you absolutely no useful information.
What didn't happen?
What did happen?
What did you expect to happen?
Where did you place the code?
What is the exact code you wrote (copied directly from your database
and pasted here so we can see if you didn't simply mis-write the
code)?
Answers to those questions would be helpful to us .... to help you!

As you haven't given the exact expression you used in the query, all I
can say is

NewColumn:Right([FieldName],3)

should return, in that query, the last 3 characters in a field.
If that query is the recordsource for the sub-report, use that query's
[NewColumn] field in the subreport and it should display the last 3
characters.
Click to expand...
Click to expand...

I believe you are confused.
I certainly am.
Your original message stated you wish to show just the last 3
characters in the field.
I only want to display the last three characters on the report.<
Click to expand...

That has NOTHING to do with criteria.
Criteria restricts the records shown, not parts of a field.

Also, if you have more than one table or query do you have a
relationship established between the 2 tables/queries.

If the AccountNum is something like 25815512369 and you just wish to
show the last 3 characters (369) then......

Open the query in design view.
In a NEW COLUMN write, on the Field row (exactly as I have below):

LastThree:Right([Month End Data].[AccountNum],3)

Note the column name LastThree followed by a colon, and also the
separate brackets around both the table and the field names.

Make sure the Show check box is checked.
Do not enter anything else in this column.

Run the query. In addition to all the other fields in the query you
should have a column, named [LastThree], displaying the last 3
characters of the [AccountNum] field.

Use this [LastThree] field in your report (not the AccountNum field) ,
where you wish to display the 3 numbers.

Now if you meant something else in your original message, please
repost with more information.
 
one problem is that you should not be entering what you want to display in the
criteria row. It should be entered into the field row.

You probably need something like this entered into a field "cell".
Last3: Right([Month End Data].[AccountNum],3)

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

The solution worked!



John Spencer said:
one problem is that you should not be entering what you want to display in the
criteria row. It should be entered into the field row.

You probably need something like this entered into a field "cell".
Last3: Right([Month End Data].[AccountNum],3)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Fred,
Thanks for the advice.

I opened the query used by the report. In design view, I entered the
following text on the criteria row of the AccountNum field in the query grid:

Right([AccountNum],3)
When I ran the query I got an error message regarding the same field name in
2 tables in the query. I then entered this data:
Right([Month End Data.AccountNum],3)
No records returned

In the query there are two tables with the AccountNum field. It is a text
field with a field size of 20 that contains a 11-character account number
(comprised of numbers, not text) I only want to show the last 3 digits of
the account number.

Should the field size be changed? I did not design this database, I am only
making modifications to it.

Thanks,
The Teach
Click to expand...
Click to expand...
 
Back
Top