transactions posted with incorrect date

I had that happen to me once, I have no idea how the date got changed. What I did was set the system back to the erroneous date and voided all the transactions made on that date and then ran a Z report, which basically set that day back to zero $ and inventory correct even though there were still transactions for that date. I then set the system back to the right date and ran the transactions through on that date and ran another Z report. All transactions were shown correctly on the right date, money was right, and inventory was correct. I ended up with an extra batch, but that was no big deal to me.

Craig

It can also be done with a query that finds transactions with the wrong date and changes them. I have done it in the past, but am not great with SQL, so I use MS Access to write my queries. Hopefully someone better with SQL will post a query for you.

Marc

you could modify the tables with the following scripts:

update [transaction] set timeÚteadd(dd,-24,time) where time>'7/28/10' update [journal] set timeÚteadd(dd,-24,time) where time>'7/28/10' update dailysales set date='7/4/10' where date='7/28/10'

this assumes the right date was 7/4/10, modify as needed.

Marc,

My only question regarding using a query to change the dates would be how would it effect sales and tax reporting, along with customer transaction records. The Z report for both days would definitely be off. There also would be no paper trail for how and why those days transactions dates were changed. I'm not saying using a query wouldn't be OK, but in this case it might not be the best option. Matt gave the queries so ck in ks can decide which way is appropriate for them. The queries would be quicker, but not by much, unless there where a lot of transactions to void. When dealing with transactions I prefer to make sure I have a paper trail in case of an audit. I use queries in a lot of cases, but this is one instance I wouldn't be comfortable with. Just my 2 cents.

Craig

Another question I just thought of when using these queries. Wouldn't changing the date after a Z report was run for that date open another batch for those transactions? I would be afraid using these queries could open up a whole other can of worms to deal with. I got myself in trouble a long time ago when I used some queries provided to me and I didn't really think through all of the ramifications of running those queries, they can have a domino effect on your data sometimes that you may not be aware of until after it's too late. I had to ask for more help to fix what I screwed up.

Craig

Craig, you absolutely should be cautious about changing the data directly, but fixing the data to put the records on the right day actually makes everything work better, because the reports and history then matches the z report of the batch they were part of. Keep in mind that the z report doesn't care what date was used during the batch, only what has happened between the opening of the batch (last z report) and now.

I think the only thing that wouldn't match well, would be the date stamped on the paper receipt not matching the correct day. To fix this, you would have to use the previous mentioned solution to change the date, reverse transaction, change back and recreate the transaction again.

Ditto what Matt said - LOL

I actually wanted the dates changed so my sales reports (by date, not batch) would be correct. Also sales tax reports could be off, if the dates fell across months.There may be a few other things I am missing that would be effected by wrong dates. When I looked into it, like Matt says, I did not see any related tables that would cause issues with the changes, except that receipt/journal dates would be different.

Marc

Matt,

That was my point, the paper trail for the transactions wouldn't match the electronic versions. Thus any audit would be more intense as you try to explain all of this. I just don't think this would be one of those times I would take the easy way out with the queries. I still am not convinced there wouldn't be any unforeseen problems with doing this. A batch that was already closed would then have some new transactions in it not included in the original batch.

I understand what you are saying, I guess it all boils down to what data you are comfortable with manipulating after the fact with queries. I messed things up once because I didn't completely understand the balance between all the different table structures and even though I did a backup before, the problems didn't show up until a few days later, making that backup useless. I then had to ask for more help with queries to fix what I had messed up. :-) I really do appreciate that you are willing to share your expertise with all of us though. It's very helpful at times to use queries to manipulate things, and a lot of us need the help writing those queries, so thank you. I hope those that have never used them before really think twice before just forging ahead, because it can be really dangerous.

Craig

No, fixing the date on the transactions that are on the wrong date would not open a new batch.

Matt, In ck's post they didn't say they went back to the wrong date to run a Z and close the batch, leaving that batch open with transactions on it. And if the transactions were never run on the date they were supposed to be run on, which would result in a closed batch without those transactions being there in the first place. How would it not screw something up? Would the queries make it so that erroneous batch never existed in the first place? I'm not trying to be difficult here, I'm just trying to understand how doing it with queries would be better than correcting everything manually so all batches get closed properly, and all Z's and other reports reflect the error and the process of fixing it. As I said before, I've run a lot of queries to add something to a description of a group of products, change suppliers for a group of products,etc. But I've never run any that would alter a transaction or a batches history. That makes me nervous. Don't misunderstand me, I am not saying you don't know what you're talking about, actually I'm quite sure you do(that's why all the questions). But forcing transactions into a batch by query doesn't sound like the proper way to handle this error business process wise(you know the old saying "just because you can do something, doesn't mean you should do it"). The only reason I keep on with this is I'm sure it will probably happen to me again sometime, and I would like to have a good understanding of the different ways it can be fixed(btw, thanks for the suggested queries, I will save them in my library I have received from this group), and what others opinions are on the best way to handle correcting it. Not the easiest way, but the best way. Since ck brought it up I thought it would be a good time to open a discussion about it which would benefit all of us.

Craig

"Matt Hurst" wrote in message news: snipped-for-privacy@TK2MSFTNGP05.phx.gbl...

My last post on the subject:

1. I do it all the time.

1. Doesn't "force into a batch" because it was already part of that batch

2. "how is it better?" because I like fixing other people's mistakes with elegant solutions

1. "makes me nervous" then probably not for you.

2. ck: "I had a customer" support partners should know what they are doing, if not, hire backup.

Matt,

Sorry I upset you. I wasn't trying to argue with you(sometimes the written word comes across more blunt then you intend it to) I was just trying to get a handle on what would be the best way for an end user (me and most everyone else visiting here) looking to take care of something like this ourselves before asking our partner for help; which, believe it or not, I do when I need to. I was only asking for an opinion on the dangers versus the benefits, and whether queries in this case would be better than taking a less "elegant" approach like voiding the transactions for a paper trail to show the error and how it was fixed. There could be many reasons for doing it either way and I was just looking to see what others thought about it. I think I understand now about the date not mattering and as such it would have closed with the rest of the batch making the change to the correct date the proper way to handle this, I just have seen some screwy things happen when you least expect them too, so I proceed with an abundance of caution.

Sorry for taking up your time, Craig