Display only last 7 characters in a field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form with a field called BSCB.
The fields data is a combination of 4 fields and data is displayd as follows.
H-A-1001-01
H-A-1001-02
H-A-1002-01
I just want to display the last 7 of each one.
ex:
1001-01
1001-02
1002-01
etc.
How would this be done, I also want this on the report that is printed form
this data.
 
Hi Michelle,

Right(NameOfTableOrQueryField, 7)

You can use this in a query which then becomes the recordsource for your
report.

TonyT..
 
=Right([YourField],7)
This could be in the control source of a text box. In a query on which a
form or report is based you could do something like this in an empty column
in the query design grid:
RightSeven: Right([YourField],7)
and bind a text box to RightSeven.
 
I have found the putting functions in the report when possible to be faster
than putting them in the query, particularly with Domain Aggragate functions.
I would suggest the control source of the text box on the report where the
value will be displayed. Same function, same syntax, just a different
location.

Example, inherited an application that had a report with many Domain
Aggragate functions in the query that took an hour to run. I moved them to
the report and in ran in less than a minute.
 
I get an error when I do this.

BruceM said:
=Right([YourField],7)
This could be in the control source of a text box. In a query on which a
form or report is based you could do something like this in an empty column
in the query design grid:
RightSeven: Right([YourField],7)
and bind a text box to RightSeven.

Michelle said:
I have a form with a field called BSCB.
The fields data is a combination of 4 fields and data is displayd as
follows.
H-A-1001-01
H-A-1001-02
H-A-1002-01
I just want to display the last 7 of each one.
ex:
1001-01
1001-02
1002-01
etc.
How would this be done, I also want this on the report that is printed
form
this data.
Click to expand...
Click to expand...
 
Can you post your code please,

TonyT..

Michelle said:
I get an error when I do this.

BruceM said:
=Right([YourField],7)
This could be in the control source of a text box. In a query on which a
form or report is based you could do something like this in an empty column
in the query design grid:
RightSeven: Right([YourField],7)
and bind a text box to RightSeven.

Michelle said:
I have a form with a field called BSCB.
The fields data is a combination of 4 fields and data is displayd as
follows.
H-A-1001-01
H-A-1001-02
H-A-1002-01
I just want to display the last 7 of each one.
ex:
1001-01
1001-02
1002-01
etc.
How would this be done, I also want this on the report that is printed
form
this data.
Click to expand...
Click to expand...
Click to expand...
 
Hi Klatuu,

I know where you are coming from, but I got the impression from the post
that the same data was going to be displayed on a form, and then printed via
a report, just seemed easier to edit the query for both to use.

TonyT..
 
Here is the Query Code

