access 2007 rejecting format to group by year

[Guest]

I'm looking at moving Access 2002 to 2007. There's a database query to
extract accounting records and grouping/totalling by year. 2007 rejects the
format as 'Undefined function format$. It's -
Field: Date By Year: [Format$]([Accounts - account transactions].Date,"yyyy")
Total: Group by
Any ideas why? Thanks. John

 

[Allen Browne]

What's with the square brackets around the function name?

Try:
Format([Accounts - account transactions].[Date],"yyyy")

If that still fails, try adding your database folder as a trusted location
under:
Office Button | Access Options | Trust Center | Trust Center Settings

Date is not a good name for a field. It is a reserved word in JET (query
engine) and in VBA (for the system date), so is likely to cause you issues.
The square brackets around the field name (as above) may help, but it would
be a good idea to rename the field.

For a list of all the field names to avoid, see:
http://allenbrowne.com/AppIssueBadWord.html

 

[Guest]

Thanks, Allen, that did it. I'm selftaught with Access to help a non-profit.
I can do quite a lot but appreciate the quick, expert help available on these
forums.
John

What's with the square brackets around the function name?

Try:
Format([Accounts - account transactions].[Date],"yyyy")

If that still fails, try adding your database folder as a trusted location
under:
Office Button | Access Options | Trust Center | Trust Center Settings

Date is not a good name for a field. It is a reserved word in JET (query
engine) and in VBA (for the system date), so is likely to cause you issues.
The square brackets around the field name (as above) may help, but it would
be a good idea to rename the field.

For a list of all the field names to avoid, see:
http://allenbrowne.com/AppIssueBadWord.html


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I'm looking at moving Access 2002 to 2007. There's a database query to
extract accounting records and grouping/totalling by year. 2007 rejects
the
format as 'Undefined function format$. It's -
Field: Date By Year: [Format$]([Accounts - account
transactions].Date,"yyyy")
Total: Group by
Any ideas why? Thanks. John