Adding Columns with Different Date Formats

[brian.hutchings@spsaerost]

I have an Access table that includes a column for dates in the format dd/mm/yyyy. I added two extra columns intending to use them for dates by week number/year and month/year. Is there some way of generating these from the existing date in order to analize some data in an Excel pivot table. I do not want to change the existing date column as that is used by another pivot chart.

Many thanks, Brian Hutchings

 

[Ziggy]

Try the following expressions


WeekNum: Format([YourDate],"ww")

Month: Month([Yourdate])

Ziggy

 

[brian.hutchings@spsaerost]

Many thanks for the suggestion Ziggy. The problem with that is that I would have to manually input the date every time (as I see it). There are already some 1500 dates in the table with new ones being added every day. What I was looking for is a pair of formulas that takes the existing date, reformats it and places it in a new column.

 

[Mudface]

Try adding all your existing fields to a query, then add two extra columns formatting your date field as Ziggy suggested. You can then use the query in the same way as an ordinary table (eg to create forms, reports, export to Excel etc) even though you haven't actually typed in the reformatted dates manually.

 

[CT Witter]

May ask why you need to store this data in the table?

It is not normally a good practice to store information that can be calculated. The reason being that as you have seen you have to write custom functions to populate this data. Access doesn't have triggers like sql server or other high end dbs. Also, What if you change one of the dates? You now have to change it in 2 additional places. If you don't then you have a data integrity issue.

I would definitely do as Mudface has suggested. Take the fields that ziggy proposed and add to a query.

HTH,
CT

 

[mdmilner]

Ziggy, Mudface and CT Witter are absolutely right.

My addition is description and explanation of the above - by your response I don't think you understood some of the above.

What Ziggy was proposing is that you setup a query and within QBE (Query by Example -- the default Access Wizard interface that steps you through SQL query creation) you add his text.

In SQL, those fields would look like:
Format([YourDate],"ww") As WeekNum, Month([Yourdate]) As dtMonth.

But, I'm not sure you should use a fieldname of 'Month'. It may work 99% of the time depending on where precisely it's used, but it's a reserved word and references to the field in VBA should have [] brackets around it.

Secondly, I'm not sure you understand exactly what a query can do. When you execute a query, it can calculate the values for ALL fields in the table during a single execution regardless of quantity. Using a query (and basing forms or reports) off it is even better because you never have to go back and make sure you updated the latest records added.

Even better, queries can be exported and printed *JUST LIKE THE TABLE* that it's based upon.

Use the query...feel the (Access) love.

Mike

 

[brian.hutchings@spsaerost]

Many thanks for the replies; to answer CT Witter and mdmilner, the reason for storing the information in the table is that the table has been built up over the last four years and the information has been analysed using pivot tables in Excel, using the date as dd/mm/yyyy. The "powers that be" have now decided that they want to see the information weekly and monthly.
You are quite right in saying that my understanding is limited, I only deal with some limited aspects of the programs to generate charts that monitor my companies quality performance. There is a requirement that the charts are updated automatically as more information is added on a daily basis, my understanding is that a query only looks at the data at the time that the query is run and does not look at any data that is added afterwards. Again, many thanks for your help, I'm now off to read a book on Access and to have a play with the various suggestions

Regards, Brian Hutchings

 

[Ziggy]

The query is run when you open it so it is no different than when you look at the table data, a query gives you more flexibility. The table is basically your storage container for the data. The query can be the datasource for reports as well.

there should be no problem in automating what you need, outline the steps you might take in the process you use.

Ziggy

 

[brian.hutchings@spsaerost]

Thanks for your help Ziggy, I didn't realise that Queries could be as useful as that. As I said before, I have obtained a book that explains Access in depth so I will be using your suggestion to solve my problem

Regards, Brian Hutchings