Trouble concatenating using tblName.FieldName

[markmarko]

I am creating some reports, and I'd like to concatenate fields. Some of the
fields have the same name as fields from other tables used for the report,
therefore I'd like to use the format tblName.fieldName (as opposed to just
fieldName).

So, as far as I can tell, to enter more than 1 field with "&" in between,
the control source needs to begin with an =.

If I do so, and view the form, it asks for the parameter tblName. It's
apparently not seeing it as part of the field name. Any tips?

 

[Duane Hookom]

I would rename/alias the field in the report's record source query.
tblEmp.FirstName as EmpFirstName, tblCust.FirstName as CustFirstName
Then, you have unique field names in your report and know exactly where the
fields came from.

 

[markmarko]

Well, both are referenced in calculated controls and VBA numerous times, so I
would need to somehow find all instances of the one I wanted to change in
order to change the field name.

Any suggestions on that front?

 

[KARL DEWEY]

so I would need to somehow find all instances of the one I wanted to change
in
order to change the field name.

A given field can have multiple aliases like this --
SELECT A_Adams.[Trade Date], A_Adams.Quantity AS [Some], A_Adams.Quantity AS
[All]
FROM A_Adams;

 

[markmarko]

Well, perhaps I need to give an example.
tblCustomers has the fields LastName and FirstName.

I want to concatenate in a report customer LastName and FirstName, with a
comma in between.

If I use the Add existing Fields menu, and drag the fields LastName and
FirstName onto the report, they work.

But since I also include Sales Rep name. tblSalesReps also has fields
LastName and FirstName.

In vba (etc) one can identify each by including the table name ie
[Customers].LastName vs. [SalesReps].LastName.

However, using that syntax in a control behind an = sign, so I can
concatentate (ie =[Customers].LastName] & [Customers].FirstName) results in
an error that my control contains an invalid control source.







If I create a calculated control with the Control Source of
=[Customers].LastName

 

[Evi]

I bet you'll never make that field name error again!

Your function in your query could read

CustomerName: [TblCustomers].[LastName] & (", " + [TblCustomers].[FirstName]

(the plus is used to ensure that you don't get a redundant comma if you
don't have firstname for your customer)

You can use this field in your report rather than LastName and FirstName

Hopefully, you will now be able to use your Salesman firstname and Lastname
fields in safety, concatenating them within a new text box (not just typing
into the existing field - it makes Access grumpy)


Evi

 

[Duane Hookom]

To repeat, go back to your report's record source query and alias the
fields/columns there so that you don't have any confusion in your report. It
just works. I would never create a report that had two of the same name in
the Field List.

 

[markmarko]

Ohhhhhh, I see... I didn't realize you were speaking of aliasing them in the
query, since my question had been about concatenating in the report textbox
control.

Thank you very much!

 

[markmarko]

I bet you'll never make that field name error again!

I didn't realize it was an error! It's pretty managable in other
circumstances, didn't know access would get all fussy about it in this one
instance.

Your function in your query could read

CustomerName: [TblCustomers].[LastName] & (", " + [TblCustomers].[FirstName]

That's a good idea too, to concatenate in the query. Thank you!