It seems that Microsoft Office Accounting Professional (2007) has a small problem. Actually, I’m not an accountant, so there may be more than one. But in this case, the problem is that once you have moved past the end of a year (here, 2007) and started entering items in a new fiscal year (2008), the accounting package doesn’t want to let you enter any complex journal entries any longer. If you want to enter a single line-item transaction, it’s fine and you don’t even notice it. That’s great, and the way that I prefer to go. But unfortunately, every once in a while, I have to enter something a little more complex, perhaps spanning two accounts on one side of the entry and one on the other. You can do that on a simple one-line transaction.
At that point, you have a problem and you get the message that says “A Voucher No. on the lines may only be used once per fiscal year”. I had actually seen this message before (and I don’t recall what that one was for – it may have been the same thing), but I managed to get around it somehow. At this point, I needed to enter an adjustment for last year, and 2008 is well under way. So I needed to figure a way around it. For those who are asking, Quickbooks isn’t a solution at this point. I am getting there, but it won’t happen in the next couple of days, when I need to have things turned over to the accountant, and that’s when I started digging. The good news is that it’s fixable. The bad news it that you have to do a little work.
If you’re familiar with SQL, then it’s not bad at all. If you aren’t, well, get used to it, because that’s the only way I’ve been able to fix the problem. I just couldn’t take a three-account transaction and make it into two, no matter how hard I tried. So before you continue, make sure you have a backup. If there’s one thing that Office Accounting does, it is back up well. So get that out of the way. Next, you’ll need to be able to attach to your database. I use the SQL Query Analyzer because I have SQL Server installed for just this reason.
What I ended up doing is adding my adjustment with a date of 1/1/2008. That way it’s in the database (and it shows that it works – just not for the prior year), and I just need to figure out how to update the dates. That’s where SQL comes into play. The nice thing about using Query Analyzer is that you can just browse the server. Simply select connect and connect to your server like it’s a real one – you use servername\mssmlbiz and you’ll probably want to use your administrator’s credentials. That should get you in. Change to the database (typically named after your company) and you should be set. Then you can browse the various tables in the database (there are a lot of them!). You really are only concerned with five however.
Locate dbo.accountregisterlinetable, dbo.documentlinetable, dbo.documentable, dbo.postingtable and dbo.transactiontable (these are listed alphabetically). Once you do, you can just right-click and select Open to browse the contents (you have to do these one at a time). Chances are your adjustments are the last entries – if they are not, you might need to do some searching to locate them. In some tables, you will have a single entry, in some tables you will have an entry for each item in the transaction – make sure that you change each of them!
When you do make the change, you can just scroll through the entries to modify the date that you see there. While you can change the modified dates, you really don’t need to do that. Just change the posting dates. It’s called FinancialDateDate in each table. The only ones that appear to be strictly necessary are dbo.accountregisterlinetable (what you see when you open an account register) and dbo.documentlinetable (what you see when you then open the journal entry). It is probably a good idea to update the other tables to be consistent throughout the database though.
Why does Microsoft have so many copies of the same data? I have no idea. Why can you enter the same transaction for the current year? Not a clue. Why can you make this change, and afterwards you’ll be able to open that transaction, and even make changes to it? You had me at “Hello, Quickbooks”. But hopefully this will help someone else, so that you don’t have to go looking for all the tables. Enjoy, and don’t forget to back up first!
Update: It appears that the root cause of the problem actually isn’t that you have switched fiscal years, as I first thought, but that the saved document never increments the document number. So if you saved voucher number 1, and incremented your fiscal year, then tried to use the memorized document, you would get an error – because voucher number 1 would be in use again.
If you’re using a memorized document, think that perhaps you might want to generate a new voucher number? That’s just stupid, Microsoft.
For those of you who don’t want to mess with the SQL solution above, go down to the first unused line in the journal entry, and pretend that you’re going to add to the document. You’ll get a new line item, with the first unused voucher number. Then switch all your voucher numbers to that number instead.
Next, you’ll also need to update your dates, because strangely, Microsoft assumes you wanted to memorize your document and reuse it for the exact same date forever, too. Now that’s really lame. But that’s the way it is. Once you do, you’ll be all set.