Union queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following union query and it works perfectly in my labtop. However, when I tried to run it from my desktop I got those weird looking characters. When I run a report based on this query, I got "Run-time error: '3071'". I even compared the version of Ms Access on my labtop and desktop and they're of the same version. Any advices would be greatly appreciated. Thanks.

SELECT distinct tbl_audits.branch, tbl_audits.client, Count(tbl_audits.dlsop) AS no_dlsops, null as no_bill_sch, null as no_ach, null as duty_outlay, null as duty_money_cost, null as freight_payout, null as freight_money_cost, null as outside_forwarde
FROM tbl_audit
WHERE tbl_audits.dlsop="0"
GROUP BY tbl_audits.branch, tbl_audits.client, tbl_audits.dlso

UNION SELECT distinct tbl_audits.branch, tbl_audits.client, null as no_dlsops, Count(tbl_audits.bill_schedule) AS no_bill_sch, null as no_ach, null as duty_outlay, null as duty_money_cost, null as freight_payout, null as freight_money_cost, null as outside_forwarde
FROM tbl_audit
WHERE tbl_audits.bill_schedule = "No"
GROUP BY tbl_audits.branch, tbl_audits.client, tbl_audits.bill_schedul

UNION SELECT distinct [tbl_audits].[branch], [tbl_audits].[client], null as no_dlsops, null as no_bill_sch, Count([tbl_audits].[ach]) AS no_ach, Sum([tbl_audits].[ach_actual_duty]) AS duty_outlay, Sum([tbl_audits].[ach_cost_money]) AS duty_money_cost, null as freight_payout, null as freight_money_cost, null as outside_forwarde
FROM tbl_audit
WHERE [tbl_audits].[ach]="No"
GROUP BY [tbl_audits].[branch], [tbl_audits].[client], [tbl_audits].[ach];
 
Hi

Here's the exact error message:

"This expression is typed incorrectly, or it's too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

Thanks

----- Ivar Svendsen wrote: ----

Hello

Please include the exact text appearing in the "Runtime error 3071" dialog box.
 
Hi.

I would suggest that you break the union query into its three subqueries, to find out which one is causing the trouble. When you find the subquery that does not work, you will need to check the types of the fields.

It may be in a WHERE clause, where you might need to replace tbl_audits.dlsop="0" with tbl_audits.dlsop=0 and tbl_audits.bill_schedule = "No" with tbl_audits.bill_schedule = False
 
I think I'll do that.

The strange thing that doesn't make sense to me is that it works when running in labtop but not if running in desktop. I don't know why that's happening

Thanks for your advice.
 
Back
Top