UNION ALL in vba

F

foxy

Registered User.
Local time
Today, 14:24
Joined
Feb 17, 2009
Messages
64
Hi All,

I am trying to use the UNION ALL syntax in a vba DoCmd.RunSQL function.

The following syntax works fine if I use it in a query design window, but I need the code to be in vba. When I run the following code I get an error that says "Syntax error (missing operator) in query expression".

Code: 
DoCmd.RunSQL "INSERT INTO tblTemp_CloseSummScores_Parent (cypd_per_id, caf_id, close_date, question, improvement) " & _
            "SELECT tblChild.cypd_per_id, tblClosure.caf_id, tblClosure.close_date, '1. Health',summ_par_score_a_then-summ_par_score_a_now " & _
            "FROM tblChild INNER JOIN (tblCAF INNER JOIN tblClosure ON tblCAF.caf_ID = tblClosure.caf_id) ON tblChild.child_ID = tblCAF.child_id " & _
            "WHERE tblClosure.summ_par_score_a_then IS NOT NULL OR tblClosure.summ_par_score_b_then IS NOT NULL OR tblClosure.summ_par_score_c_then IS NOT NULL " & _
            "OR tblClosure.summ_par_score_d_then IS NOT NULL OR tblClosure.summ_par_score_e_then IS NOT NULL OR tblClosure.summ_par_score_f_then IS NOT NULL " & _
            "OR tblClosure.summ_par_score_a_now IS NOT NULL OR tblClosure.summ_par_score_b_now IS NOT NULL OR tblClosure.summ_par_score_c_now IS NOT NULL " & _
            "OR tblClosure.summ_par_score_d_now IS NOT NULL OR tblClosure.summ_par_score_e_now IS NOT NULL OR tblClosure.summ_par_score_f_now IS NOT NULL " & _
            "OR tblClosure.summ_chil_score_a_then IS NOT NULL OR tblClosure.summ_chil_score_b_then IS NOT NULL OR tblClosure.summ_chil_score_c_then IS NOT NULL " & _
            "OR tblClosure.summ_chil_score_d_then IS NOT NULL OR tblClosure.summ_chil_score_e_then IS NOT NULL OR tblClosure.summ_chil_score_f_then IS NOT NULL " & _
            "OR tblClosure.summ_chil_score_g_then IS NOT NULL OR tblClosure.summ_chil_score_a_now IS NOT NULL OR tblClosure.summ_chil_score_b_now IS NOT NULL " & _
            "OR tblClosure.summ_chil_score_c_now IS NOT NULL OR tblClosure.summ_chil_score_d_now IS NOT NULL OR tblClosure.summ_chil_score_e_now IS NOT NULL " & _
            "OR tblClosure.summ_chil_score_f_now IS NOT NULL OR tblClosure.summ_chil_score_g_now IS NOT NULL " & _
            "UNION ALL "& _
            "SELECT tblChild.cypd_per_id, tblClosure.caf_id, tblClosure.close_date, '2. Safety',summ_par_score_b_then-summ_par_score_b_now " & _
            "FROM tblChild INNER JOIN (tblCAF INNER JOIN tblClosure ON tblCAF.caf_ID = tblClosure.caf_id) ON tblChild.child_ID = tblCAF.child_id " & _
            "WHERE tblClosure.summ_par_score_a_then IS NOT NULL OR tblClosure.summ_par_score_b_then IS NOT NULL OR tblClosure.summ_par_score_c_then IS NOT NULL " & _
            "OR tblClosure.summ_par_score_d_then IS NOT NULL OR tblClosure.summ_par_score_e_then IS NOT NULL OR tblClosure.summ_par_score_f_then IS NOT NULL " & _
            "OR tblClosure.summ_par_score_a_now IS NOT NULL OR tblClosure.summ_par_score_b_now IS NOT NULL OR tblClosure.summ_par_score_c_now IS NOT NULL " & _
            "OR tblClosure.summ_par_score_d_now IS NOT NULL OR tblClosure.summ_par_score_e_now IS NOT NULL OR tblClosure.summ_par_score_f_now IS NOT NULL " & _
            "OR tblClosure.summ_chil_score_a_then IS NOT NULL OR tblClosure.summ_chil_score_b_then IS NOT NULL OR tblClosure.summ_chil_score_c_then IS NOT NULL " & _
            "OR tblClosure.summ_chil_score_d_then IS NOT NULL OR tblClosure.summ_chil_score_e_then IS NOT NULL OR tblClosure.summ_chil_score_f_then IS NOT NULL " & _
            "OR tblClosure.summ_chil_score_g_then IS NOT NULL OR tblClosure.summ_chil_score_a_now IS NOT NULL OR tblClosure.summ_chil_score_b_now IS NOT NULL " & _
            "OR tblClosure.summ_chil_score_c_now IS NOT NULL OR tblClosure.summ_chil_score_d_now IS NOT NULL OR tblClosure.summ_chil_score_e_now IS NOT NULL " & _
            "OR tblClosure.summ_chil_score_f_now IS NOT NULL OR tblClosure.summ_chil_score_g_now IS NOT NULL"

