=Trim with value from a combobox?

[excellenthelp]

Hi Accessers,



How should I edit the code below to reflect the correct value using Trim?

=Trim([PhyCity] & ", " & [PhyState] & " " & [PhyPostalCode])

I want the result to show 'TX' instead of '5' (which is the ID of the record).

Help!

[pbaldy]

Try
[PhyState].Column(1)

or the appropriate zero based column number.

[excellenthelp]

pbaldy,

Thank you for the suggestion. It does, unfortunately, not work. After I type [PhyState].Column(1), it automatically turns to [PhyState].[Column](1) and spits out #NAME? on the report.

Next suggestion? I will continue to do some research on this as well.

[pbaldy]

Does the combo include the relevant field in the row source? Is the column count property of the combo correct?

[excellenthelp]

Yes, there are only two columns: first with ID and second with States.

However, there is no comboboxes on the report. I am using the TRIM function. Below is a quick snapshot of the report:

[pbaldy]

I'm confused since the thread title mentions a combo. For a report, I'd expect it to be based on a query that joins the main table to the table with states, enabling you to return the text value as a field for the report. Your formula would reference that field.

[excellenthelp]

Ahh pbaldy, my bad. I was referencing a combo for a reason! The query the report is based on is:

SELECT tblAIA_ACAList.ACAName, tblAIA_ACAList.Acronym, tblAIA_ACAList.PhyAddress1, tblAIA_ACAList.PhyAddress2, tblAIA_ACAList.PhyCity, tblAIA_ACAList.PhyState, tblAIA_ACAList.PhyPostalCode, tblAIA_ACAList.PhyCountry, tblAIA_ACAList.WebsiteFormatted, tblAIA_ACAList.PublicEMail, tblAIA_ACAList.PublicPhone, tblAIA_ACAList.Contact1, tblAIA_ACAList.Contact1EMail, tblAIA_ACAList.AccredStatus, tblAIA_ACAList.NOPAccredDoc, tblAIA_OperatingLocationsAccredScopes.USStatesList.ValueFROM (tblSupport_USStatesList INNER JOIN tblAIA_ACAList ON tblSupport_USStatesList.ID = tblAIA_ACAList.PhyState) INNER JOIN tblAIA_OperatingLocationsAccredScopes ON tblAIA_ACAList.ID = tblAIA_OperatingLocationsAccredScopes.ACAID
WHERE (((tblAIA_ACAList.AccredStatus)=1 Or (tblAIA_ACAList.AccredStatus)=2));

Hope this helps?

[pbaldy]

I would assume there's a field in tblSupport_USStatesList that contains the text you want. Add that field to the query if it isn't already and reference that field in your formula.

[excellenthelp]

Ah it turned out you were right, pbadly. it was my query. I just needed to redo it and it works now by fixing the row source, bound column and number of columns and its widths.

Thanks!!!

Marking this as SOLVED.

[pbaldy]

Glad you got it working!