Select query doesn't work in union query (1 Viewer)

R

Reese

Registered User.
Local time
Today, 16:56
Joined
Jan 13, 2013
Messages
387
[Solved] Select query doesn't work in union query

I created a rather lengthy union select query that works--except one section, which pulls records that should be ignored based on the criteria (whether a record labeled as "WE" is paid or unpaid). All other sections of the union query--regarding unpaid records labeled in ways other than "WE"--pull records according to similar criteria perfectly.

When I copy/paste the relevant section of the sql into a new query, the selection query works perfectly--limiting the records pulled appropriately. Within the union query, however, it pulls all of the records labeled as "WE", not just the unpaid ones.

Here is the relevant sql (when removed from the union; in the union it's the exact same but with UNION added in front of SELECT):
Code: 
SELECT [Event Information].Event_ID, [Event Information].Invoice_Date, [Event Information].Unpaid_Contact_Date, [Event Information].Date_of_Event, [Event Information].Unpaid_Contact_Notes, [Event Information].Program_Code, [Client Information].Organization, [Client Information].Full_Name, [Event Information].Payment_Notes, [Event Information].Paid_By_PO, [Event Information].Deposit_Paid, [Event Information].Multiple_On_Billing
FROM [Client Information] INNER JOIN [Event Information] ON [Client Information].Client_ID = [Event Information].Client_ID
WHERE ((([Event Information].Invoice_Date)<DateAdd("d",-5,Date())) AND (([Event Information].Program_Code)="WE") AND (([Event Information].Incomplete_Booking) Is Null Or ([Event Information].Incomplete_Booking)="0") AND (([Event Information].Paid)="0") AND (([Event Information].Cost_Category)="Full Price" Or ([Event Information].Cost_Category)="Discount") AND (([Event Information].Canceled) Is Null Or ([Event Information].Canceled)="0"))
ORDER BY [Event Information].Invoice_Date, [Event Information].Unpaid_Contact_Date, [Event Information].Date_of_Event;
Any ideas? Thanks.
 
Last edited:
N

namliam

The Mailman - AWF VIP
Local time
Today, 22:56
Joined
Aug 11, 2003
Messages
11,695
Thanks for using the code tags :), next time please also make the SQL readable.
Code: 
SELECT [Event Information].Event_ID, [Event Information].Invoice_Date, [Event Information].Unpaid_Contact_Date, [Event Information].Date_of_Event, [Event Information].Unpaid_Contact_Notes, [Event Information].Program_Code, [Client Information].Organization, [Client Information].Full_Name, [Event Information].Payment_Notes, [Event Information].Paid_By_PO, [Event Information].Deposit_Paid, [Event Information].Multiple_On_Billing
FROM       [Client Information] 
INNER JOIN [Event Information] ON [Client Information].Client_ID = [Event Information].Client_ID
WHERE   [Event Information].Invoice_Date<DateAdd("d",-5,Date()) 
   AND  [Event Information].Program_Code="WE" 
   AND (    [Event Information].Incomplete_Booking Is Null 
         Or [Event Information].Incomplete_Booking="0"
       ) 
   AND [Event Information].Paid="0" 
   AND (    [Event Information].Cost_Category ="Full Price" 
         Or [Event Information].Cost_Category ="Discount"
       ) 
   AND (    [Event Information].Canceled Is Null 
         Or [Event Information].Canceled="0"
       )
ORDER BY [Event Information].Invoice_Date, [Event Information].Unpaid_Contact_Date, [Event Information].Date_of_Event;
There that helps a lot, regardless if this query works properly there is no reason it shouldnt work in the UNION, (UNION or UNION ALL?)

You will need to post the full UNION query, I think, becuase the problem will be in there most likely with one of the other queries involved in the UNION query.
 
R

Reese

Registered User.
Local time
Today, 16:56
Joined
Jan 13, 2013
Messages
387
Okay, so after looking at things some more, I found that part of the issue is with the Cost_Category="Discount". The two "WE" are the only two records that are Discounts.

