UNION with queries: Run Time Error 3021

[simon03]

Hi

I have two queries and I'd like to join/merge the results of these two queries. I have tried a simple case as follows and I have no problems:

strSQL1 = "select name from table Where name='940212'"

strSQL2 = "select name from table Where name='230104'"

strSQL1 = strSQL1 & " UNION ALL " & strSQL2

Debug.Print strSQL1

Set qdf = db.CreateQueryDef("Query_output", strSQL1)

DoCmd.OpenQuery "Query_output"

This code returns all records where name is equal to '940212' and concatenates right after those with name = '230104'

In reality my goal is to concatenate two queries that are a bit more complicated:

strSQL1 = "SELECT table.name, Year(table.Month) AS YEAR, round(table.Quarter,2) AS Performance " & _
" FROM  table WHERE (Month(table.Month) IN (12))"


strSQL2 = " SELECT table.name, Year(table.Month) AS YEAR, round(table.Quarter,2) AS Performance " & _
" FROM table INNER JOIN (SELECT table.name, MAX(table.month) AS MaxOfReportingDate FROM table GROUP BY name) " & _
" AS a ON table.name = a.name AND table.month = a.MaxOfReportingDate ORDER BY table.month DESC"

If I use these two queries with the UNION Statement, I receive Run-Time Error 3021: No Current Record.

If I run them separately they work fine.

Could someone suggest me what is creating the Error 3021?

Thanks

 

[vbaInet]

When do you get the error? When you open the query, when you open it in a recordset, when you open a form bound to it?

 

[simon03]

the Query/ies run when I push a button in a form which runs a VBA code

 

[vbaInet]

Two things to try:

1. Remove the subquery and re-test it.
2. Change the SQL of an existing querydef instead of creating a querydef from scratch.

 

[simon03]

I tried a 3rd option.

I have inverted the combination of the two queries:

strSQL1 = strSQL2 & " UNION ALL " & strSQL1

This solves my initial problem but I now have a new problem.

I'd like to sort the queries' result by name and month. So I used this code:

strSQL1 = strSQL2 & " UNION ALL (" & strSQL1 & ") ORDER by table.name, table.month"

I tried with and without parenthesis. And again I get Run-time Error 3021 :banghead:

I need to order the result otherwise it is a pain to search trough the query the values of interest.

Thanks

 

[vbaInet]

Did you even try what I suggested?

 

[simon03]

I tried. If I remove the subquery it runs well.

For removing the subquery I simply set the second query as:

 strSQL2 = " SELECT table.name, Year(table.Month) AS YEAR, round(table.Quarter,2) AS Performance " & _
" FROM table"

For the second suggestion, unfortunately I do not have other queries created in the subroutine connected to the button. Is this what you mean with your second suggestion? or you want me to run the query with UNION in a query object in Access?

Thanks

 

[simon03]

Just an extra information. With the option:

 strSQL1 = strSQL2 & " UNION ALL " & strSQL1

If I manually sort by name and then by month the query's result (with UNION), I correctly visualise the results as I like.

Maybe I can just use this approach (although not very elegant)

 

[vbaInet]

I can't remember if a subquery is a problem in Union statements because it's been a while I did all this stuff, that's why I asked that you test it.

Alias the table name in the subquery itself.
Best thing for you to do really is to test this out in the Query Designer before even transferring it to code.

I'm not even sure why this is being built in code in the first place?

 

[vbaInet]

Forgot to answer your second question about using an already saved query. Here's what I meant:

dim qdf as dao.querydef

set qdf = currentdb.querydefs("[COLOR="blue"]queryname[/COLOR]")

qdf.SQL = [COLOR="Blue"]strSQL[/COLOR]

set qdf = nothing

 

[simon03]

You're right. I should have tested my Query before.

I tested it outside the VBA environment and then tested again inside the VBA environment.

What I found out is that the problem is solved if I create the query with the UNION statement inside a single string instead of concatenating two. This code now works:

strSQL1 = "SELECT table.name, Year(table.Month) AS YEAR, round(table.Quarter,2) AS Performance " & _
" FROM table  WHERE (Month(table.Month) IN (12) ) " & _
" UNION ALL (SELECT table.name, Year(table.Month) AS YEAR, round(table.Quarter,2) AS Performance " & _
" FROM table INNER JOIN (SELECT table.name, MAX(table.month) AS MaxOfReportingDate FROM table GROUP BY name) AS a ON table.name = a.name AND table.month = a.MaxOfReportingDate ORDER BY table.month DESC  ) " & _
" ORDER by table.name,YEAR"

The point of using the VBA is anyway because the subroutine is running some update tables before (I am following the suggestion received here in this forum a few days ago) and also because in the form I can select some filter conditions that I'd like to include in the query (WHERE conditions with names or range of dates etc.)

In fact now, when I try to concatenate the string above with some text coming from the form, I again receive errors.

Thanks

 

[simon03]

It looks like that the suggestion in post #10 does the trick. Not getting errors anymore

Thanks

 

[vbaInet]

You can form SQL strings in multiple parts. The main thing is how you form it, i.e. do you give enough spacing, are you using the right syntax.

Good to hear you got it working!