Date manipulations in Access

[cgsiii]

I have two calculated date fields that I am trying to create in a Make Table Query. I am using Access 97 and have not been able to locate any answers in the Help file. I have also searched the message boards to no avail, so if by chance these questions have been answered, I apologize, but ask that you direct me to the responses.

1. I would like to take a date field mm/dd/yy, and return the text name of the month. The Excel function of =TEXT(A1, "mmmm") works great, but I can not locate the same function in Access.

2. again using a date field mm/dd/yy, I would like to create a "week of" date with the week beginning on Monday. I had created a formula to create such a field some years ago in Excel, but have long since forgotten how to do it, not to mention that Access functionality is not always a mirror image of Excel

 

[Parra]

In the query view go to your date field.
Right mouse click & choose Properties
Under the General Tab > Format type the following:
mmmm
When you run the query this will only give you the month.

Hope this helps.

 

[cgsiii]

Parra, Thanks for the quick response and advice that was exactly what I needed. I was looking for some type of complex calculation and the answer was simply built intot he field format. I did get thrown for a bit of a loop however. The fix worked in VIEW, but when I actually created the table, the results still showed the standard date format. I then realized that since I had previously created the table, the original formatting was still present. A simple format change in the table corrected the problem.
While no one responded to my second question, I believe I "tinkered" my way to a solution. Others may suggest a more elegant way to accomplish the end goal, but I created the following and it works for what I needed to accomplish: Week Of: [customer acceptance date]-(Format([customer acceptance date],"w")-2) with [customer acceptance date] being a date in the mm/dd/yy format.


Chuck