I changed one to "Full Price" in the table, re-ran the Union query, and only one the one that is still "Discount" came up. (Both are paid, by the way, which is why they shouldn't be showing up in the query--if it was working right.)

It still doesn't explain why the sql works when taken out and used as a single query but not when included in the union query.

Anyway, here's the entire union query's sql. The section that isn't working is at the last Union Select.
Code: 
SELECT [Event Information].Event_ID, [Event Information].Invoice_Date, [Event Information].Unpaid_Contact_Date, 

[Event Information].Date_of_Event, [Event Information].Unpaid_Contact_Notes, [Event Information].Program_Code, 

[Client Information].Organization, [Client Information].Full_Name, [Event Information].Payment_Notes, [Event 

Information].Paid_By_PO, [Event Information].Deposit_Paid, [Event Information].Multiple_On_Billing
FROM [Client Information]
INNER JOIN [Event Information] ON [Client Information].Client_ID = [Event Information].Client_ID
WHERE     (    (([Event Information].Invoice_Date)<DateAdd("d",-30,Date())    )
    AND (([Event Information].Program_Code)="ZM")
    AND (    ([Event Information].Multiple_On_Billing) Is Null
        Or ([Event Information].Multiple_On_Billing)="0"
        )
    AND (    ([Event Information].Sample_Lock) Is Null
        Or ([Event Information].Sample_Lock)="0"
        )
    AND (    ([Event Information].Incomplete_Booking) Is Null
        Or ([Event Information].Incomplete_Booking)="0"
        )
    AND (    ([Event Information].Paid) Is Null
        Or ([Event Information].Paid)="0"
        )
    AND (    ([Event Information].Cost_Category)="Full Price"    )
    AND (    ([Event Information].Canceled) Is Null
        Or ([Event Information].Canceled)="0")
        )
    OR (    (([Event Information].Cost_Category)="Discount")
        )
ORDER BY [Event Information].Invoice_Date, [Event Information].Unpaid_Contact_Date, [Event 

Information].Date_of_Event

UNION SELECT [Event Information].Event_ID, [Event Information].Invoice_Date, [Event 

Information].Unpaid_Contact_Date, [Event Information].Date_of_Event, [Event Information].Unpaid_Contact_Notes, 

[Event Information].Program_Code, [Client Information].Organization, [Client Information].Full_Name, [Event 

Information].Payment_Notes, [Event Information].Paid_By_PO, [Event Information].Deposit_Paid, [Event 

Information].Multiple_On_Billing
FROM [Client Information]
INNER JOIN [Event Information] ON [Client Information].Client_ID = [Event Information].Client_ID
WHERE     (    (([Event Information].Date_of_Event)<DateAdd("d",-5,Date())    )
    AND (    ([Event Information].Program_Code)="GT"    )
    AND (    ([Event Information].Sample_Lock) Is Null
        Or ([Event Information].Sample_Lock)="0"
        )
    AND (    ([Event Information].Incomplete_Booking) Is Null
        Or ([Event Information].Incomplete_Booking)="0"
        )
    AND (    ([Event Information].Paid) Is Null
        Or ([Event Information].Paid)="0"
        )
    AND (    ([Event Information].Cost_Category)="Full Price"    )
    AND (    ([Event Information].Canceled) Is Null
        Or ([Event Information].Canceled)="0")
        )
    OR (    (([Event Information].Cost_Category)="Discount")    )
ORDER BY [Event Information].Invoice_Date, [Event Information].Unpaid_Contact_Date, [Event 

Information].Date_of_Event

UNION SELECT [BOCES Invoice].BOCES_ID, [BOCES Invoice].Date_Sent, [BOCES Invoice].Unpaid_Contact_Date, [BOCES 

Invoice].Date_of_Event_Filler, [BOCES Invoice].Unpaid_Contact_Notes, [BOCES Invoice].Program_Code, [BOCES 

Invoice].Organization_Filler, [BOCES Invoice].Full_Name, [BOCES Invoice].Pay_Notes_Filler, [BOCES 

Invoice].Paid_By_PO, [BOCES Invoice].Deposit_Filler, [BOCES Invoice].Multiple_Billing_Filler
FROM [BOCES Invoice]
WHERE     (    (([BOCES Invoice].Date_Sent)<DateAdd("d",-60,Date())    )
    AND (    ([BOCES Invoice].Paid) Is Null
        Or ([BOCES Invoice].Paid)="0"
        )
    AND (    ([BOCES Invoice].Invoice_Sent)="-1")    )

UNION SELECT [Shared Billing Information].Shared_Billing_ID, [Shared Billing Information].Invoice_Date, [Shared 

Billing Information].Unpaid_Contact_Date, [Shared Billing Information].Date_of_Event, [Shared Billing 

Information].Unpaid_Contact_Notes, [Shared Billing Information].Program_Code, [Client Information].Organization, 

[Client Information].Full_Name, [Shared Billing Information].Payment_Notes, [Shared Billing Information].Paid_By_PO, 

[Shared Billing Information].Deposit_Paid, [Shared Billing Information].Shared_Billing_Invoice
FROM [Client Information]
INNER JOIN [Shared Billing Information] ON [Client Information].Client_ID = [Shared Billing Information].Client_ID
WHERE     (    (([Shared Billing Information].Invoice_Date)<DateAdd("d",-30,Date())    )
    AND (    ([Shared Billing Information].Paid) Is Null
        Or ([Shared Billing Information].Paid)="0"
        )
    AND (    ([Shared Billing Information].Cost_Category)="Full Price"    )
    AND (    ([Shared Billing Information].Canceled) Is Null
        Or ([Shared Billing Information].Canceled)="0"
        )
    AND (    ([Shared Billing Information].Sample_Lock) Is Null
        Or ([Shared Billing Information].Sample_Lock)="0"
        )
    AND (    ([Shared Billing Information].Incomplete_Booking) Is Null
        Or ([Shared Billing Information].Incomplete_Booking)="0")
        )
    OR (    (([Shared Billing Information].Cost_Category)="Discount")    )

UNION SELECT [Event Information].Event_ID, [Event Information].Invoice_Date, [Event 

Information].Unpaid_Contact_Date, [Event Information].Date_of_Event, [Event Information].Unpaid_Contact_Notes, 

[Event Information].Program_Code, [Client Information].Organization, [Client Information].Full_Name, [Event 

Information].Payment_Notes, [Event Information].Paid_By_PO, [Event Information].Deposit_Paid, [Event 

Information].Multiple_On_Billing
FROM [Client Information]
INNER JOIN [Event Information] ON [Client Information].Client_ID = [Event Information].Client_ID
WHERE     (    (([Event Information].Invoice_Date)<DateAdd("d",-5,Date())    )
    AND (    ([Event Information].Program_Code)="BD"    )
    AND (    ([Event Information].Deposit_Paid) Is Null
        Or ([Event Information].Deposit_Paid)="0"
        )
    AND (    ([Event Information].Sample_Lock) Is Null
        Or ([Event Information].Sample_Lock)="0"
        )
    AND (    ([Event Information].Incomplete_Booking) Is Null
        Or ([Event Information].Incomplete_Booking)="0"
        )
    AND (    ([Event Information].BD_ZooMobile_Option)="-1"    )
    AND (    ([Event Information].Cost_Category)="Full Price"    )
    AND (    ([Event Information].Canceled) Is Null
        Or ([Event Information].Canceled)="0")
        )
    OR (    (([Event Information].Cost_Category)="Discount")    )
ORDER BY [Event Information].Invoice_Date, [Event Information].Unpaid_Contact_Date, [Event 

Information].Date_of_Event

UNION SELECT [Event Information].Event_ID, [Event Information].Invoice_Date, [Event 

Information].Unpaid_Contact_Date, [Event Information].Date_of_Event, [Event Information].Unpaid_Contact_Notes, 

[Event Information].Program_Code, [Client Information].Organization, [Client Information].Full_Name, [Event 

Information].Payment_Notes, [Event Information].Paid_By_PO, [Event Information].Deposit_Paid, [Event 

Information].Multiple_On_Billing
FROM [Client Information]
INNER JOIN [Event Information] ON [Client Information].Client_ID = [Event Information].Client_ID
WHERE     (    (([Event Information].Date_of_Event)<DateAdd("d",-5,Date())    )
    AND (    ([Event Information].Program_Code)="BD"    )
    AND (    ([Event Information].Sample_Lock) Is Null
        Or ([Event Information].Sample_Lock)="0"
        )
    AND (    ([Event Information].Incomplete_Booking) Is Null
        Or ([Event Information].Incomplete_Booking)="0"
        )
    AND (    ([Event Information].BD_ZooMobile_Option)="-1"        )
    AND (    ([Event Information].Cost_Category)="Full Price"    )
    AND (    ([Event Information].Canceled) Is Null
        Or ([Event Information].Canceled)="0"
        )
    AND (    ([Event Information].Paid) Is Null
        Or ([Event Information].Paid)="0")
        )
    OR (    (([Event Information].Cost_Category)="Discount")    )
ORDER BY [Event Information].Invoice_Date, [Event Information].Unpaid_Contact_Date, [Event 

Information].Date_of_Event

UNION SELECT [Event Information].Event_ID, [Event Information].Invoice_Date, [Event 

Information].Unpaid_Contact_Date, [Event Information].Date_of_Event, [Event Information].Unpaid_Contact_Notes, 

[Event Information].Program_Code, [Client Information].Organization, [Client Information].Full_Name, [Event 

Information].Payment_Notes, [Event Information].Paid_By_PO, [Event Information].Deposit_Paid, [Event 

Information].Multiple_On_Billing
FROM [Client Information]
INNER JOIN [Event Information] ON [Client Information].Client_ID = [Event Information].Client_ID
WHERE     (    (([Event Information].Date_of_Event)<DateAdd("d",-5,Date())    )
    AND (    ([Event Information].Program_Code)="SC"    )
    AND (    ([Event Information].Incomplete_Booking) Is Null
        Or ([Event Information].Incomplete_Booking)="0"
        )
    AND (    ([Event Information].Paid) Is Null
        Or ([Event Information].Paid)="0"
        )
    AND (    ([Event Information].Cost_Category)="Full Price"    )
    AND (    ([Event Information].Canceled) Is Null
        Or ([Event Information].Canceled)="0")
        )
    OR (    (([Event Information].Cost_Category)="Discount")    )
ORDER BY [Event Information].Invoice_Date, [Event Information].Unpaid_Contact_Date, [Event 

Information].Date_of_Event

UNION SELECT [Event Information].Event_ID, [Event Information].Invoice_Date, [Event 

Information].Unpaid_Contact_Date, [Event Information].Date_of_Event, [Event Information].Unpaid_Contact_Notes, 

[Event Information].Program_Code, [Client Information].Organization, [Client Information].Full_Name, [Event 

Information].Payment_Notes, [Event Information].Paid_By_PO, [Event Information].Deposit_Paid, [Event 

Information].Multiple_On_Billing
FROM [Client Information]
INNER JOIN [Event Information] ON [Client Information].Client_ID = [Event Information].Client_ID
WHERE     (    (([Event Information].Invoice_Date)<DateAdd("d",-5,Date())    )
    AND (    ([Event Information].Program_Code)="WE"    )
    AND (    ([Event Information].Incomplete_Booking) Is Null
        Or ([Event Information].Incomplete_Booking)="0"
        )
    AND (    ([Event Information].Paid)="0"    )
    AND (    ([Event Information].Cost_Category)="Full Price"
        Or ([Event Information].Cost_Category)="Discount"
        )
    AND (    ([Event Information].Canceled) Is Null
        Or ([Event Information].Canceled)="0")
        )
ORDER BY [Event Information].Invoice_Date, [Event Information].Unpaid_Contact_Date, [Event 

Information].Date_of_Event;
 
R

Reese

Registered User.
Local time
Today, 16:56
Joined
Jan 13, 2013
Messages
387
And I just played with it some more and found more out. I changed a "ZM" record that was paid and "Full Price" to "Discount". Now it also shows up in the query, even though it's still recorded as Paid.

So apparently the problem is the "Discount" part throughout the entire query. The thing is, I made each of these as individual queries and copied them each into the Union query. They worked fine as individuals. How is being part of a union query changing it?

The end result is that all overdue records that are either "Full Price" or "Discount" for Cost_Category and that meet all of the other criteria within each individual query are included in the results of the union query.
 
N

namliam

The Mailman - AWF VIP
Local time
Today, 22:56
Joined
Aug 11, 2003
Messages
11,695
Making sql readable isnt just about putting some spaces in.... or hitting return a few times....

Code: 
WHERE  (    [Event Information].Invoice_Date<DateAdd("d",-30,Date())    
    AND     [Event Information].Program_Code ="ZM" 
    AND (   [Event Information].Multiple_On_Billing Is Null
         Or [Event Information].Multiple_On_Billing="0"
        )
    AND (   [Event Information].Sample_Lock Is Null
         Or [Event Information].Sample_Lock="0"
        )
    AND (   [Event Information].Incomplete_Booking Is Null
         Or [Event Information].Incomplete_Booking="0"
        )
    AND (   [Event Information].Paid Is Null
         Or [Event Information].Paid="0"
        )
    AND     [Event Information].Cost_Category="Full Price"  
    AND (   [Event Information].Canceled Is Null
        Or  [Event Information].Canceled="0"
        )
       )
    OR     [Event Information].Cost_Category="Discount"
If you take out all the unneeded brackets you are left with above where clause for your ZM Program_Code... Which if I understand your logic properly is "not quite right".

I hope that gets you going :)
 
