capture the year of a date

[bogglebeats]

hello i am trying to base the value of a field on date which is entered by the user. so if 'date_received' is entered as 1/23/03, i would like the value of the field 'past1' to equal 2001, 'past2' to 2002, etc. is there a way to separate out the year to execute this calculation? thanks.

ameen

 

[Mile-O]

Firstly, you don't need to store this in a table as it is a calculated value.

If you need to query the date field by year then use a query to return the results by adding a calculated field.

To add a calculated field simply pick on of the empty blocks in the query design grid and put the name you wish to call this field (you shuld not call it YEAR, though - it is a reserved word) and then we put the expression to get the year from the date:

We'll call the new field OurNewField

OurNewField: Year([YourDateField])

So we now have a calculated field in our query - all it needs now is the criteria i.e. 2002, 2003, etc.

 

[Tim K.]

You can use DateDiff() like this on the past1 and past2

=DateDiff("yyyy",-2, Me.date_received)

=DateDiff("yyyy",-1, Me.date_received)

But you don't need to store calculated fields. You can create them on-fly in a query.

 

[Mile-O]

Also, by having fields with repeating groups you are violating database normalisation rules.