"Invalid procedure call" in Union query

[seb]

Hello.
I am trying to a union query with three queries.


In the following SQL code, the first two queries run well.

SELECT tblA.ddate, Format(Sqr([qty_hrs]),"Standard") AS rc, Format(Sum([qty]*8),"Standard") AS total_hoursFROM tblA
GROUP BY tblA.ddate


UNION


SELECT tblB.ddate, Format(Sqr([qty_hrs]),"Standard") AS rc, Format(Sum([qty]*8),"Standard") AS total_hours
FROM tblB
GROUP BY tblB.ddate;

However, when I add the third SQL MS Access displays this message:

SELECT tblA.ddate, Format(Sqr([qty_hrs]),"Standard") AS rc, Format(Sum([qty]*8),"Standard") AS total_hoursFROM tblA
GROUP BY tblA.ddate


UNION


SELECT tblB.ddate, Format(Sqr([qty_hrs]),"Standard") AS rc, Format(Sum([qty]*8),"Standard") AS total_hours
FROM tblB
GROUP BY tblB.ddate


UNION


SELECT tblC.date, Format(Sqr([qty_hrs]),"Standard") AS rc, Format(Sum(([hr_end]-[hr_beg])*24),"Standard") AS total_hours
FROM tblC
GROUP BY tblC.ddate;

"Invalid procedure call"

I think my problem is in the "rc" calculation of the third query.

If so, I really appreciate any help to reformulate the "rc" recalculation in the third query.

Cheers

[ranman256]

try building each in its own query,
then the union is:
select * from Qry1
union
select * from Qry2
union
select * from Qry3

[Micron]

In the 3rd you have both tblC.date and tblC.ddate.

[seb]

Thanks for your reply.


I tested your SQL code:

SELECT * FROM Qry1
UNION
SELECT * FROM Qry2

MS Access displays:

Syntax error in FROM clause

I am confused. This is my first time fixing SQL Errors.
I really appreciate your help

[seb]

Thanks. Good catch.
Yes, I already edited to tblC.ddate, but the error still displayed.
Cheers

[orange]

I see no space before the FROM in the TblA line

AS total_hoursFROM tblA

[seb]

Thanks for your reply.
That is a copy paste mistake.

In my SQL code is correct.

Cheers

[GinaWhipp]

What happens if you past the last line into a separate query? Does it run as a SELECT query?

[Micron]

I think my problem is in the "rc" calculation of the third query

That looks exactly the same to me in all 3 parts and you said it works in the first two. After testing, my guess would be that the aggregate function Sum shouldn't be there since it didn't like it when I tested. I'm saying more like

Format(([hr_end]-[hr_beg])*24,"Standard")

yet you say that also works in the first 2. Then I'd have to suspect that the calculation is failing on some records where the data is bogus. I don't think it would be because of Nulls as simple math involving Null should return Null; "" should return "Type Mismatch" error. Perhaps one or more fields has data that won't work with your calculation, but I can only guess because even negative numbers * 24 should work. I don't suppose UNION ALL would help, or could it be that one of your domains isn't a table as your code suggests, but is a query with outer joins?

EDIT: just thought of something else - scr or [hr_end] or [hr_beg] are mis-spelled or don't exist in tblC
OR it should be
SELECT tblC.date, tblC.hr_end, tblC.hr_beg...

It looks like you are trying to use fields that are not part of the SELECT portion of the statement, or they are form fields and you have not referenced the form they are on.

[seb]

Hello.
My three SQL codes are:

QRY01

QRY1SELECT Min(tblA.ddate) AS MínDeddate, Max(tblA.ddate) AS MáxDeddate, Format(Sqr([total_hours]),"Standard") AS rc, Format(Sum([qty]*8),"Standard") AS total_hours
FROM tblA
GROUP BY tblA.ddate;

QRY02

SELECT Min(tblB.ddate) AS MínDeddate, Max(tblB.ddate) AS MáxDeddate, Format(Sqr([total_hours]),"Standard") AS rc, Format(Sum([qty]*8),"Standard") AS total_hoursFROM tblB
GROUP BY tblB.ddate;

QRY03

[SELECT Min(tblC.ddate) AS MínDeddate, Max(tblC.ddate) AS MáxDeddate, Format(Sum(([hr_end]-[hr_beg])*24),"Standard") AS total_hours, Format(Sqr([total_hours]),"Standard") AS rc
FROM tblC
GROUP BY tblC.ddate;[/CODE]

For the UNION qry_010203 SQL works fine

SELECT * FROM Qry01UNION 
SELECT * FROM Qry02
UNION SELECT * Qry03;

I attach my db file
In my Db only the following UNION QUERY are working fine:
qry_010203, qry_0405, qry_0708, qry_1011.

I don't understand why this UNION QUERIES are not working:
qry_040506, qry_070809, qry_101112

MS Access always displays:

"Invalid procedure call"

I am really confused

I really appreciate your help

[GinaWhipp]

Change UNION to UNION ALL

[seb]

Hello GinaWhipp.

UNION ALL relieves my headaches in UNION QUERIES.

Thank you very much indeed.

[Micron]

Umm, GinaWhipp just copied what I already said in post 9

[seb]

Micron.

Yes, I read in post 9:
"I don't suppose UNION ALL would help, or could it be that one of your domains isn't a table as your code suggests, but is a query with outer joins?"

Access had overwhelmed me every time I executed a code and always returned errors. Therefore, I was focused on finding the solution and I admit that I did not try to use UNION ALL. Moreover, this is my first time facing a problem like this and using UNION ALL.

I am really sorry.

[GinaWhipp]

Micron, sorry missed your post, just downloaded sample and saw the issue. UNION ALL returns all the records and without it Access was having a problem because of duplication.