Concatenated field displays Primary Key, need description

[MissaLissa]

Greetings Experts

I have a subform that contains a combo box for "Reason Category". The Reason Category table contains Category ID (Primary Key) and Reason Category.

0	N/A
1	Underwriting
2	Marketing & Sales
3	Claims
4	Policyholder Services
5	Miscellaneous

<tbody>
[TH="bgcolor: #C0C0C0"] Category ID
[/TH]
[TH="bgcolor: #C0C0C0"] Reason Category
[/TH]

</tbody>

I have another combo box for "Reason" which displays items based on what was chosen in the Reason Category field. The Reason table contains the Primary Key number from the Reason Category table. (small sample below)

3	Unsatisfactory Settlements
0	U&C Reduction
2	Replacement
1	Refusal to Insured
1	Recession

<tbody>
[TH="bgcolor: #C0C0C0"] Category Number
[/TH]
[TH="bgcolor: #C0C0C0"] Reason
[/TH]

</tbody>

These are set up so that categories chosen are passed through to a master table. The data passed through is the description not the primary key. However, when creating a select query which creates a new field with the Reason Category and the Reason fields concatenated, the results are as: 3 - Unsatisfactory Settlements. I need it to be displayed as: Claims - Unsatisfactory Settlements

Can someone please help me solve this issue?

 

[MissaLissa]

Issue: If I select the Reason Category and/or Reason fields from the Master table I get the description. Great, That works fine… But if I concatenate the two fields in a select query I get the Reason Category primary key. (Expression: [tbl_001Master].[Reason Category] & " - " & [tbl_001Master].[Reason])
[TABLE="width: 775"]
<thead>[TR]
[TH]Reason Category
[/TH]
[TH]Reason
[/TH]
[TH]Concatenated
[/TH]
[/TR]
</thead><tbody>[TR]
[TD]Claims
[/TD]
[TD]Denial of Claim
[/TD]
[TD]3 - Denial of Claim
[/TD]
[/TR]
[TR]
[TD]Underwriting
[/TD]
[TD]Cancellation
[/TD]
[TD]1 - Cancellation
[/TD]
[/TR]
[TR]
[TD]Claims
[/TD]
[TD]Unsatisfactory Settlements
[/TD]
[TD]3 - Unsatisfactory Settlements
[/TD]
[/TR]
</tbody>[/TABLE]

To answer your question…Yes, there are two combo boxes in the form. I have them set up so that when you choose the Reason Category only the reasons associated with the chosen category will be displayed in the Reason box. I want both boxes to show the description, not the Category ID/Number.

Combo Box 1 – cboReasonCategory – bound to Column 1, Column Count 2, Column Widths 0”;1”. When I click on the dropdown only the Reason Category appears.

Combo Box Row Source:
SELECT [tbl_Reason Category].[Category ID], [tbl_Reason Category].[Reason Category]
FROM [tbl_Reason Category]
WHERE ((([tbl_Reason Category].[Category ID])>0))
ORDER BY [tbl_Reason Category].[Category ID];
[TABLE="width: 355"]
<thead>[TR]
[TH="colspan: 2"]tbl_Reason Category
[/TH]
[/TR]
[TR]
[TH]Category ID
[/TH]
[TH]Reason Category
[/TH]
[/TR]
</thead><tbody>[TR]
[TD]0
[/TD]
[TD]N/A
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Underwriting
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Marketing & Sales
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Claims
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Policyholder Services
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Miscellaneous
[/TD]
[/TR]
</tbody>[/TABLE]

Combo Box 2 – cboReason – bound to Column 1, Column Count 1, Column Widths 1. This combo box displays only the items that are associated with the Reason Category ID that was chosen in cboReasonCategory combo box.”.








Combo Box Row Source:
SELECT tbl_Reason.Reason
FROM tbl_Reason
WHERE (((tbl_Reason.[Category Number])=[Forms]![f_CalPERS Main Menu]![f_CalPERS Complaints].[Form]![cboReasonCategory]));
[TABLE="width: 403"]
<thead>[TR]
[TH="colspan: 2"]tbl_Reason
[/TH]
[/TR]
[TR]
[TH]Category Number
[/TH]
[TH]Reason
[/TH]
[/TR]
</thead><tbody>[TR]
[TD]3
[/TD]
[TD]Unsatisfactory Settlements
[/TD]
[/TR]
[TR]
[TD]0
[/TD]
[TD]U&C Reduction
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Replacement
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Refusal to Insured
[/TD]
[/TR]
</tbody>[/TABLE]

Table Relationships:
tbl_Reason Category.Reason Category is linked to tbl_001Master.Reason Category
tbl_Reason.Reason is linked to tbl_001Master.Reason
tbl_Reason Category.Category ID is linked to tbl_Reason.Category Number

 

[xenou]

Do you actually have lookups in your tables? That may be why this is happening (because otherwise what you describe is impossible). Anyway, try a lookup on the Reason Category table using the reason number. Because we are obviously getting the number/ID, not the description.

DLOOKUP("[Reason Category]", "[Reason Category]","[Category ID] = " & [tbl_001Master].[Reason Category])

(above function is written with the assumption that category id in the Reason Category table is numeric)