Convert date field ?

[WineSnob]

I have records that have a date field mm/dd/yyyy. I also have fields named Month and Year. How can fill in the Month and Year field from the date field using an update query?

I have Need to fill in
Date [Month] [Year]
5/24/2011 5 2011

 

[Beetle]

The simple answer is - DON'T!

You should not store data like this in a table. Simply display it in a query, or in a calculated control on a form or report, using the Year and Month functions. For example, the calculated field in a query would look like;

MyYear: Year([YourDateField])

or in a calculated control on a form/report;

=Year([YourDateField])

 

[WineSnob]

My problem is I have many other tables that only have [month] and [Year] with values that need to be added.
Ex:
Month Year Sales
5 2011 $1000

Date Expenses
5/24/2011 $100

I need to calculate the net for May $1000 - $100 = $900

 

[Beetle]

My problem is I have many other tables that only have [month] and [Year] with values that need to be added.

What do you mean by "many other tables"? Hopefully you don't have a situtaion where a separate table has been created for every year (or even worse - every month), as this would be a maintenance nightmare. However, even if that is the case, it does not mean that you have to continue the practice by adding more calculated data into another table. You can just create a query using the method I described earlier and then either include fields from other tables for comparison, or create an additional query that joins the first query with additional tables as necessary. The purpose of this would be to summarize data so that it could be output to a from or report, not so that you can store the summarized data in a table.

The problem (actually one of several) with storing calculated or extrapolated data like this in a table is that if any of the underlying data that the original calculation was based on gets changed, then you have calculated data in you table which is wrong, and no reliable way to detect that fact. It also wastes disk space and time (a calculation in a query will almost always be faster than a disk fetch from a table).

 

[WineSnob]

Well not really many tables but some.
Example: Tbl[Sales] is recorded by month not by monthdate Sales: month 5, Year 2011, Actual Sales $20000

Tbl[Expenses] is recorded by monthdate [name],[Date 5/11/2011],[Amt $511]

I want to query Sales-Expenses for May. However in tbl[Sales] the date field is 5 in tbl[Expenses] the date field is 5/11/2011.

any other way to do this is appreciated.

 

[Beetle]

Example: Tbl[Sales] is recorded by month not by monthdate Sales: month 5, Year 2011, Actual Sales $20000

This table should not exist, at least not in that structure. Presumably you have a table where each individual sales transaction is recorded by date, an the table you described is just summarizing that data, which should be done in a query, not a table. An exception here might be that the sales data is coming from some external source and you don't have control over how the data is formatted when it is imported. Is that the case?

 

[WineSnob]

I only get a report of the sales figure once a month from different vendors. Then the Sales are recorded for the month. Example ABC company send a report that says Sales in May 2011 were $20000 DEF Company send a report saying Sales in May 2011 were $4000. I have a column for Budget Sales and Actual sales by Month and year. I record the actual sales when I get the data.

 

[Beetle]

OK, so this is an exeption to the general rule. Does the expenses table record the same Company names (or ID numbers) as the Sales table? If so you can create a Totals query that sums the expenses by month and year (using the Month and Year functions mentioned earlier), then another query that joins the Sales table to the Expenses query on the Company name, and subtracts the SumOfExpenses field (from the Expenses query) from the ActualSales field (from the Sales table).

 

[WineSnob]

Thanks for your help. I figured out a work around where I use an update query and get the month value 5 and the year value 2011 from the date field 5/13/2011 to populate the fields after the date 5/13/2011 is entered.....All is working as needed now.

 

[boblarson]

Thanks for your help. I figured out a work around where I use an update query and get the month value 5 and the year value 2011 from the date field 5/13/2011 to populate the fields after the date 5/13/2011 is entered.....All is working as needed now.

That may appear to be working but it does violate the rules of normalization and puts your data integrity at risk. Why bother when a query can accomplish the same thing. You don't need to store the two values. You can create a base query which just created the two fields in order to link them to the other tables in another query. My view is do not go down the path of storing the redundant data as it will come back to bite you at some point.

 

[WineSnob]

I would prefer to do that. However I guess I don't know how to link a field [month 5] to a field [Date 5/11/2011]. How does month 5 relate to 5/11/2011? If I join the tables using month to date i get nothing.

 

[boblarson]

I would prefer to do that. However I guess I don't know how to link a field [month 5] to a field [Date 5/11/2011]. How does month 5 relate to 5/11/2011? If I join the tables using month to date i get nothing.

You create a base query which is going to replace what you normally use your one table which has the full date in it. And instead of including the date field you would create the month field:

MyMonth:Month([DateFieldNameHere])

and then the year

MyYear:Year([DateFieldNamehere])

and then you use THAT query in place of the table which has the full date in any other queries where you need to link on month and year.

 

[WineSnob]

That is MUCH better!!! I just didn't know how to do it.