ACCESS FEED: [MS_AccessPros] Help in Union query for three tables

Rabu, 08 Januari 2014

[MS_AccessPros] Help in Union query for three tables

Hi All,

I am trying to make a union query from three tables.

My query is working for first two tables, the sql upto here is:

SELECT TransactionDate, ClientCIN, ClientName, NULL As Details, ConsignmentNo, Cartons, Weight, Expenses, NULL As AmountReceived

FROM FreightCharges

WHERE (((FreightCharges.ClientCIN) Like [Enter Client ID]))

UNION ALL

SELECT TransactionDate, ClientCIN, ClientName, Details, NULL As ConsignmentNo, NULL As Cartons, NULL As Weight, Expenses, AmountReceived

FROM MiscAccount

WHERE (((MiscAccount.ClientCIN) Like [Enter Client ID]));

----------- OK UPTO HERE ABOVE--------------------

But when i add sql for third tableand sql becomes as follows:

SELECT TransactionDate, ClientCIN, ClientName, NULL As Details, ConsignmentNo, Cartons, Weight, Expenses, NULL As AmountReceived

FROM FreightCharges

WHERE (((FreightCharges.ClientCIN) Like [Enter Client ID]))

UNION ALL

SELECT TransactionDate, ClientCIN, ClientName, Details, NULL As ConsignmentNo, NULL As Cartons, NULL As Weight, Expenses, AmountReceived

FROM MiscAccount

WHERE (((MiscAccount.ClientCIN) Like [Enter Client ID]))

UNION ALL

SELECT TransactionDate, ClientCIN, ClientName, Details, NULL , NULL , NULL , NULL , AmountReceived

FROM PaymentReceived

WHERE (((PaymentReceived.ClientCIN) Like [Enter Client ID]));

Here after entering Client ID, i get error message "Data Type mismatch" I have checked many times Data Type for third table and it is same as first two tables.

Second thing which i tried is for third table i removed "ALL" from the "UNION ALL" then it gives an error message:

"This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try

simplifying the expression by assigning parts of expression to variables."

Structure of Fields is:

TransactionDate = Date/Time, Short Date

ClientCIN = Number, Double

ClientName = Text, 50

ConsignmentNo = Text, 9, Input mask: 9999\-A\-99;0;-

Cartons = Number, Double, Standard, 0

Weight = Number, Double, Standard, 2

Expenses = Number, Double, Standard, 0

Details = Text, 100

AmoundReceived = Number, Double, Standard, 0

Need help where i am wrong or what to do.