Solved

Union All Query Doesn't work all of a sudden

Posted on 2012-04-04
9
475 Views
Last Modified: 2012-06-21
I had a query that combined two separate queries so that I could have all of the records in one table.  Basically the table is from the quickbooks backend database.  It contains a table for transfers with both the from and to accounts.  I was trying to create a separate record for each side.  In other words if a record had 25k transferred from Account A to Account B, the table would have a record for Account A with -25k and a record for Account B with +25.  It has been working fine for a week now and now it doesn't all of a sudden.  I get the following error:

"Invalid Procedure Call"

Any idea what is going on here?
0
Comment
Question by:BBlu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 75
ID: 37809046
Probably should post the SQL ...

mx
0
 
LVL 7

Expert Comment

by:micropc1
ID: 37809073
It is a probably a problem with one of your UNIONed queries. Try running each one separately to see if that helps you narrow down the issue.
0
 

Author Comment

by:BBlu
ID: 37809081
Okay.  

Here is the code that's hiccuping:
Select TxnNum, AccountNumber, TxnAmt, TxnDate, Type from Q_Transfer_Outs

UNION ALL Select TxnNum, AccountNumber, TxnAmt, TxnDate, Type from Q_Transfer_Ins;

Open in new window


And the two queries that I'm trying to combine, which are both working fine:

SELECT TransferIn.F AS TxnNum, TransferIn.AccountNumber, abs((TransferIn.TxnAmount)) AS TxnAmt, TransferIn.TxnDate, "TransferIn" AS Type
FROM (SELECT F, Left([FACCOUNT],InStr([FACCOUNT]," ·")-1) AS AccountNumber, CDbl([FAMOUNT]) AS TxnAmount, CDate([Fdate]) AS TxnDate, T_TRANSFERS.FCREDIT, T_TRANSFERS.FDEBIT FROM T_TRANSFERS WHERE ((Not (T_TRANSFERS.FACCOUNT) Is Null) AND ((T_TRANSFERS.FCREDIT) Is Null)))  AS TransferIn;

Open in new window


..and

SELECT TransferOut.F AS TxnNum, TransferOut.AccountNumber, TransferOut.TxnAmount AS TxnAmt, TransferOut.TxnDate, "TransferOut" AS Type
FROM (SELECT F, Left([FACCOUNT],InStr([FACCOUNT]," ·")-1) AS AccountNumber, CDbl([FAMOUNT]) AS TxnAmount, CDate([Fdate]) AS TxnDate, T_TRANSFERS.FCREDIT, T_TRANSFERS.FDEBIT FROM T_TRANSFERS WHERE ((Not (T_TRANSFERS.FACCOUNT) Is Null) AND ((T_TRANSFERS.FDEBIT) Is Null)))  AS TransferOut;

Open in new window

0
How To Reduce Deployment Times With Pre-Baked AMIs

Even if we can't include all the files in the base image, we can sometimes include some of the larger files that we would otherwise have to download, and we can also sometimes remove the most time-consuming steps. This can help a lot with reducing deployment times.

 
LVL 7

Accepted Solution

by:
micropc1 earned 275 total points
ID: 37809135
I'm leaning towards a problem somewhere here: Left([FACCOUNT],InStr([FACCOUNT]," ·")-1)

Do you have any instances where FACCOUNT starts with " ."? It's not going to like it if InStr([FACCOUNT]," ·") evaluates to 0.

See this...  http://www.dbforums.com/microsoft-access/1633859-invalid-procedure-call-error.html
0
 
LVL 75
ID: 37809214
BBlu ... try the suggestion micropc1 gave and run each query separately and see which one (or both) blow up ...

mx
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 75 total points
ID: 37809479
As micropc1 is eluding to ... IF ... FACCOUNT does not contain a Space+Dot  (" .") ... then InStr() returns zero, and you end up with

Left ([FACCOUNT], -1) ... which will definitely generate an Illegal Function Call ... So, possibly you have some bogus data for FACCOUNT that has entered the mix ...
I don't see anything else that would generate that specific error.

mx
0
 

Author Comment

by:BBlu
ID: 37809480
Thanks, Guys.  It wasn't that one, but you put me on the right path.  I had null values where I didn't expect them.  I've corrected by filtering those out.  Thanks, Guys.
0
 
LVL 75
ID: 37809491
And I was about to say ... if that field contains Null ... same issue.

mx
0
 

Author Closing Comment

by:BBlu
ID: 37809492
Thanks, again, guys.  As usual, even when I don't get the answer directly, the suggestions lead me down the right path to self discovery, which is actually better.  Thanks.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question