Transaction Pro Homepage
Forum Home Forum Home > QuickBooks Products > 01 Transaction Pro Exporter
  New Posts New Posts RSS Feed - Zeros to Blank Fields
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Zeros to Blank Fields

 Post Reply Post Reply
Author
Message
Ektheo View Drop Down
Newbie
Newbie


Joined: 28 December 2017
Status: Offline
Points: 4
Post Options Post Options   Thanks (0) Thanks(0)   Quote Ektheo Quote  Post ReplyReply Direct Link To This Post Topic: Zeros to Blank Fields
    Posted: 28 December 2017 at 5:43pm
We are running into a problem with a process that produces fields with zeros in them.  I recognize the problem may be with our process, but I'm trying to find out if I can correct the problem in Transaction Pro for QB Desktop.  Here's the process:

1. Using an Access macro, we create a spreadsheet that fits the Journal Entry Import Type for Transaction Pro Import.  The bottom of the entry always as two lines: a debit to an income account and a credit to an income account.  Sometimes there is no entry for the debit line, so the Access macro puts a zero on both the debit and credit fields (I tried to attach a screenshot but couldn't get it to work on the forum.)

2. We use Transaction Pro Import to import the entry into QB.  After importing, when we view the entry in QB, the zeros are now blanks.

3.  When we export the transaction from QB for another database used for reporting, Transaction Pro Export makes the debit line in question a "-" instead of a blank.  The database doesn't recognize the "-", so it errors out during its append query.

I realize that one alternative is to edit the debit line manually, but I'm wondering if there is a way to deal with the issue in Transaction Pro Import or Export.
Back to Top
QBKaren View Drop Down
Admin Group
Admin Group
Avatar

Joined: 26 March 2011
Location: Sarasota, FL
Status: Offline
Points: 4126
Post Options Post Options   Thanks (0) Thanks(0)   Quote QBKaren Quote  Post ReplyReply Direct Link To This Post Posted: 02 January 2018 at 10:28am
I guess I do not understand what the underlying issue is with the Importer. The journal entry is correct is it just formatting so I don't understand why it matter if the field is blank or zero in QuickBooks. 

Or is this more of a question in how the exporter formats the cells?

Could you clarify? Thanks.
Visit our blog at http://www.blog.transactionpro.com for more tips and how to examples
Back to Top
Ektheo View Drop Down
Newbie
Newbie


Joined: 28 December 2017
Status: Offline
Points: 4
Post Options Post Options   Thanks (0) Thanks(0)   Quote Ektheo Quote  Post ReplyReply Direct Link To This Post Posted: 03 January 2018 at 2:07pm
Thank you, QBKaren.  I appreciate your response.  I was wondering if I should have restated my question.  Sorry I didn't go back and do that.

I think the question has more to do with how the Exporter formats the cells, but may have to do with the Importer.  When there is a line in a QB journal entry where both the debit and credit fields are blank, the Exporter formats the amount field as a "-" (dash or hyphen).  This dash trips up a macro we run in Access to pull in the data from the exported spreadsheet.

The reason I brought up the Importer is because that's where the journal entry with the blanks in QB comes from.  The spreadsheet has "zeros" in the debit/credit fields, but after the Importer completes the import, the fields are blank in QB.

What I would like to know is if there is something in TP that I can do to address this issue, or will I need to look elsewhere in our processes?

Here are some screen shots of what I'm referring to.  The numbers indicate the order of our process as it concerns TP.


1 is in Excel
2 is in TP Importer
3 is in QB
4 is in TP Exporter

Thank you!



Back to Top
Administrator View Drop Down
Admin Group
Admin Group


Joined: 01 October 2003
Status: Offline
Points: 2940
Post Options Post Options   Thanks (0) Thanks(0)   Quote Administrator Quote  Post ReplyReply Direct Link To This Post Posted: 04 January 2018 at 12:27pm
Why not just use an IIF statement in your append query to filter out the dash?

IIF(Trim([DEBITCREDIT]) = "-", 0, [DEBITCREDIT])

where DEBITCREDIT is the Access field name you are attempting to append to your database table.
Back to Top
Ektheo View Drop Down
Newbie
Newbie


Joined: 28 December 2017
Status: Offline
Points: 4
Post Options Post Options   Thanks (0) Thanks(0)   Quote Ektheo Quote  Post ReplyReply Direct Link To This Post Posted: 09 January 2018 at 10:33am
Thank you.  That did it!  I approached the question from the TP angle because I only know enough about Access to be dangerous.  I appreciate you taking the time to write out what I needed - it was easy to change.
Back to Top
QBKaren View Drop Down
Admin Group
Admin Group
Avatar

Joined: 26 March 2011
Location: Sarasota, FL
Status: Offline
Points: 4126
Post Options Post Options   Thanks (0) Thanks(0)   Quote QBKaren Quote  Post ReplyReply Direct Link To This Post Posted: 10 January 2018 at 10:47am
We are glad that this worked out for you. 
Visit our blog at http://www.blog.transactionpro.com for more tips and how to examples
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 11.07
Copyright ©2001-2016 Web Wiz Ltd.