Question End of Year Date Problem

[klix]

Hello,

I wasn't sure exactly where the problem is occurring so I'm not sure where to post my question; hence the general section. I've designed a sort of financial database for my checkbook-keeping. I'm not the most Access-savvy person so explaining how I've designed it could be complex and lengthy.

The problem is that the credit card cycle doesn't correspond to the end of the month, which means I have overlap (e.g. some June expenses appear on the July credit card statement). This wasn't a problem intra-year. But with the new year just passing through (I just began this db in April) I've discovered a new problem: the transactions (records) which occurred in December for the 'January' billing cycle won't show up with the rest of the 'January' transactions (records) which actually took place in January.

This description might sound confusing. I'm sorry but I don't think I can adequately explain it in words. If someone is willing to help I think it would be easier for me to upload my db so you can look at it hands-on. As soon as someone responds that they're willing, I'll put it up and we can go from there.

Thanks.

 

[statsman]

A common problem.

You can:

Wait for your credit card bills to come in and then enter your 2009 expenses. This means you can't do your year end until you get your statements

Or

Enter your credit card purchases into your db when you make them. There is a reason they give you a paper receipt.

 

[klix]

That's not quite the problem. I do keep the paper receipts. But I also enter the date manually, so it doesn't matter when I enter them. I'll explain a little more about the db.

I have a 'TDate' field for the transaction date, which I enter manually. Then I have a 'Month' field to enter the statement month, which I also enter manually. When I pull up the statements in the report, the query sorts by 'Month' and uses the year pulled from the 'TDate' as the year.

Now, the statement month 'January 2010' will contain transactions from December 2009 as well as January 2010. But (I guess because of the way I ask the db to sort) when I try to pull the statement 'January 2010' transactions (records), it won't display correctly. Instead, only the transactions that occurred in January show up. The ones from December 2010 will only display if I sort by 'December 2009'. This totally messes up the statement totals, which is one of the main points of the db.

Maybe I need help with the formula I use to sort the date. I use the simple 'Year[TDate]) now. This problem could be approached this way or another way probably. Thanks for any help.

 

[Rich]

Why do you enter the Month manually, it can be extracted from the date field in much the same as the Year?

 

[Steve R.]

The problem is that the credit card cycle doesn't correspond to the end of the month, which means I have overlap (e.g. some June expenses appear on the July credit card statement). This wasn't a problem intra-year. But with the new year just passing through (I just began this db in April) I've discovered a new problem: the transactions (records) which occurred in December for the 'January' billing cycle won't show up with the rest of the 'January' transactions (records) which actually took place in January.

Do you really have two questions here?

First, if you are trying to reconcile your charges with a credit card's monthly billing cycle, I would suggest a Boolean field for "cleared".

Second, if you want to retrieve data by month, such as December, I would suggest either the use SQL or a filter.

 

[Brianwarnock]

It seems to me that your statement month should also have had a year.

What you are now trying to achieve is

If StatementMonth=1 then Month(Tdate)=1 and year(tdate) =Year(date())
Or Month(Tdate)=12 and Year(Tdate)=Year(date())-1

which translates into a where clause like

WHERE (Format([datetime],"mm/yyyy"))=Format(DateAdd("m",-1,Date()),"mm/yyyy") OR (Format([datetime],"mm/yyyy"))=Format(Date(),"mm/yyyy");

Brian

 

[gemma-the-husky]

as you have discovered, the thing about financial statements is that the transaction date on its own is not sufficient.

you also need to attribute the item to an accounting period, to allow for transactions that come to hand outside the strict calendar month. - as in this case.

that's partly why double entry bookkeeping works as it does.

 

[Rich]

It's much easier to write your own Fiscal year function and use it in relevant Totals queries