Any clues?

Cheers

Foxy
 
Second line, right at the end, you've got a - instead of a _, I think that means it's trying to subtract one from the other? If that is the table name enclose in []. If you are subtracting I think you have to tell it it's an expression, so put "AS fieldname" afterwards.... I think, anyway
 
Hi James,

Yea that is what is supposed to be happening. either side of the "-" are two different fields. I am subtracting one from the other and the result is going into the 'improvement' field.

The query seems to work if I take the bottom half off, but errors when I make it a UNION ALL join.

Can access 2007 not handle union all?
 
As far as I know, but I don't have much use for them myself. So if you put
Code: 
[summ_par_score_a_then]-[summ_par_score_a_now] AS fieldname
Does it make any difference?
 
Probably just a case of to many line continuations ( & _ )
Try this type of thing instead:
Code: 
Dim MySQL as string 
MySQL = ""
MySQL = MySQL & "INSERT INTO tblTemp_CloseSummScores_Parent (cypd_per_id, caf_id, close_date, question, improvement) " 
MySQL = MySQL & "SELECT tblChild.cypd_per_id, tblClosure.caf_id, tblClosure.close_date, '1. Health',summ_par_score_a_then-summ_par_score_a_now " 
MySQL = MySQL & "FROM tblChild INNER JOIN (tblCAF INNER JOIN tblClosure ON tblCAF.caf_ID = tblClosure.caf_id) ON tblChild.child_ID = tblCAF.child_id "
MySQL = MySQL & "WHERE tblClosure.summ_par_score
Etc....

Also do yourself a BIG pleasure and make a readable query... right now it is just a jumble of meaningless words
 
I didn't realise there was a limit on those... it is a big query though! Is it nothing to do with the calculated field on line 2 not having an alias?
 
James - Yea I still get the same error if I use that approach.

namilam - I didnt realise there was a limit on line continuations, do you know what it is?


Foxy
 
That's me out of ideas then... have a fun one! Sorry!
 
Right...

I've now got my query as below, to avoid using too many line continuations in one go.

I'm still getting the runtime error message. The error message seems to say that the error is something around the WHERE clause, but I cant see anything wrong with it...

