Link to home
Start Free TrialLog in
Avatar of Michael Paravicini
Michael ParaviciniFlag for Chile

asked on

Join on Expression gives an error

I have a table Pax (PaxId, PaxName) and a query qryPaxRooms (RoomPaxId, PaxName). RoomPaxId in the query is an expression with Nz(ResRooms.PaxId,ResPaxid) AS RoomPaxId. So if a reservation has no rooms yet assigned RoomPaxId will have the PaxId of the reservation otherwise the PaxId of the Room Owner (might be different). Now if I try to join table qryPaxRooms with a different table Invoices (InvID, InvDate, PaxId) on qryPaxRooms.RoomPaxId with Invoices.PaxId I get a type mismatch error. Is it not possible to do a join on expressions? Is there a different solution? Thank you so much for any help! Regards Michael
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
As your IDs probably are numeric, try to force a Long for the ID:

CLng(Nz(ResRooms.PaxId,ResPaxid)) AS RoomPaxId

Open in new window

Or you could attach a database with sample values.
Avatar of Michael Paravicini

ASKER

Thank you to both of you for your help. Best regards Michael