Finding the Start of Year

[prgwhiting]

I'm sure this is possible, but I can't find the formula anywhere. I've a table that stores records by Financial Year Date. I've got a Field YearStart which always contains 01/04 and whatever year, and a field YearEnd which always contains 31/03 and whatever year. What I need to do, is via an SQL query only select the year relevant for Date() IE. today 01/12/2001 will bring up 01/04/2001 as year start and 31/03/2002 as year end. I thought I could do this by using YearStart<=Date() etc. but this is flakey and only works on some dates. Any ideas

 

[Rich]

Do you mean that you store records with actual dates and wish to return those within the current financial year when entering Date() as the criteria

 

[prgwhiting]

I've got two fields YearStart and YearEnd. Also in this table are financial limits for these years. What I'm doing is running a query that makes a few calculations but only using the information relevant to today. I should mention that I'm using the British Date format dd/mm/yyyy. So basically what I've done so far is have filter YearStart by making it show me anything that's <=Date() which of course only works if you do YearEnd as >=Date() because then you're selecting the year that adheres to either side of the criteria. This would work fine if I could guarentee that the date format was always going to be the same. My problem at the moment is that it's formating Date() in the american format and because my database will occurr on multiple computers I want to regulate things in access not on the computers. Currently the format problem is manifesting itself in the way that yesterday the 30/11/2001 my query retrieved records. Today 01/12/2001 it retrieves none.

 

[Jack Cowley]

This article shows you how to make a date always a US formatted date. Just modify the code slighly and you can always make your dates conform to the British format. This will mean that regardless of Regional Settings the dates will be in British format.

http://support.microsoft.com/default.aspx?scid=kb;en-us;q210069