jdraw

jdraw

Super Moderator
Staff member
Local time
Today, 16:56
Joined
Jan 23, 2006
Messages
15,403
Also you may wish to describe the intended logic.
 
R

Reese

Registered User.
Local time
Today, 16:56
Joined
Jan 13, 2013
Messages
387
Namlian, I didn't realize that so many of those paranthesis were actually clutter. Like I mentioned I had created each query individually using Design View (it's easier for me), tested it, then simply copied the sql into the Union query.

I'll try to clean it up more and see what happens.

Jdraw, by intended logic did you mean what the end results should be? If so, the end result should pull the following:

-All ZMs in the Event Information table that:

-[Paid]= Is Null or 0
-[Cost_Category]="Full Price" or "Discount"
-[Sample_Lock]=0
-[Invoice_Date]<("d",-30,Date())
-[Incomplete_Booking]=Is Null or 0
-[Canceled]=Is Null or 0

-All GTs in the Event Information table that:

-[Paid]= Is Null or 0
-[Cost_Category]="Full Price" or "Discount"
-[Date_of_Event]<("d",-5,Date())
-[Incomplete_Booking]=Is Null or 0
-[Canceled]=Is Null or 0

-All BDs in the Event Information table that:

-[Deposit_Paid]= Is Null or 0
-[Cost_Category]="Full Price" or "Discount"
-[Invoice_Date]<("d",-5,Date())
-[Incomplete_Booking]=Is Null or 0
-[Canceled]=Is Null or 0

