Problem with date parameters in union query

[Guest]

Hi,

I keep getting an error message when I try to run the following union query.
The problem, apparently, is with the final line of each query. What have I
done wrong?:

SELECT tblParty.PartyName, tblAgreement.AgName, tblAgreement.EffectiveDate,
tblAgreement.InitialTermExp, tblAmendment.AmendmentNumber,
tblAmendment.EffDate

FROM (tblParty INNER JOIN tblAgreement ON tblParty.PartyID =
tblAgreement.PartyID) LEFT JOIN tblAmendment ON tblAgreement.AgID =
tblAmendment.AgID

GROUP BY tblParty.PartyName, tblAgreement.AgName,
tblAgreement.EffectiveDate, tblAgreement.InitialTermExp,
tblAmendment.AmendmentNumber, tblAmendment.EffDate,
tblAgreement.[Expired/Terminated]

WHERE ([EffectiveDate]) >= #4/01/2005#

UNION SELECT tblParty.PartyName, tblAgreement.AgName,
tblAgreement.EffectiveDate, tblAgreement.InitialTermExp,
tblAmendment.AmendmentNumber, tblAmendment.EffDate

FROM (tblParty INNER JOIN tblAgreement ON tblParty.PartyID =
tblAgreement.PartyID) INNER JOIN tblAmendment ON tblAgreement.AgID =
tblAmendment.AgID

WHERE ([EffDate]) >= #4/01/2005#

 

[Tom Ellison]

Dear Laura:

The queries you show include 4 clauses: SELECT, FROM, GROUP BY, and WHERE.

They must be in a specific order: SELECT, FROM, WHERE, and GROUP BY.

Just change this order. If that's the only thing you have wrong, it may
then work. I didn't study to find any other possible problems. If it gives
you another error message, please post again.

Tom Ellison

 

[Guest]

Thanks, Tom. I'll change the order of the clauses.
Just one more thing: could you check the "WHERE" line for me and make sure
that the date-related expression is correct?

Dear Laura:

The queries you show include 4 clauses: SELECT, FROM, GROUP BY, and WHERE.

They must be in a specific order: SELECT, FROM, WHERE, and GROUP BY.

Just change this order. If that's the only thing you have wrong, it may
then work. I didn't study to find any other possible problems. If it gives
you another error message, please post again.

Tom Ellison

Hi,

I keep getting an error message when I try to run the following union
query.
The problem, apparently, is with the final line of each query. What have I
done wrong?:

SELECT tblParty.PartyName, tblAgreement.AgName,
tblAgreement.EffectiveDate,
tblAgreement.InitialTermExp, tblAmendment.AmendmentNumber,
tblAmendment.EffDate

FROM (tblParty INNER JOIN tblAgreement ON tblParty.PartyID =
tblAgreement.PartyID) LEFT JOIN tblAmendment ON tblAgreement.AgID =
tblAmendment.AgID

GROUP BY tblParty.PartyName, tblAgreement.AgName,
tblAgreement.EffectiveDate, tblAgreement.InitialTermExp,
tblAmendment.AmendmentNumber, tblAmendment.EffDate,
tblAgreement.[Expired/Terminated]

WHERE ([EffectiveDate]) >= #4/01/2005#

UNION SELECT tblParty.PartyName, tblAgreement.AgName,
tblAgreement.EffectiveDate, tblAgreement.InitialTermExp,
tblAmendment.AmendmentNumber, tblAmendment.EffDate

FROM (tblParty INNER JOIN tblAgreement ON tblParty.PartyID =
tblAgreement.PartyID) INNER JOIN tblAmendment ON tblAgreement.AgID =
tblAmendment.AgID

WHERE ([EffDate]) >= #4/01/2005#

 

[Tom Ellison]

Dear Laura:

It looks "reasonable". I cannot pass on every aspect of correctness, but it
is pretty simple, and seems to be stated as you may have intended.

Tom Ellison

Thanks, Tom. I'll change the order of the clauses.
Just one more thing: could you check the "WHERE" line for me and make sure
that the date-related expression is correct?

Dear Laura:

The queries you show include 4 clauses: SELECT, FROM, GROUP BY, and
WHERE.

They must be in a specific order: SELECT, FROM, WHERE, and GROUP BY.

Just change this order. If that's the only thing you have wrong, it may
then work. I didn't study to find any other possible problems. If it
gives
you another error message, please post again.

Tom Ellison

Hi,

I keep getting an error message when I try to run the following union
query.
The problem, apparently, is with the final line of each query. What
have I
done wrong?:

SELECT tblParty.PartyName, tblAgreement.AgName,
tblAgreement.EffectiveDate,
tblAgreement.InitialTermExp, tblAmendment.AmendmentNumber,
tblAmendment.EffDate

FROM (tblParty INNER JOIN tblAgreement ON tblParty.PartyID =
tblAgreement.PartyID) LEFT JOIN tblAmendment ON tblAgreement.AgID =
tblAmendment.AgID

GROUP BY tblParty.PartyName, tblAgreement.AgName,
tblAgreement.EffectiveDate, tblAgreement.InitialTermExp,
tblAmendment.AmendmentNumber, tblAmendment.EffDate,
tblAgreement.[Expired/Terminated]

WHERE ([EffectiveDate]) >= #4/01/2005#

UNION SELECT tblParty.PartyName, tblAgreement.AgName,
tblAgreement.EffectiveDate, tblAgreement.InitialTermExp,
tblAmendment.AmendmentNumber, tblAmendment.EffDate

FROM (tblParty INNER JOIN tblAgreement ON tblParty.PartyID =
tblAgreement.PartyID) INNER JOIN tblAmendment ON tblAgreement.AgID =
tblAmendment.AgID

WHERE ([EffDate]) >= #4/01/2005#