Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2012
    Posts
    2

    Unanswered: Need help figuring out how to do intervals with dates

    I am trying to calculate intervals using a list box that pulls from a table when the specific code is selected. What I mean is:

    A = 30 days in a table

    If "A" is selected in the list box then I want 30 days added to todays date and for that date to be displayed in a text box on a report. Is this possible to do?

    I was able to make it possible in excel using the following code:

    =IF(ISERROR(A4+(VLOOKUP(H2,$E$106:$F$138,1+1)))," ",A4+(VLOOKUP(H2,$E$106:$F$138,1+1)))

    now I know VLOOKUP is not in access 2007 but is there another way to do it?

    Thanks in advance!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use the date time functions inherent in Access

    in the report details on format event place some code which sets the value of an unbound control in the report detail

    lets say you have created a control called, say tbDueDate, plonk it on the report in the detail section you require
    then your code goes in tbDueDates on format event
    if avalue = "A" then
    tbduedate.text = DateAdd("d",30,mydatecolumn)
    tbduedate.visible = true
    else 'hide the control if there is no calculation required
    tbduedate.visible=false
    endif

    of course you could store the value of the list box in the data for this item and derive the date as required

    say you had a table called dateintervals
    with columns such as code, NoDays, description, althoiugh you could use NoDays as your index column and drop the code so you'd have a value for 0, 30 days

    you could then in your query create this value using an IIF construct

    select my, column, list, if(DateInterval>0, format(dateadd("D",dateinterval,mydatecolumn),"dd/mm/yyyy"),"") as TextDueDate from mytable
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2012
    Posts
    2
    Quote Originally Posted by healdem View Post
    in the report details on format event place some code which sets the value of an unbound control in the report detail

    lets say you have created a control called, say tbDueDate, plonk it on the report in the detail section you require
    then your code goes in tbDueDates on format event
    if avalue = "A" then
    tbduedate.text = DateAdd("d",30,mydatecolumn)
    tbduedate.visible = true
    else 'hide the control if there is no calculation required
    tbduedate.visible=false
    endif
    I am rather new at using access so I need a little clarification.
    Does the code above go in the event for the list box on update?
    Will that code update a text box on a report?
    and mydatecolumn refers to what?

    Thanks for your help!!

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    lets say you have created a control called, say tbDueDate, plonk it on the report in the detail section you require
    then your code goes in tbDueDates on format event
    if avalue = "A" then
    tbduedate.text = DateAdd("d",30,mydatecolumn)
    tbduedate.visible = true
    else 'hide the control if there is no calculation required
    tbduedate.visible=false
    endif
    mydatecolumn should be replaced with whatever you are deriving the date value from. eg if it was a column in your table called InvoiceDate then you'd use
    tbduedate.text = DateAdd("d",30,InvoiceDate)
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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