-All BDs in the Event Information table that: (yes, there are 2 sections with BD)

-[Paid]= Is Null or 0
-[Cost_Category]="Full Price" or "Discount"
-[Date_of_Event]<("d",-5,Date())
-[Incomplete_Booking]=Is Null or 0
-[Canceled]=Is Null or 0
-[BD_ZooMobile_Option]="-1"

-All SCs in the Event Information table that:

-[Paid]= Is Null or 0
-[Cost_Category]="Full Price" or "Discount"
-[Date_of_Event]<("d",-5,Date())
-[Incomplete_Booking]=Is Null or 0
-[Canceled]=Is Null or 0

-All WEs in the Event Information table that:

-[Paid]= Is Null or 0
-[Cost_Category]="Full Price" or "Discount"
-[Invoice_Date]<("d",-5,Date())
-[Incomplete_Booking]=Is Null or 0
-[Canceled]=Is Null or 0

-All records in the BOCES Invoice table that:

-[Paid]= Is Null or 0
-[Date_Sent]<("d",-60,Date())
-[Invoice_Sent]="-1"

-All records in the Shared Billing Information table that:

-[Paid]= Is Null or 0
-[Cost_Category]="Full Price" or "Discount"
-[Invoice_Date]<("d",-30,Date())
-[Incomplete_Booking]=Is Null or 0
-[Canceled]=Is Null or 0