Code: 
Dim MySQL As String
MySQL = ""
MySQL = MySQL & "INSERT INTO tblTemp_CloseSummScores_Parent (caf_id, close_date, question, improvement) " & _
                "SELECT tblClosure.caf_id, tblClosure.close_date, '1. Health', [summ_par_score_a_then]-[summ_par_score_a_now] " & _
                "FROM tblCAF INNER JOIN tblClosure ON tblCAF.caf_ID = tblClosure.caf_id " & _
                "WHERE tblClosure.summ_par_score_a_then IS NOT NULL OR tblClosure.summ_par_score_b_then IS NOT NULL OR tblClosure.summ_par_score_c_then IS NOT NULL " & _
                "OR tblClosure.summ_par_score_d_then IS NOT NULL OR tblClosure.summ_par_score_e_then IS NOT NULL OR tblClosure.summ_par_score_f_then IS NOT NULL " & _
                "OR tblClosure.summ_par_score_a_now IS NOT NULL OR tblClosure.summ_par_score_b_now IS NOT NULL OR tblClosure.summ_par_score_c_now IS NOT NULL " & _
                "OR tblClosure.summ_par_score_d_now IS NOT NULL OR tblClosure.summ_par_score_e_now IS NOT NULL OR tblClosure.summ_par_score_f_now IS NOT NULL " & _
                "OR tblClosure.summ_chil_score_a_then IS NOT NULL OR tblClosure.summ_chil_score_b_then IS NOT NULL OR tblClosure.summ_chil_score_c_then IS NOT NULL " & _
                "OR tblClosure.summ_chil_score_d_then IS NOT NULL OR tblClosure.summ_chil_score_e_then IS NOT NULL OR tblClosure.summ_chil_score_f_then IS NOT NULL " & _
                "OR tblClosure.summ_chil_score_g_then IS NOT NULL OR tblClosure.summ_chil_score_a_now IS NOT NULL OR tblClosure.summ_chil_score_b_now IS NOT NULL " & _
                "OR tblClosure.summ_chil_score_c_now IS NOT NULL OR tblClosure.summ_chil_score_d_now IS NOT NULL OR tblClosure.summ_chil_score_e_now IS NOT NULL " & _
                "OR tblClosure.summ_chil_score_f_now IS NOT NULL OR tblClosure.summ_chil_score_g_now IS NOT NULL "
MySQL = MySQL & "UNION ALL " & _
                "SELECT tblClosure.caf_id, tblClosure.close_date, '2. Safety', [summ_par_score_b_then]-[summ_par_score_b_now] " & _
                "FROM tblCAF INNER JOIN tblClosure ON tblCAF.caf_ID = tblClosure.caf_id " & _
                "WHERE tblClosure.summ_par_score_a_then IS NOT NULL OR tblClosure.summ_par_score_b_then IS NOT NULL OR tblClosure.summ_par_score_c_then IS NOT NULL " & _
                "OR tblClosure.summ_par_score_d_then IS NOT NULL OR tblClosure.summ_par_score_e_then IS NOT NULL OR tblClosure.summ_par_score_f_then IS NOT NULL " & _
                "OR tblClosure.summ_par_score_a_now IS NOT NULL OR tblClosure.summ_par_score_b_now IS NOT NULL OR tblClosure.summ_par_score_c_now IS NOT NULL " & _
                "OR tblClosure.summ_par_score_d_now IS NOT NULL OR tblClosure.summ_par_score_e_now IS NOT NULL OR tblClosure.summ_par_score_f_now IS NOT NULL " & _
                "OR tblClosure.summ_chil_score_a_then IS NOT NULL OR tblClosure.summ_chil_score_b_then IS NOT NULL OR tblClosure.summ_chil_score_c_then IS NOT NULL " & _
                "OR tblClosure.summ_chil_score_d_then IS NOT NULL OR tblClosure.summ_chil_score_e_then IS NOT NULL OR tblClosure.summ_chil_score_f_then IS NOT NULL " & _
                "OR tblClosure.summ_chil_score_g_then IS NOT NULL OR tblClosure.summ_chil_score_a_now IS NOT NULL OR tblClosure.summ_chil_score_b_now IS NOT NULL " & _
                "OR tblClosure.summ_chil_score_c_now IS NOT NULL OR tblClosure.summ_chil_score_d_now IS NOT NULL OR tblClosure.summ_chil_score_e_now IS NOT NULL " & _
                "OR tblClosure.summ_chil_score_f_now IS NOT NULL OR tblClosure.summ_chil_score_g_now IS NOT NULL"
 
