Export to Excel, update two fields in a query question

[bdhtexas]

We have a report that is generated monthly to management on Excel. We Export the data from an Access query to Excel and at that time the user has to update the Month Reported and Year Reported fields on each record within that query.

Can I set up something to where the user can click a button or something and Access do all of this automatically?

Example: May 2005 was just sent out and we exported the data from the query to Excel and went back into the query and chose update query and had Access fill in May (in the month reported field) and 2005 (in the year reported field) on all of the records.

Any help is appreciated, thanks...

 

[NoFrills]

Curious how the report is generated for the month of May 2005 to be exported to excel. It seems like you are asking to populate a date and year field, but yet you are able to generate a report for that time without the field already in the database?

 

[Smart]

re export to excel

In your access query Add the following two colums

Month: Format([Your date field],"mmmm")
Year: Format([Your date field],"yyyy")

If the date is 10/06/05
Your query will the have two extra colums displayed as follows

June
2005

Hope this helps

 

[bdhtexas]

Curious how the report is generated for the month of May 2005 to be exported to excel. It seems like you are asking to populate a date and year field, but yet you are able to generate a report for that time without the field already in the database?

We run the query based on the Date Resolved field.

 

[bdhtexas]

In your access query Add the following two colums

Month: Format([Your date field],"mmmm")
Year: Format([Your date field],"yyyy")

If the date is 10/06/05
Your query will the have two extra colums displayed as follows

June
2005

Hope this helps

We already have Month and Year fields, it's just that the report for the previous month is due the following month. So, we can't use today's date as a trigger to update the previous months records.