Combining fields in report.. easy. Showing text field instead of ID #.... HUH?

[bazzanoid]

Afternoon,

I'm banging my head on a wall here with this - i had solved it before but only on single value report controls.

I'm combining three/four fields into one on a report, as below:

=[EAStaff1] & "," & [EAStaff2] & "," & [EAStaff3]

But instead of getting the [Staff Member] field, which is their specific names, it just displays the [ID] fields... as below:

Any thoughts?

Thanks!

 

[RuralGuy]

It sounds to me like you are using LookUp Fields in your tables

 

[bazzanoid]

It sounds to me like you are using LookUp Fields in your tables

It appears I was! I've just changed them to straightforward text fields as I can get the form to look up the values and enter into the table, but it still gives me the same trouble....

Combo box on report (tried changing to List Box, all i got for the trouble is a list of names from the Staff Member field, which is great, but it didn't filter them at all, and put them on separate lines instead of one one!)

Control Source: =[EAStaff1] & "," & [EAStaff2] & "," & [EAStaff3]
Row Source: SELECT EAStaff.[Staff Member] FROM EAStaff;

Any advice appreciated!

 

[RuralGuy]

ComboBoxes on Reports do not work. You need to work on the query that the Report is using. Join the EAStaff table on the ID field but display the [Staff Member] field.

 

[bazzanoid]

Ok, I've changed it to a list box, left the control source as above, changed the row source query to:

SELECT EAStaff.[Staff Member], EAStaff.ID FROM EAStaff INNER JOIN Bookings ON (EAStaff.[Staff Member] = Bookings.EAStaff1) AND (EAStaff.[Staff Member] = Bookings.EAStaff2) AND (EAStaff.[Staff Member] = Bookings.EAStaff3) AND (EAStaff.ID = Bookings.EAStaff2) AND (EAStaff.ID = Bookings.EAStaff3) AND (EAStaff.ID = Bookings.EAStaff1) ORDER BY EAStaff.[Staff Member];

And it now tells me I have a type mismatch expression....

How is it I can build an entire database without any issues then something that should be simple just isn't!!!!

Thanks!

 

[RuralGuy]

How can the following two equates be possible?
EAStaff.[Staff Member] = Bookings.EAStaff1
EAStaff.ID = Bookings.EAStaff1