calculating value based on date entered.

[Megacitizen]

I'm working on a DB recording the stats of various teams from around the world, all working in the same city at various stages throughout one calender year. The year is split into 4 quarters which we are actually terming as "Waves", numbered 1-4.

I am trying to set up a calculation whereby the user inputs the date a team arrives in the city, and the form then calculates whether they are Wave 1, 2, 3 or 4.

I have got the validation for the Wave field as >0 and <5 but not sure if I need a code or an expression for the calculation. Also, I can easily calculate records for THIS year (specific dates where a team arriving on or after that date is in a specific wave), but I need to consider future years as the actual dates when a wave changes will vary from year to year. We do a new city every 2 years.

Using MS Access 2010

 

[Beetle]

You need a table to store the valid date ranges for the Waves for the current and future years, i.e.;

tblWaves
*******
WaveID (PK)
WaveNumber
DateFrom
DateTo

When a new record is added, the arrival date entered is compared to the Wave table and the appropriate WaveNumber is returned (via a DLookup or some other method)

 

[Megacitizen]

I never really thought of tables - should really have posted this thread on the Forms pages, wasn't checking where I was reading

I'm only questioning your suggestion because we will be having the situation where no matter what year or what city, our Waves will ALWAYS be numbered 1-4. Having tables may suggest that that would bring up conflicting or repeating data, even though dates won't match. Also, I assume the user would have to ensure the table is correctly updated each new year with the parameters for that year.

 

[Beetle]

should really have posted this thread on the Forms pages, wasn't checking where I was reading

I didn't suggest a table simply because you posted this in the tables group. If the date parameters for each Wave are going to change from year to year, then you need a table. If the values were static then you could just hard code the values in a function or an expression, but that's not the case here.

our Waves will ALWAYS be numbered 1-4. Having tables may suggest that that would bring up conflicting or repeating data

No. The Wave Number (1 through 4) won't be the Primary Key, and since the date values will be different for each row, this doesn't constitute repeating data.

Also, I assume the user would have to ensure the table is correctly updated each new year with the parameters for that year.

Yes. You can enter them in advance if you know what they're going to be, but otherwise they will have to be entered at the time they become available.