# Thread: Need help figuring out how to do intervals with dates

1. Registered User
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?

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
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.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

3. Registered User
Join Date
Feb 2012
Posts
2
Originally Posted by healdem
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.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?

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
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.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