In addition, each section needs to pull the ID field, Organization, Unpaid_Contact_Date, Unpaid_Contact_Notes, Full_Name and Paid_By_PO. Plus any "extra" fields so that each query has the same number/types of fields for the union to work.
 
R

Reese

Registered User.
Local time
Today, 16:56
Joined
Jan 13, 2013
Messages
387
I know this is a lot of info for the union query to pull, but it's for a continous form for users to easily find overdue programs, quickly read information about them, then open up a payment management form for any of the records.
 
N

namliam

The Mailman - AWF VIP
Local time
Today, 22:56
Joined
Aug 11, 2003
Messages
11,695
Also have you thought about cleaning things up a little by replacing
Code: 
    AND (   [Event Information].Sample_Lock Is Null
         Or [Event Information].Sample_Lock="0"
        )
by
Code: 
    AND (   NZ([Event Information].Sample_Lock,"0") <> "0"
        )
On top of which is "0" intended or is Sample_Lock a number field and should it simply be 0 instead?

FYI doing order by clauses inside the union is quite useless

I tried redoing your ZM where clause, this time without removing surplus brackets and still come to the same conclusion though with regards to, it doesnt match your logic that you posted above:
Code: 
WHERE  (    
           (
              ([Event Information].Invoice_Date)<DateAdd("d",-30,Date())    
           )
       AND (
              ([Event Information].Program_Code)="ZM"
           )
       AND (    
              ([Event Information].Multiple_On_Billing) Is Null
           Or ([Event Information].Multiple_On_Billing)="0"
           )
       AND (    
              ([Event Information].Sample_Lock) Is Null
           Or ([Event Information].Sample_Lock)="0"
           )
       AND (    
              ([Event Information].Incomplete_Booking) Is Null
           Or ([Event Information].Incomplete_Booking)="0"
           )
       AND (    
              ([Event Information].Paid) Is Null
           Or ([Event Information].Paid)="0"
           )
       AND (    
              ([Event Information].Cost_Category)="Full Price"    
           )
       AND (  ([Event Information].Canceled) Is Null
           Or ([Event Information].Canceled)="0"
           )
       )
       OR (    
              (
               ([Event Information].Cost_Category)="Discount"
              )
           )
