Solved

Union All Query Doesn't work all of a sudden

Posted on 2012-04-04
9
469 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
  • 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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 - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

773 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