SELECT tblRHUInmtinfo.IN_INMNUM, tblRHUInmtinfo.IN_NAME,
tblRHUInmtinfo.IN_RACE, tblRHUInmtinfo.IN_SECTION, tblRHUInmtinfo.BSCB,
tblESSLog.SEP, tblESSLog.EXERCISE, tblESSLog.SHOWER, tblESSLog.[YARD #],
tblESSLog.RAZOR, tblESSLog.SCREAM, tblESSLog.MIRROR, tblESSLog.DATE
FROM tblRHUInmtinfo LEFT JOIN tblESSLog ON tblRHUInmtinfo.BSCB =
tblESSLog.BSCB
WHERE (((tblRHUInmtinfo.BSCB) Like "*" & [Enter Pod:] & "*"))
ORDER BY tblRHUInmtinfo.BSCB;


TonyT said:
Can you post your code please,

TonyT..

Michelle said:
I get an error when I do this.

BruceM said:
=Right([YourField],7)
This could be in the control source of a text box. In a query on which a
form or report is based you could do something like this in an empty column
in the query design grid:
RightSeven: Right([YourField],7)
and bind a text box to RightSeven.

I have a form with a field called BSCB.
The fields data is a combination of 4 fields and data is displayd as
follows.
H-A-1001-01
H-A-1001-02
H-A-1002-01
I just want to display the last 7 of each one.
ex:
1001-01
1001-02
1002-01
etc.
How would this be done, I also want this on the report that is printed
form
this data.
Click to expand...
Click to expand...
Click to expand...
 
What error? Did you substitute your actual field name for [YourField]?

Michelle said:
I get an error when I do this.

BruceM said:
=Right([YourField],7)
This could be in the control source of a text box. In a query on which a
form or report is based you could do something like this in an empty
column
in the query design grid:
RightSeven: Right([YourField],7)
and bind a text box to RightSeven.

Michelle said:
I have a form with a field called BSCB.
The fields data is a combination of 4 fields and data is displayd as
follows.
H-A-1001-01
H-A-1001-02
H-A-1002-01
I just want to display the last 7 of each one.
ex:
1001-01
1001-02
1002-01
etc.
How would this be done, I also want this on the report that is printed
form
this data.
Click to expand...
Click to expand...
Click to expand...
 
I went into into the form and in the control for that filed I used the
expression builder and made it =Right([BSCB], 7)
it gives me
#Error

BruceM said:
What error? Did you substitute your actual field name for [YourField]?

Michelle said:
I get an error when I do this.

BruceM said:
=Right([YourField],7)
This could be in the control source of a text box. In a query on which a
form or report is based you could do something like this in an empty
column
in the query design grid:
RightSeven: Right([YourField],7)
and bind a text box to RightSeven.

I have a form with a field called BSCB.
The fields data is a combination of 4 fields and data is displayd as
follows.
H-A-1001-01
H-A-1001-02
H-A-1002-01
I just want to display the last 7 of each one.
ex:
1001-01
1001-02
1002-01
etc.
How would this be done, I also want this on the report that is printed
form
this data.
Click to expand...
Click to expand...
Click to expand...
 
You can add the Right code into your query thus;
SELECT tblRHUInmtinfo.IN_INMNUM, tblRHUInmtinfo.IN_NAME,
tblRHUInmtinfo.IN_RACE, tblRHUInmtinfo.IN_SECTION, tblRHUInmtinfo.BSCB,
tblESSLog.SEP, tblESSLog.EXERCISE, tblESSLog.SHOWER, tblESSLog.[YARD #],
tblESSLog.RAZOR, tblESSLog.SCREAM, tblESSLog.MIRROR, tblESSLog.DATE, RIGHT(tblRHUInmtinfo.BSCB,7) AS RightValue
FROM tblRHUInmtinfo LEFT JOIN tblESSLog ON tblRHUInmtinfo.BSCB =
tblESSLog.BSCB
WHERE (((tblRHUInmtinfo.BSCB) Like "*" & [Enter Pod:] & "*"))
ORDER BY tblRHUInmtinfo.BSCB;
Click to expand...

If you want to compare that to [Enter Pod:] then you need;
WHERE ((RIGHT(tblRHUInmtinfo.BSCB),7) Like "*" & [Enter Pod:] & "*"))
Click to expand...
rather than referring to RightValue.

TonyT..
Michelle said:
Here is the Query Code

SELECT tblRHUInmtinfo.IN_INMNUM, tblRHUInmtinfo.IN_NAME,
tblRHUInmtinfo.IN_RACE, tblRHUInmtinfo.IN_SECTION, tblRHUInmtinfo.BSCB,
tblESSLog.SEP, tblESSLog.EXERCISE, tblESSLog.SHOWER, tblESSLog.[YARD #],
tblESSLog.RAZOR, tblESSLog.SCREAM, tblESSLog.MIRROR, tblESSLog.DATE
FROM tblRHUInmtinfo LEFT JOIN tblESSLog ON tblRHUInmtinfo.BSCB =
tblESSLog.BSCB
WHERE (((tblRHUInmtinfo.BSCB) Like "*" & [Enter Pod:] & "*"))
ORDER BY tblRHUInmtinfo.BSCB;


TonyT said:
Can you post your code please,

TonyT..

Michelle said:
I get an error when I do this.

:

=Right([YourField],7)
This could be in the control source of a text box. In a query on which a
form or report is based you could do something like this in an empty column
in the query design grid:
RightSeven: Right([YourField],7)
and bind a text box to RightSeven.

I have a form with a field called BSCB.
The fields data is a combination of 4 fields and data is displayd as
follows.
H-A-1001-01
H-A-1001-02
H-A-1002-01
I just want to display the last 7 of each one.
ex:
1001-01
1001-02
1002-01
etc.
How would this be done, I also want this on the report that is printed
form
this data.
Click to expand...
Click to expand...
Click to expand...
 
What happens if you bind the field to BSCB?

Michelle said:
I went into into the form and in the control for that filed I used the
expression builder and made it =Right([BSCB], 7)
it gives me
#Error

BruceM said:
What error? Did you substitute your actual field name for [YourField]?

Michelle said:
I get an error when I do this.

:

=Right([YourField],7)
This could be in the control source of a text box. In a query on
which a
form or report is based you could do something like this in an empty
column
in the query design grid:
RightSeven: Right([YourField],7)
and bind a text box to RightSeven.

I have a form with a field called BSCB.
The fields data is a combination of 4 fields and data is displayd as
follows.
H-A-1001-01
H-A-1001-02
H-A-1002-01
I just want to display the last 7 of each one.
ex:
1001-01
1001-02
1002-01
etc.
How would this be done, I also want this on the report that is
printed
form
this data.
Click to expand...
Click to expand...
Click to expand...
 
Change the name of the text box to something other than BSCB
--
Marsh
MVP [MS Access]

I went into into the form and in the control for that filed I used the
expression builder and made it =Right([BSCB], 7)
it gives me
#Error

BruceM said:
What error? Did you substitute your actual field name for [YourField]?

Michelle said:
I get an error when I do this.

:

=Right([YourField],7)
This could be in the control source of a text box. In a query on which a
form or report is based you could do something like this in an empty
column
in the query design grid:
RightSeven: Right([YourField],7)
and bind a text box to RightSeven.

I have a form with a field called BSCB.
The fields data is a combination of 4 fields and data is displayd as
follows.
H-A-1001-01
H-A-1001-02
H-A-1002-01
I just want to display the last 7 of each one.
ex:
1001-01
1001-02
1002-01
etc.
How would this be done, I also want this on the report that is printed
form
this data.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top