Results 1 to 11 of 11
  1. #1
    sdel_nevo is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402

    Financial years

    Hi Everyone



    Im really sorry if this has been posted before.

    i have a form in my database that contains a financial start date and end date

    start date - 07-04-2016
    end date - 06-04-2017

    is it possible that these dates can automatically change in the new financial year

    so for example

    when todays date is bigger than 07-04-2017 change the start date to 07-04-2017 and change the end date to 06-04-2018

    if so

    would this be the logic

    if date(now()) > me.startdate then
    me.startdate= 07-04 (of the current year)
    me.enddate = me.enddate + add 1 year, but keep the day and month the same
    end if

    for some reason im really struggling with this, im not even sure that the logic is correct to be honest, i have been mulling this over for a while now, i think i have got to the point were i can't see the woods for the trees lol

    any ideas how i could do this

    many thanks

    Steve

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    try

    me.startdate=cdate("07/04/" & year(date())+(format(date(),"mmdd")<="0407"))
    me.enddate=cdate("06/04/" & year(date())-(format(date(),"mmdd")>="0406"))

  3. #3
    sdel_nevo is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi Ajax

    Thats brill

    many thanks for the reply, will give that a go and let you know how it goes

    Steve

  4. #4
    sdel_nevo is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi Ajax

    Thats wonderful, many many thanks

    Steve

  5. #5
    sdel_nevo is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi Ajax

    the example you gave was brilliant and works really well if i enter the dates manually, but just out of interest
    would it be possible to change the Bold items

    me.startdate=cdate("07/04/" & year(date())+(format(date(),"mmdd")<="0407"))
    me.enddate=cdate("06/04/" & year(date())-(format(date(),"mmdd")>="0406"))

    with the content of the start date and end date fields

    for example

    me.startdate=cdate("date from date field" & year(date())+(format(date(),"mmdd")<="date from date field"))
    me.enddate=cdate(date from date field" & year(date())-(format(date(),"mmdd")>="date from date field"))

    i believe from your example that i would have to break out the day and month but i am unsure how to do this
    any ideas


    Sorry to be a pain
    Steve

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    don't know what you mean by 'date from date field'. Date is a reserved word (it means today). Suggest provide some examples

  7. #7
    sdel_nevo is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi Ajax

    sorry i should have been more descriptive

    me.startdate=cdate("date from date field" & year(date())+(format(date(),"mmdd")<="date from date field"))
    me.enddate=cdate(date from date field" & year(date())-(format(date(),"mmdd")>="date from date field"))

    the "date from date field will be dates entered into a date field on a form.

    so basically what i would like to do is

    enter a "startdate" into a date filed on a form by using a date picker
    enter a "enddate" into a date field on a form by using a date picker

    these dates would then be the "bold" text in the code example above

    hope this makes sence

    Steve

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Ah - terminology, though still not clear

    I thought your financial year ran from 7th April. Are you saying you want different financial year start dates? in which case why not just use the date that has been picked on the form?

    if you mean you want to find the financial year that a date on the form falls into then you would replace the date() function with a reference to your form control

    e.g.

    me.startdate=cdate("07/04/" & year(me.datecontrol)+(format(me.datecontrol,"mmdd" )<="0407"))

  9. #9
    sdel_nevo is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi Ajax

    yeah terminology makes things really hard, harder still when you really don't know what you are trying to achieve lol


    yeah i wanted to use a different financial year, i am trying to make a form for a friend of mine and im not sure what his dates would be, i used those as they are the ones i use.

    ok

    so on my form i have 4 date fields

    Me.FinancialStartDate
    Me.FinancialEndDate
    These are the date fields that the user (my friend) will select the start and end dates from

    Me.StartDateTXTBox
    Me.EnddateTXTbox
    these are hidden, and break out the day and month using the code below


    Me.StartDateTXTBox = Format(Me.FinancialStartDate, "ddmm")
    Me.EndDateTXTBox = Format(Me.FinancialEndDate, "ddmm")


    Me.FinancialStartDate = CDate(Me.StartDateTXTBox & Year(Date) + (Format(Date, "ddmm") <= Me.StartDateTXTBox))
    Me.FinancialEndDate = CDate(Me.EndDateTXTBox & Year(Date) - (Format(Date, "ddmm") >= Me.EndDateTXTBox))

    i thought it would be just a case of using the code below to mimic you original example, but i get a "error has been found Type 13"

    i thought it may be due to my formatting the dates "ddmm" but i also get the error with "mmdd"

    Steve

  10. #10
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you've changed the code - Format(Date, "ddmm") should be Format(Date, "mmdd") - google 'sorting text' to find out why

    and this

    Me.StartDateTXTBox

    assuming this has been populated by a datepicker needs to be

    format(Me.StartDateTXTBox,"mmdd")

    suggest you google about what dates actually are and the effect of formatting as well

  11. #11
    sdel_nevo is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi Ajax

    thanks for the reply, i will google as suggested

    many thanks

    steve

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Changing financial years in a query
    By Blackcat in forum Queries
    Replies: 2
    Last Post: 02-10-2016, 05:41 PM
  2. Financial Year Query Help
    By Kirsti in forum Queries
    Replies: 12
    Last Post: 07-04-2012, 10:19 PM
  3. Financial Report
    By Luke in forum Reports
    Replies: 11
    Last Post: 07-05-2011, 01:08 PM
  4. Financial Form
    By Luke in forum Access
    Replies: 23
    Last Post: 07-05-2011, 07:59 AM
  5. Financial Query
    By Luke in forum Queries
    Replies: 8
    Last Post: 06-30-2011, 12:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums