Member Login

Username
Password
Forget Password
New Sign Up
Search Forum

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

Forum : QODBC working but will not retrieve dates prior to Sept. using Crystal ReportsSearch Forum

Forum Home > QODBC - ODBC Driver for QuickBooks > QODBC v7 Forum

 New Topic 
 
 Post Reply 
[1]  
 QODBC working but will not retrieve dates prior to Sept. using Crystal Reports 
 Author   Message 
  becker1234 
  
 Group: Members 
 Posts: 9 
 Joined: 2007-08-29 
 Profile
 Posted : 2007-09-04 08:14:52
This is a very weird problem. I finally have the driver working in unattended mode. The crystal report calls for data to be pulled for the "last 7 days". As of the date of this writing this would include Aug 07 and Sep 07 . . That doesn't work. But any data which has been entered ONLY for Sept 07 comes up! How can that be? 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2007-09-04 08:53:44
You haven't shown the SQL Statement that Crystal Reports is using, try looking at: Referencing TODAY's date minus 30 days in a WHERE clause?  

  Top 
  becker1234 
  
 Group: Members 
 Posts: 9 
 Joined: 2007-08-29 
 Profile
 Posted : 2007-09-04 14:01:23

The syntax is the crystal commands and its programmed into the report under the Select Expert as the following formula:

{Command.Date} in Last7Days

This has worked in the past with the 2006 driver - but no longer works under the 2007. Or at least nothing has changed in the report . . .

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2007-09-04 16:21:56
Check that you actually have transactions for the Last7Days. 

  Top 
  becker1234 
  
 Group: Members 
 Posts: 9 
 Joined: 2007-08-29 
 Profile
 Posted : 2007-09-04 22:30:19
I do have transaction for the last 7 days. For some reason it will only read transaction as of begining of Sept. and not end of Aug (tail end of the 7 days). I tried removing the criteria from the selection page and adding the criteria to the command part (sp_report) in the database part. Now I get some dates, but again - not all. How can that be? I thought we have an all or nothing solution? Show I reoptimize or something? 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2007-09-05 09:07:17

Again, you will need to either show a screen dump of what you are trying to do or the SQL statement being executed by Crystal Reports. As far as the Last7Days function is concerned, the function was found to work correctly with QODBC as shown here:-

This report wizard executed the following SQL Statement:

 SELECT "InvoiceLine"."CustomerRefFullName", "InvoiceLine"."TxnDate",
"InvoiceLine"."RefNumber", "InvoiceLine"."InvoiceLineItemRefFullName",
"InvoiceLine"."InvoiceLineDesc", "InvoiceLine"."InvoiceLineQuantity",
"InvoiceLine"."InvoiceLineAmount" FROM   "InvoiceLine" "InvoiceLine"
 WHERE  ("InvoiceLine"."TxnDate">={d '2007-08-29'} AND "InvoiceLine"."TxnDate"<={d '2007-09-04'})

and 5 records were returned, which also matched with the 5 records returned using VB Demo:

For the record, I didn't have any transactions on the 29th, 30th or the 31st of August:

 

 

  Top 
  becker1234 
  
 Group: Members 
 Posts: 9 
 Joined: 2007-08-29 
 Profile
 Posted : 2007-09-05 12:18:41

In my SQL query I have the following:

sp_report BalanceSheetDetail show TxnType, Date, RefNumber, Name, Memo, Amount, Account, RunningBalance, RowNumber,SplitAccount parameters DateMacro = 'Last7Days', AccountFilterType = 'Bank'

This doesn't work.

In addition - what I REALLY want and don't know if possible is instead of Last7Days by transaction date the last 7 days in which the database was modified. This would allow me to include, for example, a transaction put in yesterday but dated laste MONTH!

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2007-09-05 13:32:46

The correct SQL Syntax is:

sp_report BalanceSheetDetail show TxnType, Date, RefNumber, Name, Memo, Amount, Account, RunningBalance, RowNumber,SplitAccount parameters DateMacro = 'LastWeekToDate', AccountFilterType = 'Bank'

The valid values for the QODBC DateMacro parameter are:

|All|Today|ThisWeek|ThisWeekToDate|ThisMonth|ThisMonthToDate|ThisQuarter
|ThisQuarterToDate|ThisYear|ThisYearToDate|Yesterday|LastWeek|LastWeekToDate
|LastMonth|LastMonthToDate|LastQuarter|LastQuarterToDate|LastYear|LastYearToDate
|NextWeek|NextFourWeeks|NextMonth|NextQuarter|NextYear|

 

  Top 
  becker1234 
  
 Group: Members 
 Posts: 9 
 Joined: 2007-08-29 
 Profile
 Posted : 2007-09-05 14:08:11

Things are starting to work as I am slowly figuring this out . . . thanks for your help.

In addition: Is there any way I can use the DateMacro for the date the database was modified (regardless of the date which the user entered for the transaction)? For example: Transaction entered: Sept 4, 2007 ---- but the user tried to write back validate a check and entered Feb 2, 2007 thinking that no one will catch this.....(The date macro will only do transaction for the last week . . . )

Therefore what I want is that regardless of the date that is entered for the check, I want the Lastweektodate to go according to when the transaction database was modified....

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2007-09-05 17:45:18

You're far better off looking at the transactions themselves within the appropriate QODBC table for exceptions. For example, for manual checks, I can do:

SELECT * FROM Check order by TimeModified desc

which will show the latest modified check to the oldest modified date.

The TxnDate, TimeCreated and the last time the transaction was modifed can be anything! To see the last 50 modified entries do:

SELECT Top 50 * FROM Check order by TimeModified desc

 

  Top 
  becker1234 
  
 Group: Members 
 Posts: 9 
 Joined: 2007-08-29 
 Profile
 Posted : 2007-09-06 05:40:31

Since I am beginner at this two further "newibie" questions:

1. Since I am using Crystal to create my reports I am not sure how I can implement your suggestion in the sp_report statement that I have to provide to Crystal. How will I put the suggestion to use the the  "SELECT Top 50 * FROM Check order by TimeModified"  desc in the sp_report statement I need to give Crystal when I click to Add Command and am presented with the statement: sp_report BalanceSheetDetail show TxnType, Date, RefNumber, Name, Memo, Amount, Account, RunningBalance, RowNumber,SplitAccount parameters DateMacro = 'LastWeekToDate', AccountFilterType = 'Bank'

2. How can I filter by TWO account types: Bank AND Credit Card instead of just BANK? What would be the AccountFilterType command to use?

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2007-09-06 08:23:42

For:

sp_report BalanceSheetDetail show TxnType, Date, RefNumber, Name, Memo, Amount, Account, RunningBalance, RowNumber,SplitAccount parameters DateMacro = 'LastWeekToDate', AccountFilterType = 'Bank'

The available AccountFilterTypes that you can use are:

|AccountsPayable|AccountsReceivable|AllowedFor1099|APAndSalesTax|APOrCreditCard|ARAndAP|Asset
|BalanceSheet|Bank|BankAndARAndAPAndUF|BankAndUF|CostOfSales|CreditCard|CurrentAsset
|CurrentAssetAndExpense|CurrentLiability|Equity|EquityAndIncomeAndExpense|ExpenseAndOtherExpense
|FixedAsset|IncomeAndExpense|IncomeAndOtherIncome|Liability|LiabilityAndEquity|LongTermLiability
|NonPosting|OrdinaryExpense|OrdinaryIncome|OrdinaryIncomeAndCOGS|OrdinaryIncomeAndExpense
|OtherAsset|OtherCurrentAsset|OtherCurrentLiability|OtherExpense|OtherIncome|OtherIncomeOrExpense|

You can choose from any one of the above parameters. See: What do the new stored procedures SP_REPORTS and SP_PARAMETERS do? for more information.

You can run any SQL Statement you want in Crystal Reports in the Add Command pane:

 

  Top 
  becker1234 
  
 Group: Members 
 Posts: 9 
 Joined: 2007-08-29 
 Profile
 Posted : 2007-09-06 08:50:52

Apologies for all these issues but here is another one:

In the Enterprise version there is NO VBDemo tool to check out the SQL statements to see the type of records they pool. Right now I have to use Crystal.

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2007-09-06 09:09:34

Ok, download the full version of QODBC from: How can I get the latest version of QODBC? and then see: How do I use the VB Demo for testing? 

Note: Each SQL Statement needs to be executed seperately in either VB Demo or Crystal Reports. They can't be combined.

 

  Top 
  becker1234 
  
 Group: Members 
 Posts: 9 
 Joined: 2007-08-29 
 Profile
 Posted : 2007-09-06 09:27:05
Will I need another license? 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2007-09-06 09:31:23
No, you can install the full version of QODBC over your Enterprise Edition. Intuit has chosen to restrict certain functions in what's delivered with QuickBooks Enterprise, but the full version of QODBC will work in read-only mode with USA editions of QuickBooks Enterprise 2006 and 2007 (or later) without a CD Key. 

  Top 
 New Topic 
 
 Post Reply 
[1]  

Jump to