You probably want something like:
Code: 
WHERE  (    
           (
              ([Event Information].Invoice_Date)<DateAdd("d",-30,Date())    
           )
       AND (
              ([Event Information].Program_Code)="ZM"
           )
       AND (    
              ([Event Information].Multiple_On_Billing) Is Null
           Or ([Event Information].Multiple_On_Billing)="0"
           )
       AND (    
              ([Event Information].Sample_Lock) Is Null
           Or ([Event Information].Sample_Lock)="0"
           )
       AND (    
              ([Event Information].Incomplete_Booking) Is Null
           Or ([Event Information].Incomplete_Booking)="0"
           )
       AND (    
              ([Event Information].Paid) Is Null
           Or ([Event Information].Paid)="0"
           )
       AND (    
              ([Event Information].Cost_Category)="Full Price"    
[COLOR="Red"]           OR ([Event Information].Cost_Category)="Discount"[/COLOR]
           )  
       AND (  ([Event Information].Canceled) Is Null
           Or ([Event Information].Canceled)="0"
           )
       )
Notice the moved OR in red....
 
R

Reese

Registered User.
Local time
Today, 16:56
Joined
Jan 13, 2013
Messages
387
On top of which is "0" intended or is Sample_Lock a number field and should it simply be 0 instead?
Click to expand...

