Unable to find what payments have not been made from SQL statement need to find and list all unpaid invoices

When I run this SQL i am encountering "enter parameter" although have not set any criteria

Please find the code below

I have tired to use = and is

SELECT TraderAccomodationPaymentNo as PaymentNo, AmountPaid as PaidAmount, DateOfPayment as DatePaid, TraderAccomodationInvoiceNo as InvoiceNo, PaymentMethod as PaymentType
FROM [TraderAccomodationPayment] 
WHERE PaidAmount IS NULL

UNION 

SELECT TraderSeminarPaymentNo as PaymentNo, AmountPaid as PaidAmount, DateOfPayment as DatePaid, TraderSeminarInvoiceNo as InvoiceNo, PaymentMethod as PaymentType
FROM [TraderSeminarPayment]
WHERE PaidAmount IS NULL

UNION

SELECT AttendeeAccommodationPaymentNo as PaymentNo, AmountPaid as PaidAmount, DateOfPayment as DatePaid, AttendeeAccommodationInvoiceNo as InvoiceNo, PaymentMethod as PaymentType
FROM [AttendeeAccomodationPayment]
WHERE PaidAmount IS NULL

UNION 

SELECT AttendeeSeminarPaymentNo as PaymentNo, AmountPaid as PaidAmount, DateOfPayment as DatePaid, AttendeeSeminarInvoiceNo as InvoiceNo, PaymentMethod as PaymentType
FROM [AttendeeSeminarPayment]
WHERE PaidAmount IS NULL

I would like to be able to find all unpaid invoices.

1 Answers

0
Gordon Linoff On

You cannot use column aliases in the WHERE. MS Access does not recognize PaidAmount, so it assumes it is a parameter and asking for you to input the value.

You need to use the original column name. I also strongly recommend UNION ALL, unless you want to incur the additional overhead of removing duplicates.

SELECT TraderAccomodationPaymentNo as PaymentNo, 
       AmountPaid as PaidAmount,
       DateOfPayment as DatePaid, 
       TraderAccomodationInvoiceNo as InvoiceNo, 
       PaymentMethod as PaymentType
FROM TraderAccomodationPayment
WHERE AmountPaid IS NULL
UNION ALL
SELECT TraderSeminarPaymentNo as PaymentNo,
       AmountPaid as PaidAmount,
       DateOfPayment as DatePaid, 
       TraderSeminarInvoiceNo as InvoiceNo,
       PaymentMethod as PaymentType
FROM TraderSeminarPayment
WHERE AmountPaid IS NULL
UNION ALL
SELECT AttendeeAccommodationPaymentNo as PaymentNo,
       AmountPaid as PaidAmount,
       DateOfPayment as DatePaid, 
       AttendeeAccommodationInvoiceNo as InvoiceNo,
       PaymentMethod as PaymentType
FROM AttendeeAccomodationPayment
WHERE AmountPaid IS NULL
UNION ALL
SELECT AttendeeSeminarPaymentNo as PaymentNo, 
       AmountPaid as PaidAmount,
       DateOfPayment as DatePaid, 
       AttendeeSeminarInvoiceNo as InvoiceNo, 
       PaymentMethod as PaymentType
FROM AttendeeSeminarPayment
WHERE AmountPaid IS NULL;

The square braces are also superfluous.