I have a database which is used to store the records for a local flying club who's club year runs from the 5th of Decemeber - so on the 5th of December this year (2006) the club year will be 2007.

Is it possible to set a value when the database is opened, say 'Club-Year', based on the above criteria which would then be availbale to use in forms, queries etc also in the database.

Thanks,

Russell

You could have a little table.

ClubYear. . . . . . .StartDate. . . . . . . . .EndDate

2006. . . . . . . . . .05/12/05. . . . . . . . . 04/12/06
2007. . . . . . . . . .05/12/06. . . . . . . . . 04/12/07
2008. . . . . . . . . .05/12/07. . . . . . . . . 04/12/08

etc

So depending on where todays date Date() sits, you then select the clubYear.

Col

Thanks for the reply Colin.

I can see the basis for your solution, but how would I then code an expression in a the criteria field of a query to set it to the current club year.

Russell

i would store the current CLUBYEAR in a constants table. Do you need to test it against anything, or is it just a "fact" as it were.

Hi Gemma

It's just a fact. Your idea sounds good - how would I go about it?

Russ

just have a table called constants etc, with a single row, and look up the info when you need it (or when yuo open the dbs, and store it in a variable.

lots of uses for things like company name, address, and various overall system settings, eg default vat rate, and any other defaults you may need to use.

Thanks - I'll give it a try.

Hi -

Given the scenario you describe, think you can probably pull this off with a one-liner, without need for a table. Couple of examples from the debug (immediate) window:

mydate = #12/5/06#
clubyear = year(mydate) + iif(mydate >=dateserial(year(mydate),12,5), 1, 0)
? clubyear
2007

mydate = #11/13/06#
clubyear = year(mydate) + iif(mydate >=dateserial(year(mydate),12,5), 1, 0)
? clubyear
2006


HTH - Bob