Sample_Lock is a true/false. In that case, it should be "0", right?

I haven't had time to clean up & re-organize the code yet, once I do I'll let you know the results. Thanks.
 
N

namliam

The Mailman - AWF VIP
Local time
Today, 22:56
Joined
Aug 11, 2003
Messages
11,695
True/False is nothing but a number at the core of it, meaning 0 not "0" or perhaps a more logical False?
 
R

Reese

Registered User.
Local time
Today, 16:56
Joined
Jan 13, 2013
Messages
387
Success!

AND (
([Event Information].Cost_Category)="Full Price"
OR ([Event Information].Cost_Category)="Discount"
)
Click to expand...

That did the trick; I had to replace that in all of the sections though. After some investigating and playing around I discovered why/how this happened.

Like I said, I created each part in Design View. Originally I had put "Discount" in the second line of the criteria instead of including including it in with the first line. The issue didn't show up when they were single queries but they did when merged as a union.

True/False is nothing but a number at the core of it, meaning 0 not "0" or perhaps a more logical False?
Click to expand...

It gave me a data type error when I tried without quotes, so I'm keeping them.

Also, I know understand why--when sharing the code--I should do what you suggested to make it readable, but when trying that I kept messing up and getting errors that I couldn't track down.

Given my skill set right now, it's easier to at least start in Design View and that just causes the sql to be all smushed like that. So while I appreciate your advice, I'm going to continue the way I have been. Thanks.
 
N

namliam

The Mailman - AWF VIP
Local time
Today, 22:56
Joined
Aug 11, 2003
Messages
11,695
I still make queries in design view, I even abuse it to create base Oracle queries instead of typing :)

Getting the SQL that way isnt a problem at all, however if you are running into trouble and/or are using queries in VBA... It really really pays to fix your queries in such a way that you can more easily read it....
Even if just spacing it like I did in my last post, helps a LOT to make it more readable and to keep track of what bracket belongs together with what bracket.

I for one would have NEVER tracked down the problem with that OR without respacing the statement.... PERHAPS if I had taken it and gone into design view.... but that is just another way of making things look readable....
 
R

Reese

Registered User.
Local time
Today, 16:56
Joined
Jan 13, 2013
Messages
387
In order to track down the problem, I actually copied each section into a new query, changed over to Design View, then played around with it. After fixing the problem, I then copied the sql back into the union query.

Thanks again.
 
You must log in or register to reply here.

Similar threads

W
Solved Duplicate Items in Query
Replies
5
Views
225
wmix
W
C
Solved Union query to combine outer join with a WHERE criteria?
Replies
2
Views
404
ebs17
E
K
Solved OrderBy in Union Query
2
Replies
25
Views
1,258
KitaYama
K
J
Solved Hi, stumped with how to build this query
Replies
11
Views
412
Pat Hartman
P
T
How to hide a label on 2nd subreport if 1st subreport has no data
Replies
11
Views
813
CJ_London
C

Users who are viewing this thread

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