By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,486 Members | 1,659 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,486 IT Pros & Developers. It's quick & easy.

DateDiff in an access report

P: 55
On the database that I am developing, I have a date column and I would like to create a report that I would use to calculate the number of months between todays date and the Effective Date. I used the following code as the control source of the report field but I am getting an error.

Expand|Select|Wrap|Line Numbers
  1. =DateDiff("m",[EffectiveDate],Date())
Apr 21 '09 #1
Share this Question
Share on Google+
9 Replies


RuralGuy
Expert 100+
P: 375
Do you have a control named EffectiveDate as well as a field? Try naming the control [txtEffectiveDate] and see if the error goes away. BTW, you *must* display a field if you wish to use it in a report. It can be hidden or behind another control but it needs to be bound to a control so the query will pull it.
Apr 21 '09 #2

missinglinq
Expert 2.5K+
P: 3,532
@RuralGuy
You know, Allan, back when I was running v2000, I would have sworn that was true! But a little while ago I found out that in 2003, at least, the field only has to be a part of the recordsource. You don't have to actually have it tied to a control on the form! I suspect that Billie Bob Gates boys have pulled another fast one on us!

Another point for the OP to consider:

When using DateDiff() you need to be careful in what interval you use. Using 'm' for months, and the dates 3/31/2009 and 5/1/2009, the DateDiff() function will return 2 months, even though the actual difference in the dates is actually only 31 days!

Sometimes this does suit the user's needs, but most people don't consider 31 days anywhere near 2 months!

A more accurate way would be to use days as the interval, then divide by an "average" number of days in a month. I usually simply use 30, but you could be picky and use 2.958.

DateDiff("d",Date1, Date2)\30

will return the nearest whole month, in the above example, 1 month, and 31 days are much closer to 1 month than 2 months.

You could also use

DateDiff("d",Date1, Date2)/30

which will give you month and fraction of a month, and then use a rounding scheme.

Linq ;0)>
Apr 21 '09 #3

RuralGuy
Expert 100+
P: 375
Thanks Linq. It was still true in acXP, which is what I use most. The OP didn't say which Access they were using so to be safe... :D
Apr 21 '09 #4

missinglinq
Expert 2.5K+
P: 3,532
It do strain the brain, don't it, trying to keep track of what's what with which version? Another site I contribute to, which shall remain nameless, per site rules, requires that you declare your version when you register. They then display this anytime you post, along with your name. It really helps in cutting down on the confusion caused by version differences!

Linq ;0)>
Apr 21 '09 #5

NeoPa
Expert Mod 15k+
P: 31,709
@Lebbsy
It wouldn't hurt to share the error you're getting Lebbsy ;)
Apr 21 '09 #6

P: 55
I am using Access 2007.

The error that I am getting is "#error" that I assume is something concerned with the data type but the data type for EffectiveDate is Date.
Apr 22 '09 #7

P: 55
@RuralGuy
Having done the above, the error goes away.

Thanks a ton,
Apr 22 '09 #8

RuralGuy
Expert 100+
P: 375
Have you set the Format of the control that is displaying the value? It should be General Number.
Apr 22 '09 #9

NeoPa
Expert Mod 15k+
P: 31,709
@Lebbsy
Sounds like the control was being used instead of the field and the control was not recognised as a Date value. Nice one RG :)
Apr 22 '09 #10

Post your reply

Sign in to post your reply or Sign up for a free account.