|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
|
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
|
Click Here
|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
|
| How do I locate the Date Paid for a check payment? |
| Author |
Message |
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-08-25 14:26:31 |
| I have installed your product successfully and it is working fine. I have located the information within Quickbooks I need except for the date paid column in a table. The records I require are found in the [Account] table linked to the [BillExpenseLine] table through [Account].[FullName] and [BillExpenseLine].[ExpenseLineAccountRefFullName]. |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-08-25 14:30:23 |
Basically you need to be looking at the BillLinkedTxn table for your payments and the dates like this:
SELECT BillLinkedTxn.VendorRefFullName as "Supplier",
BillLinkedTxn.LinkedTxnTXNType as Type, BillLinkedTxn.RefNumber as "Bill Num",
BillLinkedTxn.TxnDate as "Bill Date", BillLinkedTxn.DueDate as "Bill Due Date",
BillLinkedTxn.AmountDue as "Bill Amount Due", BillPaymentCheck.TxnDate as "Paid",
BillPaymentCheck.RefNumber as "Check Num",
BillPaymentCheck.Amount as "Amount Paid",
BillPaymentCheck.BankAccountRefFullName as "Account"
FROM BillLinkedTxn, BillPaymentCheck
WHERE BillLinkedTxn.LinkedTxnTxnID = BillPaymentCheck.TxnID
AND (((BillLinkedTxn.TxnDate)>={d '2006-01-01'} And (BillLinkedTxn.TxnDate)<={d '2008-01-01'}))
AND BillLinkedTxn.IsPaid = 1 order by BillLinkedTxn.VendorRefFullName
Or in MS Access you would run this query:
PARAMETERS [Date From] DateTime, [Date To] DateTime;
SELECT BillLinkedTxn.VendorRefFullName as "Supplier",
BillLinkedTxn.LinkedTxnTXNType as Type, BillLinkedTxn.RefNumber as "Bill Num",
BillLinkedTxn.TxnDate as "Bill Date", BillLinkedTxn.DueDate as "Bill Due Date",
BillLinkedTxn.AmountDue as "Bill Amount Due", BillPaymentCheck.TxnDate as "Paid",
BillPaymentCheck.RefNumber as "Check Num",
BillPaymentCheck.Amount as "Amount Paid",
BillPaymentCheck.BankAccountRefFullName as "Account"
FROM BillLinkedTxn, BillPaymentCheck
WHERE BillLinkedTxn.LinkedTxnTxnID = BillPaymentCheck.TxnID
AND (((BillLinkedTxn.TxnDate)>=[Date From] And (BillLinkedTxn.TxnDate)<=[Date To]))
AND BillLinkedTxn.IsPaid = 1 order by BillLinkedTxn.VendorRefFullName; |
|
|
|
|
|
| Posted : 2007-12-07 12:13:11 |
Using
SELECT BillLinkedTxn.VendorRefFullName as "Supplier",
BillLinkedTxn.LinkedTxnTXNType as Type, BillLinkedTxn.RefNumber as "Bill Num",
BillLinkedTxn.TxnDate as "Bill Date", BillLinkedTxn.DueDate as "Bill Due Date",
BillLinkedTxn.AmountDue as "Bill Amount Due", BillPaymentCheck.TxnDate as "Paid",
BillPaymentCheck.RefNumber as "Check Num",
BillPaymentCheck.Amount as "Amount Paid",
BillPaymentCheck.BankAccountRefFullName as "Account"
FROM BillLinkedTxn, BillPaymentCheck
WHERE BillLinkedTxn.LinkedTxnTxnID = BillPaymentCheck.TxnID
AND (((BillLinkedTxn.TxnDate)>={d '2006-01-01'} And (BillLinkedTxn.TxnDate)<={d '2008-01-01'}))
AND BillLinkedTxn.IsPaid = 1 order by BillLinkedTxn.VendorRefFullName
query, I get the same values for last three columns for all transactions. the screen shot is below:
What could be the problem please?
Thank you,
Piyush |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-12-07 15:02:20 |
| It appears the one cheque number has been used to pay everything? |
|
|
|
|
|
| Posted : 2007-12-08 05:35:32 |
Well it was not the same check number for all.
All data were reloaded. I verified that amounts and cheque numbers are different using
SELECT BillPaymentCheck.RefNumber as "Check Num",BillPaymentCheck.Amount as "Amount Paid",BillPaymentCheck.BankAccountRefFullName as "Account"
FROM BillPaymentCheck
When I dropped Order By clause, and lo and behold, the Amount Paid and Cheque numbers appeared!
SELECT BillLinkedTxn.VendorRefFullName as "Supplier",
BillLinkedTxn.LinkedTxnTXNType as Type, BillLinkedTxn.RefNumber as "Bill Num",
BillLinkedTxn.TxnDate as "Bill Date", BillLinkedTxn.DueDate as "Bill Due Date",
BillLinkedTxn.AmountDue as "Bill Amount Due", BillPaymentCheck.TxnDate as "Paid",
BillPaymentCheck.RefNumber as "Check Num",
BillPaymentCheck.Amount as "Amount Paid",
BillPaymentCheck.BankAccountRefFullName as "Account"
FROM BillLinkedTxn, BillPaymentCheck
WHERE BillLinkedTxn.LinkedTxnTxnID = BillPaymentCheck.TxnID
AND (((BillLinkedTxn.TxnDate)>={d '2007-01-01'} And (BillLinkedTxn.TxnDate)<={d '2008-01-01'}))
AND BillLinkedTxn.IsPaid = 1
It puzzles me!
Piyush |
|
|
|
|