DoCmd.RunSQL MySQL

Any ideas?

Foxy
 
boblarson said:
Namliam already gave you the answer but you seem to have either ignored him or not understood.
Click to expand...

Oh ok, is it a case of having to remove all the line continuations? I just thought the aim was to get below the limit... Apologies.

I'll get rid of them all and try again.
 
I've got rid of all the line continuations now and am still getting the syntax error. Is there an error in the SQL that I'm missing?...


Code: 
Dim MySQL As String
MySQL = ""
MySQL = MySQL & "INSERT INTO tblTemp_CloseSummScores_Parent (caf_id, close_date, question, improvement) "
MySQL = MySQL & "SELECT tblClosure.caf_id, tblClosure.close_date, '1. Health', [summ_par_score_a_then]-[summ_par_score_a_now] "
MySQL = MySQL & "FROM tblCAF INNER JOIN tblClosure ON tblCAF.caf_ID = tblClosure.caf_id "
MySQL = MySQL & "WHERE tblClosure.summ_par_score_a_then IS NOT NULL OR tblClosure.summ_par_score_b_then IS NOT NULL OR tblClosure.summ_par_score_c_then IS NOT NULL "
MySQL = MySQL & "OR tblClosure.summ_par_score_d_then IS NOT NULL OR tblClosure.summ_par_score_e_then IS NOT NULL OR tblClosure.summ_par_score_f_then IS NOT NULL "
MySQL = MySQL & "OR tblClosure.summ_par_score_a_now IS NOT NULL OR tblClosure.summ_par_score_b_now IS NOT NULL OR tblClosure.summ_par_score_c_now IS NOT NULL "
MySQL = MySQL & "OR tblClosure.summ_par_score_d_now IS NOT NULL OR tblClosure.summ_par_score_e_now IS NOT NULL OR tblClosure.summ_par_score_f_now IS NOT NULL "
MySQL = MySQL & "OR tblClosure.summ_chil_score_a_then IS NOT NULL OR tblClosure.summ_chil_score_b_then IS NOT NULL OR tblClosure.summ_chil_score_c_then IS NOT NULL "
MySQL = MySQL & "OR tblClosure.summ_chil_score_d_then IS NOT NULL OR tblClosure.summ_chil_score_e_then IS NOT NULL OR tblClosure.summ_chil_score_f_then IS NOT NULL "
MySQL = MySQL & "OR tblClosure.summ_chil_score_g_then IS NOT NULL OR tblClosure.summ_chil_score_a_now IS NOT NULL OR tblClosure.summ_chil_score_b_now IS NOT NULL "
MySQL = MySQL & "OR tblClosure.summ_chil_score_c_now IS NOT NULL OR tblClosure.summ_chil_score_d_now IS NOT NULL OR tblClosure.summ_chil_score_e_now IS NOT NULL "
MySQL = MySQL & "OR tblClosure.summ_chil_score_f_now IS NOT NULL OR tblClosure.summ_chil_score_g_now IS NOT NULL "
MySQL = MySQL & "UNION ALL "
MySQL = MySQL & "SELECT tblClosure.caf_id, tblClosure.close_date, '2. Safety', [summ_par_score_b_then]-[summ_par_score_b_now] "
MySQL = MySQL & "FROM tblCAF INNER JOIN tblClosure ON tblCAF.caf_ID = tblClosure.caf_id "
MySQL = MySQL & "WHERE tblClosure.summ_par_score_a_then IS NOT NULL OR tblClosure.summ_par_score_b_then IS NOT NULL OR tblClosure.summ_par_score_c_then IS NOT NULL "
MySQL = MySQL & "OR tblClosure.summ_par_score_d_then IS NOT NULL OR tblClosure.summ_par_score_e_then IS NOT NULL OR tblClosure.summ_par_score_f_then IS NOT NULL "
MySQL = MySQL & "OR tblClosure.summ_par_score_a_now IS NOT NULL OR tblClosure.summ_par_score_b_now IS NOT NULL OR tblClosure.summ_par_score_c_now IS NOT NULL "
MySQL = MySQL & "OR tblClosure.summ_par_score_d_now IS NOT NULL OR tblClosure.summ_par_score_e_now IS NOT NULL OR tblClosure.summ_par_score_f_now IS NOT NULL "
MySQL = MySQL & "OR tblClosure.summ_chil_score_a_then IS NOT NULL OR tblClosure.summ_chil_score_b_then IS NOT NULL OR tblClosure.summ_chil_score_c_then IS NOT NULL "
MySQL = MySQL & "OR tblClosure.summ_chil_score_d_then IS NOT NULL OR tblClosure.summ_chil_score_e_then IS NOT NULL OR tblClosure.summ_chil_score_f_then IS NOT NULL "
MySQL = MySQL & "OR tblClosure.summ_chil_score_g_then IS NOT NULL OR tblClosure.summ_chil_score_a_now IS NOT NULL OR tblClosure.summ_chil_score_b_now IS NOT NULL "
MySQL = MySQL & "OR tblClosure.summ_chil_score_c_now IS NOT NULL OR tblClosure.summ_chil_score_d_now IS NOT NULL OR tblClosure.summ_chil_score_e_now IS NOT NULL "
MySQL = MySQL & "OR tblClosure.summ_chil_score_f_now IS NOT NULL OR tblClosure.summ_chil_score_g_now IS NOT NULL"
 
