DateDiff in an access report

[Lebbsy]

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.

=DateDiff("m",[EffectiveDate],Date())

[RuralGuy]

Do you have a control named EffectiveDate as well as a field? Try naming the control [txtEffectiveDat e] 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.

[missinglinq]

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.

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",Da te1, 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",Da te1, Date2)/30

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

Linq ;0)>

[RuralGuy]

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

[missinglinq]

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)>

[NeoPa]

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.

=DateDiff("m",[EffectiveDate],Date())

It wouldn't hurt to share the error you're getting Lebbsy ;)

[Lebbsy]

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.

[Lebbsy]

Do you have a control named EffectiveDate as well as a field? Try naming the control [txtEffectiveDat e] and see if the error goes away.

Having done the above, the error goes away.

Thanks a ton,

[RuralGuy]

Have you set the Format of the control that is displaying the value? It should be General Number.

[NeoPa]

Having done the above, the error goes away.

Thanks a ton,

Sounds like the control was being used instead of the field and the control was not recognised as a Date value. Nice one RG :)