Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Morecambe England, Lancashire, England
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Displaying English Tax years (Access 2000 / Windows 98)

    I wanted a report that printed out the English Tax year in a text box at the top of the report, in the form 6th April 2005 to 5th April 2006 and when next year comes round it must change it to 2006 to 2007.

    That 's fairly straight forward. The problem was that if you are doing a report before 5th April it should show 2004 to 2005 and after the 6th April it should show 2005 to 2006.

    I am quite sure that Hans or one of you other experts will have a much better solution, but I was so pleased I was able to crack it, I thought that some of you might be interested in my solutuion.

    =IIf(Year(Now()-96)-Year(Now())<0,"6th April " & Year(Now()-365) & " and 5th April " & Year(Now()),"6th April " & Year(Now()) & " and 5th April" & Year(Now()+365))

    I've used the year function, to get the current year Year(Now), which I had not tried before and by subtracting 96 from Now(), it checks to see if the day is before or past 6th April (allowing for leap years). The rest is pretty straight forward.

    I get so much help from this board and feel guilty that I can't put much back in, but perhaps something like this may help a bit

    Michael Peak
    Morecambe England

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Morecambe England, Lancashire, England
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying English Tax years (Access 2000 / Windows 98)

    Thank you Hans -

    Two problems though

    I pasted it in and it would not work

    I tried just = year([d]) and that didn't work

    Is it version specific ? like 2003?

    If I can get it working it will help in lots of other instances when I've said "Oh forget leap years"

    Michael

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Displaying English Tax years (Access 2000 / Windows 98)

    Edited by HansV to correct mistake

    Your expression works correctly almost every day of the year, but not on the 6th of April in non-leap years. Here is an alternative expression that handles those dates too:

    ="6th April " & Year(Date())-1-(Date()>DateSerial(Year(Date()),4,5)) & " and 5th April " & Year(Date())-(Date()>DateSerial(Year(Date()),4,5))

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Displaying English Tax years (Access 2000 / Windows 98)

    Oops - sorry about that. I just pasted my test expression which used a date d without changing it to Date() (I obviously couldn't test it with just the current date). I have corrected my reply.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Morecambe England, Lancashire, England
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying English Tax years (Access 2000 / Windows 98)

    Thanks Hans

    Great it works fine

    DateSerial(Year(Date()),4,5) has opened up a whole new world to explore!

    Best wishes
    Michael

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •