Display Abbreviation on Report instead of Field Values

[speciman_A]

Good day,



Forgive me if this has been asked before, but I haven't found it and I am relatively new to Access.

I am building a database with the intent to automate report generation for weekly meetings with my boss. Hopefully, when all is said and done, I will have a presentable report that automatically pulls information from subordinate shops through a front end I will email out. The problem I am having is I have a multi-value combo box with full length text on a form, but only want to display abbreviations on the actual report due to space considerations. For example, I would convert flux capacitor to a three digit code such as FLU. The form would say Flux Capacitor so that the end users don't have to look at a legend to figure it out, but the report would read FLU. Compounding the issue, as I mentioned previously, the field is a multi-value field, which means there may be multiple items listed on the report for one record.

Hopefully my description is clear, I look forward to any input the gurus out there may provide.

V/R

J.C.

[ranman256]

In the report query, have the qry pull the abbrevs.
put them on the rpt. (and dont use multi-value combos)

[June7]

You are saving the full length value and not the abbreviated version into multi-value field?

[speciman_A]

I have the full length value in a separate table so that the values can be easily changed later on. I used a basic query to pull the values for the combo box. The abbreviations are on the same table, but I'm not sure how to run a query for the report so that the input in the combo box is converted to abbreviations.

[June7]

If you save the abbreviation then there is no need to 'convert'. Report should show the saved abbreviation in a textbox control. Might have to manually create the textbox or change combobox to textbox.

[speciman_A]

The abbreviation is in a separate table so that I can associate it with the full length text, but my combo box uses the full length text. When I generate the report, the full length text is shown because it is the source for the combo box. Sorry if I'm not explaining this well.

My report looks something like this:
Last_Name | First Name | Training

The training field is the multi-value combo box, so when the user opens the respective form and selects all applicable training they have completed, one or more items display on the report. However, instead of displaying the following on the report:

Dole | Bob | Security Refresher Training, OSHA Certification Training, Don't Sell Secrets Training, Specific Systems Training

I want it to be condensed as follows:

Dole | Bob | SRT, OCT, DSST, SST

I have a table that looks like this:

Training_Full | Training_Abb

I created a query that pulls all values in the Training_Full field. This is the query that is the source for my combo box. I need to figure out how to use the Training_Abb for just the report.

I hope this helps, thanks.

[June7]

So you are saving the full text into table? Why don't you save the abbreviation? What are property settings of combobox - RowSource, ColumnCount, BoundColumn, ColumnWidths?

When you create a TEXTBOX on report and set its ControlSource property to the Training field, do you see abbreviations or the full descriptor?

[speciman_A]

Yes, the full text values selected from the combo box are saved into a table. I am not using the abbreviation because I want the user to see the full text. I only want the abbreviations for the report. My row source is the query I mentioned early that pulls all training titles from a separate table with only the values for the combo box. ColumnCount, BoundColumn are set to 1 if I remember correctly.

[June7]

It is possible to set up the combobox so user sees the full text but the abbreviation is saved. That is a multi-column combobox. That is why I asked for the property settings of combobox.

RowSource: SELECT Abbreviation, Description FROM Training;

BoundColumn: 1

ColumnCount: 2

ColumnWidths: 0";2"


Otherwise, the report RecordSource will need to be a query that joins the tables.

[speciman_A]

Outstanding, I will give it a try and follow-up. Thank you for your patience. I didn't think of setting the ColumnWidth to 0".

[speciman_A]

June7,

Looks like your advice is working. Thanks again for the help.

[June7]

Or try a combobox on report:

RowSource: SELECT Description, Abbreviation FROM Training;

BoundColumn: 1

ColumnCount: 2

ColumnWidths: 0";2"