Combine First and Last name fields for Report

[yes sir]

I know this has been asked before, but my situation seems to be a little different. I have three different tables that use the fields First Name and Last Name. So I'm assuming I must enter an expression different than the standard one.

I have created a query that has a field that combines the two correctly and have tried using this query field has the control source for my text box on my report, but it doesn't work. After closing the Report in design view, and reopening I get a message box that asks: Enter Parameter Value, and it has the title of my query listed.

What are the proper steps for adding a text box that displays the First and Last Names together if you have three separate tables using the same field names?

Also, I don't know if this will be a problem or not, but I plan on putting all three different table's First and Last names together on one Report.

Thanks for any help!

[ajetrumpet]

Also, I don't know if this will be a problem or not, but I plan on putting all three different table's First and Last names together on one Report.

This is not a good idea.

Also, a report based on a query that displays correctly when opened in a DS view should open fine without error or popups.

I think posting the query or even the file on this forum might help you get a quicker answer. Make sure you list what it is someone should look for in the file if they were to help you.

[yes sir]

I'll try posting screen shots before uploading the file. Thanks.

[yes sir]

This image contains the Design View for the Report with the expression builder for the combination of First and Last names.This image contains the Report in ReportView with a snapshot of the query I am using. As you can see the query correctly displays the First and Last names.

Currently on my report, I just have a text box for the first and last name. I would like to add this combination feature of first and last names for where it says Agent on the report as well. Where it says N/A and N/A, that is for the Agent's first and last name.

Why is it not recommended to have multiple first and last name combination's on one report page?

Thanks!

[ajetrumpet]

Why is it not recommended to have multiple first and last name combination's on one report page?

nothing really. It invites confusion, mostly in the future when the programmer has to remember what is related to what.


As for your pictures...

*What is the source of report? (recordsource property)
*What is the error you see when you enter the current source of that Tbox?


Also...be aware that you cannot simply refer to a field in a qry as a control source. The reason is similar to that of using DLOOKUP() without criteria. It will always give you the first value in the data field. The syntax I don't think is correct either. I always use lookup functions in tbox sources like this and never direct references like you are showing. The reason being - I believe reference syntax requirements in the property sheet of forms and reports have changed from version to version (don't quote me though).

Wouldn't hurt also to show an image of the query's sql code. (Use the [IMG] tags when posting to show inline images instead of links to their URL. thanks!)

[yes sir]

nothing really. It invites confusion, mostly in
*What is the source of report? (recordsource property)
*What is the error you see when you enter the current source of that Tbox?

Wouldn't hurt also to show an image of the query's sql code. (Use the [IMG] tags when posting to show inline images instead of links to their URL. thanks!)

The source for the Report is multiple sources really. I pull data from all of my tables except maybe one. Here is the SQL for the report's record source:SELECT Prospects.[Last Name], Prospects.Attachment, Prospects.[First Name], Prospects.Ht, Prospects.Wt, Prospects.College, Prospects.DoB, Prospects.Class, Agents.[First Name] AS [First Name_Agents], Agents.[Last Name] AS [Last Name_Agents], Agents.[Mobile Phone], Agents.[Business Phone], Agents.[E-mail Address]
FROM Agents INNER JOIN Prospects ON Agents.ID = Prospects.Agent;


Here is the error:


Query's sql code:

[ajetrumpet]

The source for the Report is multiple sources really.

what is it EXACTLY? if it's seriously too long, copy it out of the recordsource property line and paste it in notepad (please format it! e.g. - not one long line of jargon )

The query looks fine. I am assuming of course that all those nestings have never errored out. I assume you only have one LAST NAME / FIRST NAME field in the prospects table?

[yes sir]

what is it EXACTLY? if it's seriously too long, copy it out of the recordsource property line and paste it in notepad (please format it! e.g. - not one long line of jargon )

The query looks fine. I am assuming of course that all those nestings have never errored out. I assume you only have one LAST NAME / FIRST NAME field in the prospects table?

whoops. I just edited my previous post and just copied and pasted the SQL code for the Report. Luckily I have yet to add everything I want.

Yes, there is only one First name field and only one Last name field in the prospects table. Same for Coaches and Agents.

Thanks

[ajetrumpet]

SELECT Prospects.[Last Name], Prospects.Attachment, 

Prospects.[First Name], Prospects.Ht, Prospects.Wt, 

Prospects.College, Prospects.DoB, Prospects.Class, 

Agents.[First Name] AS [First Name_Agents], 

Agents.[Last Name] AS [Last Name_Agents], 

Agents.[Mobile Phone], Agents.[Business Phone], 

Agents.[E-mail Address]

FROM Agents INNER JOIN 

Prospects ON Agents.ID = Prospects.Agent;

if you want to nail the problem down to something within the report itself, paste this sql into a new qry sql window and run it. does it error? if not, the report is the problem

[yes sir]

if you want to nail the problem down to something within the report itself, paste this sql into a new qry sql window and run it. does it error? if not, the report is the problem

I'm sorry, but my lack of Access knowledge might be showing now.

I copied the code into a new query. And the query runs fine. Is that what we are looking for? No error messages when opening the query with the code you gave me?

[yes sir]

My guess would be that I'm not being specific enough with something (the query or the text box's control source on my report). Since I currently have two First and Last Name fields on the Report, then it must be causing confusion with the program.

[ajetrumpet]

YES. your report is the problem. if you want me to continue helping you out here, I'd really have to see the report. if you upload the file here, i'll take a look if you want. make sure to point out what to look at EXACTLY so the sifting through irrelevant material can be sidelined.

[yes sir]

Usually in times like these. I open up one of Access' default databases and copy how they do such things. They have one called "Students" that has reports on it doing exactly what I want. I'm just having trouble trying to translate everything to sink with my database.

[yes sir]

YES. your report is the problem. if you want me to continue helping you out here, I'd really have to see the report. if you upload the file here, i'll take a look if you want. make sure to point out what to look at EXACTLY so the sifting through irrelevant material can be sidelined.

I'm getting an invalid file message when attaching the file.

[ajetrumpet]

read this:

https://www.accessforums.net/faq.php...b3_attachments

you may have an unaccepted type or it might be too big. forums always accepts ZIPs. you can always zip it. also, compact it before you upload. it may be bloated if you haven't done this