Expression for report

[Zharia]

Can you please tell me the expression I use to join the employees names together in a report. In the Employees table they are entered as first name and last name.

Thanks

 

[chrismcbride]

Set the ControlSource of a text control on your Report to the name of the first name and last name fields in the source table. Like...

=[First_Name] & " " & [Last_Name]

Chris

 

[Zharia]

Thanks for the info Chris, but when I put that expression in my report, when I go to view it, a box pops up asking for the first name and last name???
Did I do something wrong?

 

[Rich@ITTC]

Hi Zharia

Are you sure you have these fields, from the Employees table presumably, in the underlying query that the form is based upon?

You can check by going into Design View for the Report and selecting the whole form and checking the Record Source. Click on the ellipsis at the end of the line for record Source (the three dots) and check the Query or SQL statement that the Report is based upon. Are your fields for First Name and Last Name there? If they aren't you will need to add them (either from the Employees table - if that is displayed in the upper pane of the Query design window - or by adding the table Employees to the query and then adding the two fields required).

If you have both the fields already in your query, then you will need to check the exact spelling. If your field names have spaces within them, you will need to enclose them in brackets e.g.

[First Name]&" "&[Last Name]

If the field names have no spaces (the better way of naming fields) then the following would be used:

[FirstName]&" "&[LastName]

The names have to be 100% accurate - any mistake will throw up a request by Access for you to provide the missing information.

HTH

Rich



[This message has been edited by Rich@ITTC (edited 11-07-2000).]

 

[Zharia]

Hi Rich,

I do have an employees table. On my form that my report is based on has "EmployeeID" on it that links back to this table. However on my report it comes up with the employeeID number rather than the name. I dont know how to change this to read expression you gave me that will give me the name rather than the number.

 

[chrismcbride]

OK I will assume that you have an Employee Report. You probably wrote the report using a Wizard. If that is so then you probably have a SQL string as the RecordSource for your Report. If that is so, then you can view it's contents by looking at the Property sheet. In order to reference the values within a table field they must be present in the records that make up your recordsource. So you should find something that looks like....

SELECT [tblEmployee].[First Name], [tblEmployee].[Last Name]...

If these are there then you can refer to the values that they store with the syntax I listed earlier

me.Full_Name = [First Name] & " " & [Last Name]

What Rich said is correct. If Access can not recognize the names from the list of names it finds in the RecordSource, then it will prompt for a Value.

If the First name and last name field are not included in the RecordSource of the Report, you could just add them right (be careful of the syntax), or you could write a whole new SQL Query with the Query Builder and cut the SQL string from there and paste it into the RecordSource property of your Report.
Any trouble, drop me a line
Chris

 

[Richie]

Are you using an existing text box and trying to change the data source? If so add a new unbound text box and then set it's control source to the expression you want.