Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Union All Query Doesn't work all of a sudden

Posted on 2012-04-04
9
Medium Priority
?
482 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 7

Accepted Solution

by:
micropc1 earned 1100 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 300 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

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!

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
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 …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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…

688 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