Solved

Union All Query Doesn't work all of a sudden

Posted on 2012-04-04
9
471 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

679 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