DoCmd.RunSQL MySQL
 
The number of characters allowed in the SQL is 32000-something odd, that shouldnt be the problem.

Still not very readable your SQL offhand dont seem anything wrong with it, what is the EXACT error message?
 
The exact error message is...

Run-time error '3075':

Syntax error (missing operator) in query expression
'tblClosure.summ_par_score_a_then IS NOT NULL OR
tblClosure.summ_par_score_b_then IS NOT NULL OR
tblClosure.summ_par_score_c_then IS NOT NULL OR
tblClosure.summ_par_score_d_then IS NOT NULL OR
tblClosure.summ_par_score_e_then IS NOT NULL OR
tblClosure.summ'.

I know the query is a little unreadable, but I am bound by the field names unfortunately, which were not my creation!

If I comment out half of the query, it runs fine and populates the temporary table, it just seems to be when I introduce the UNION ALL part.
 
It almost looks like the interperter is tripping up on a line feed/wrap thing... very strange... If you add spaces in to "allign" tblClosure.summ'. with a proper break point (i.e. Add in 15 spaces just before this tblClosure) does that change it?

I am not doing the whole thing, but my idea of "more readable" would be something like
Code: 
MySQL = MySQL & "INSERT INTO tblTemp_CloseSummScores_Parent (caf_id, close_date, question, improvement) "
MySQL = MySQL & "SELECT tblClosure.caf_id "
MySQL = MySQL & "     , tblClosure.close_date "
MySQL = MySQL & "     , '1. Health' "
MySQL = MySQL & "     , [summ_par_score_a_then]-[summ_par_score_a_now] "
MySQL = MySQL & "FROM            tblCAF  "
MySQL = MySQL & "     INNER JOIN tblClosure ON tblCAF.caf_ID = tblClosure.caf_id "
MySQL = MySQL & "WHERE tblClosure.summ_par_score_a_then IS NOT NULL  "
MySQL = MySQL & "   OR tblClosure.summ_par_score_b_then IS NOT NULL  "
MySQL = MySQL & "   OR tblClosure.summ_par_score_c_then IS NOT NULL  "

Ultimatly, why not run 2 seperate insert queries? that is ultimatly the same?
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom