union qerys

[Simon]

can any one see whats wrong this this union query or tell me what could
be wrong

SELECT qryLedgerInvoicesWS.[InvoiceNumber] AS Number,
qryLedgerInvoicesWS.[InvoiceDate] AS Date,
qryLedgerInvoicesWS.[Total] AS Debit,
'qryLedgerInvoicesWS' AS TheType
FROM qryLedgerInvoicesWS

UNION ALL SELECT qryLedgerPaymentsWS.[InvoiceNumber] AS Number,
qryLedgerPaymentsWS.[Date] AS Date,
qryLedgerPaymentsWS.[Amount] AS Debit,
'qryLedgerPaymentsWS' AS TheType
FROM qryLedgerPaymentsWS

ORDER BY Date;

 

[Smartin]

can any one see whats wrong this this union query or tell me what could
be wrong

SELECT qryLedgerInvoicesWS.[InvoiceNumber] AS Number,
qryLedgerInvoicesWS.[InvoiceDate] AS Date,
qryLedgerInvoicesWS.[Total] AS Debit,
'qryLedgerInvoicesWS' AS TheType
FROM qryLedgerInvoicesWS

UNION ALL SELECT qryLedgerPaymentsWS.[InvoiceNumber] AS Number,
qryLedgerPaymentsWS.[Date] AS Date,
qryLedgerPaymentsWS.[Amount] AS Debit,
'qryLedgerPaymentsWS' AS TheType
FROM qryLedgerPaymentsWS

ORDER BY Date;

Would you tell us what is wrong with it? I.e., what does it do or not do
apart from your expectations?

One thing I would change... "Number" and "Date" are reserved words.
Reserved, that is, for Access. Best not to use those labels.

 

[Larry Linson]

Please clarify: what problem are you encountering?

Today just isn't a good day for me to play guessing games.

Just one comment... "Date" is a reserved word and not a good choice for a
Field Name or an Alias. I believe "Number" is also a reserved word, but less
likely to be troublesome than "Date" which is a built-in function returning
today's date and likely to be misinterpreted by Access.

Larry Linson
Microsoft Access MVP

 

[Van T. Dinh]

The problem is that both DATE and NUMBER are reserved words (synonym to
DATETIME and FLOAT respectively).

Use different Aliases or if you still want to use DATE and NUMBER (***not***
recommended), use square brackets in all instances like:

AS [NUMBER]

AS [DATE]

BTW, you don't need Aliases in the second part of the Union ... They are
simply ignored (but can give errors) so don't use them.