Moving from Access 2010 to Access 2007 (Calculated date fields and queries)

[jonathanchye]

Right, I now have to develop in Access 2007 environment and I am kind of stuck.

In Access 2010 you can have calculated fields in tables. I created Calculated fields to autocalculate the month name from dates and then I use that fields easily in the Query wizard.

Now that Access 2007 doesn't support Calculated fields how should I tackle this problem? Specifically how do I create a query that is able to group results into months from a date field?

Thank you.

 

[gemma-the-husky]

personally, i think you are better not using application specific possibiliites, precisely becasue it does limit portabiliity of the app.

if you have a date, then you can get the month number with the month function

month(somedate)
just add this as an extra column to your query.
note that you may need a way of distinguishing the year, as well as the month.



you can get the month name with the format function

format(month(somedate,"mmm"))

try each of these and see the difference
Call MsgBox(Format(Month(Date), "m"))
Call MsgBox(Format(Month(Date), "mm"))
Call MsgBox(Format(Month(Date), "mmm"))
Call MsgBox(Format(Month(Date), "mmmm"))

 

[Banana]

Be aware that they added calculated fields mainly for web applications but the old school method was to do it in the query. So instead of creating a field to calculate the Month name based on another column's month, create a base query that does the same thing then base all your derived queries on this base query.