I have a Append query with multiple subqueries within. All of ther criteria and subqueries work until I get to the last two - then I get a Synax Error.
ResPayer: Exists (SELECT DISTINCT mPayments.ContactsNo FROM mPayments INNER JOIN mPaymentsDetails ON mPayments.PaymentsNo = mPaymentsDetails.PaymentsNo INNER JOIN mReservations ON mPaymentsDetails.ReservationNo = mReservations.ReservationNo WHERE mReservations.EventsNo=[Forms]![frmMaintenanceMenu]![cboAddtoMLEvent] AND mPayments.ContactsNo = mContacts.ContactsNo)
The above should verify if any records where the mContacts.ContactsNo paid for a Reservations (their own or someone elses - doesn't matter). The only difference I can see between this and the other subqueries that work is it has more table joins.
Any ideas? I've been at this for 2 days and I'm just sick of looking at it!
I would bet it is the stupid bracketing that Access demands in the FROM clause. Create your subquery in another window than copy & paste the SQL Access generates. BTW - no need for DISTINCT in an EXISTS clause.