Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 486
  • Last Modified:

Union All Query Doesn't work all of a sudden

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
BBlu
Asked:
BBlu
  • 4
  • 3
  • 2
2 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Probably should post the SQL ...

mx
0
 
micropc1Commented:
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
 
BBluAuthor Commented:
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
Technology Partners: 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!

 
micropc1Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
BBlu ... try the suggestion micropc1 gave and run each query separately and see which one (or both) blow up ...

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
BBluAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
And I was about to say ... if that field contains Null ... same issue.

mx
0
 
BBluAuthor Commented:
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now