Union query not working, but why??

[Guest]

This is so simple that im not seeing it! it has to be!!
I have a form which fills a list box with the results of a union query, this
query searches a table called credit by either the banking date (which works
fine) or the the slipNo (which doesnt work). When i do a query to test if i
can search by slipNo (below) it works great:

SELECT credit.*
FROM credit
WHERE (((credit.SlipNo)="L136/1804"));

below is the Union query segment which is causing me problems:

UNION SELECT credit.transactDate, receipt.receiptNo, Receipt.Student,
receipt.date, credit.Amount, "Credit" AS [Payment Type], Credit.slipNo,
AdminPayment.AmountPaid AS Admin, Utilities.AmountPaid AS Utilities,
TelephoneBills.TelephoneCredit AS Telephone, parkingBill.paymentAmount AS
Parking, RentPayment.AmountPayed AS Rent, InternetBilling.Total AS Internet,
InvoicePayment.amountPaid AS Invoice, DepositPayment.[Deposit payment] AS
Deposit, OtherPayments.paymentAmount AS Other
FROM (Students INNER JOIN (((((((((Receipt LEFT JOIN AdminPayment ON
Receipt.ReceiptNo = AdminPayment.ReceiptNo) LEFT JOIN DepositPayment ON
Receipt.ReceiptNo = DepositPayment.receiptNo) LEFT JOIN InternetBilling ON
Receipt.ReceiptNo = InternetBilling.ReceiptNo) LEFT JOIN InvoicePayment ON
Receipt.ReceiptNo = InvoicePayment.receiptNo) LEFT JOIN OtherPayments ON
Receipt.ReceiptNo = OtherPayments.ReceiptNo) LEFT JOIN parkingBill ON
Receipt.ReceiptNo = parkingBill.ReceiptNo) LEFT JOIN RentPayment ON
Receipt.ReceiptNo = RentPayment.ReceiptNo) LEFT JOIN TelephoneBills ON
Receipt.ReceiptNo = TelephoneBills.ReceiptNo) LEFT JOIN Utilities ON
Receipt.ReceiptNo = Utilities.receiptNo) ON Students.StudentRef =
Receipt.Student) INNER JOIN credit ON Receipt.ReceiptNo = credit.ReceiptNo
WHERE (credit.slipNo = forms![bankingreport]![slipNo]) or
(((credit.transactDate)=forms![BankingReport]![BankDate]) AND
((Students.Year)=2006))

when i run the Union query on its on the textbox on the form gives me no
results! The union query looks to compare either the slipNo or the bankdate
of various payments across different payment type tables (cheques, credit
etc), the bankdate part works great and for other payments the slipNo
comparison works fine too. The major difference is that other payments
slipNo's dont have a "/" in them.

I think that when i run it from a form there arent any "" around the slipNo
and this is throwing it cos its a text data type.

Any ideas on what i can do and why im not having this problem when i use
other text field based queries?

 

[Guest]

hello again, i solved it when i realised that the union query checks the
value in the text box against the slipNo value in each table and if the data
types are incopatible (comparing a text value against a numeric value) then
you get an error message. After changing the data types to text in all the
tables that are queried i was able to run this query properly.