Access 2013 Group on Date error

Question

If one uses the report wizard to group and total on a date field, the report, generated by the software itself, errors out trying to format the date. "Undefined function 'Year' in expression.". Now, mind you, this is an entirely wizard generated report, I made no modifications to the objects created by the wizard. The error is being created by the system generated statement 'Format$', where TrnDate is my date column. Notice that 'Year' is not in that expression. BTW, every row in my table has a valid date in it.

Calling Tech Support was. as usuall, a complete waste of time. Unless I was willing to PAY 295.00, MS didn't want to hear about the bug. There isn't even a place to report this online. MS only accepts bug reports on certain products. Apparently, the newest version of Office isn't one of them.

So, community, unless one of you knows what I'm talking about, I am officially screwed. Sure, I'm gonna renew my Office 365 subscription next year.... NOT!

Answer 1

Who said anything about needing to use the VBA interface?

If the problem is a missing or broken reference it is picked up at runtime because it raises a runtime error.

And since when have the final two arguments of the Format function referred to minutes and seconds?  I can only assume that you are confusing these with the second (Format) argument, which is a string expression in which minutes and seconds are represented by nn:ss, not by zeros.

Answer 2

That format is invalid, Access changes it back to what it generated when you change it. Also, the 0,0 refers to minutes and seconds.

Answer 3

No thanks, If one needs to use the VBA interface to build a database these days, Access is not what it used to be. Also, don't you think this should work out of the box? This 2013 version is generating invalid code. If the system can catch it at run time, then why doesn't it know while it's creating the code?

Answer 4

Is the Year function recognized outside the context of the report?  Try entering:

? Year(Now())

in the debug window.  If it is not recognized it suggests a missing or broken reference.

Even though you don't see any reference to the Year function in the report definition, to group by month first requires a grouping by year of course; otherwise each month, regardless of year, would be grouped together.  So it may well be that the Year and Month functions are being called behind the scenes.

Answer 5

I'm not saying that this isn't a bug.  I thought I had a place you could report it to but I can't find it.  Perhaps someone else can do so.

But anyway, can you use Format without the dollar sign?  That one doesn't handle nulls.

Format([TrnDate], "mmmm yyyy")

And without the first day of the week parts (the 0,0).