Date Query Problems

[kavsmate]

OK, my brain is fried. I just can't work out how to do this. It's probably ridiculously easy and I'm just not seeing it, but I've spent so long looking at the problem I now can't see passed it.

What I have is a database containing employee information. It is now a requirement for each record to have a fiscal year reference for certain dates and I would like this to be generated automatically, but can't work out how. To give you an example: if a member of staff undertook some H&S training on 01/03/2009, the reference needs to say 0809 (2008-2009 being the fiscal year in which the training took place). If the training was undertaken on 01/04/2009 it would need to say 0910 because it would be a new financial year.

The expression below:

Expr1: IIf([Date Trained] Between #01/04/2009# And #31/03/2010#,"0910",IIf([Date Trained] Between #01/04/2008# And #31/03/2009#,"0809",""))

works, but is of no real use as this needs to be incorporated into an existing database, over half of which is archive, so the dates would be as far back as 2003.

I'm presuming that this could be done with a separate table containing the references and a query from that table, but I just can't work out how to do it.

Any help would be much appreciated.

 

[Galaxiom]

IIF Month([datefield])>6,Format([datefield],"yy") & Format([datefield],"yy")+1, Format([datefield],"yy")-1 & Format([datefield],"yy"))

You might need Val() function on the format functions where the result is added or subtracted. Shouldn't but just in case.

However this would only work from 2010. For proper use

IIF Month([datefield])>6,Format([datefield],"yy") & Format(Format([datefield],"yy")+1,"00"), Format(Format([datefield],"yy")-1,"00") & Format([datefield],"yy"))

However it would be much better done as a function so that Format([datefield],"yy") only needed to be calculated once.

 

[spaddhu]

Generate the table in this format. This should be easy done with Excel. Later copy paste it.

Year | Month | FisYr
------------------------
2003 | 01 | 0304
2003 | 02 | 03042003 | 03 | 0304
2003 | 04 | 0405
2003 | 05 | 0405
...
...

till the period you want. This is easy in Excel, as you have the feature of Autofill.
Let all the columns be formatted as Text. And your table in Access contain the 3 fields as Text.
Then just extract the year and month from your date in the records, apply CStr() and join them with this table on both Year and month.

 

[spaddhu]

GalaxiomAtHome - Woww..
That's a lot more better than what I suggested.. Actually didn't see your reply before posting...