Query/Report Question

[pretoria]

--------------------------------------------------------------------------

I am trying to combine firstname and lastname in a listbox on a report. It seems to work for my forms but I haven't gotten it work yet for a report. Can someone take a look at my WHERE statement to see what I'm doing wrong. The report is based on the LastLogQuery2

I have the following sql statement

SELECT [LastLogQuery2].[FirstName] + ' ' +[LastLogQuery2].[LastName] FROM [LastLogQuery2] WHERE ((([LastLogQuery2].ContactID)=Reports![CallReport]![ContactID]))

 

[ScottGem]

SELECT [LastLogQuery2].[FirstName] & ' ' &[LastLogQuery2].[LastName] AS Fullname FROM [LastLogQuery2]

Also, your WHERE statement doesn't make sense. If the Report is based on the query, them comparing the ContactID to the report doesn't make sense. Where is contactID originating from? We need to know more about your table.

 

[pretoria]

Okay, then should I be comparing the ContactID rendered in the LastLogQuery2 to the original table where ContactID resides namely "Contacts" table?

is this enough information?

p.s. LastLogQuery2 is a join between records from three tables.
Companies
Contacts
CallHistory

 

[ScottGem]

That's not clear. Why are you including the comparison in the first place? Is it to apply a filter? If so, then you need to pull the ContactID from somewhere else.

 

[pretoria]

You're right. I'm not comparing anything. wrong choice of words. I already have the firstname and lastname fields in my report (queried from LastLogQuery2). The simple object I'm trying to achieve is combining the firstname lastname fields in my report. I was hoping I could do that by placing an unbound listbox in the report with this select statement above.

If I apply your statement...
SELECT [LastLogQuery2].[FirstName] & ' ' &[LastLogQuery2].[LastName] AS Fullname FROM [LastLogQuery2]

... it only renders the first record on all report records.

Now you know what I want to do, perhaps you have a better insight for a solution.

much appreciated.

 

[pretoria]

Scott Gem,
I've finally sovled it in a slightly different way. I made a third query with just Fullname and ContactID and joined that to the LastLogQuery2. In my report I just included the field Fullname.

Thanks for you help, you made me think more critically for a logical solution.

 

[ScottGem]

Not sure why you would need another query. If you purpose was a list of names in one control, then you could have set the RecordSource of the report to LastLogQuery2 and bind a text control to the Fullname column.