autofill in query

[Radhika]

I have a query that calculates a diff in the number of days. There are two
field, 'Visit date' and 'End Date'. In a field called 'TimeFrame(days)'The
query calcuates the time between visit date and end date in days. However, i
am trying to add an autofill function that autopopulates "<1-3 months" if
'TimeFrame(days)' has a number between 30 and 90 and "3-6 mothns" if
'TimeFrame(days)' has a number between 90 and 120 days. How can I go about
doing this in my query?

Thankyou

 

[vbasean]

You could create a function in a public module and then add it to the
query

create a module


add the folowing to the module code

Public Function TimeFrame(DayCount as integer) as string
Select Case DayCount
Case >29 And <91
TimeFrame = "<1-3 months"
Case >90 And <121
TimeFrame = "3-6 mothns"
Case Else
TimeFrame = "Out of Range"
End Select
End Function

save the module

add this field to your query

TimeFrame: TimeFrame([field to calculate])

I think that will do it.

 

[KARL DEWEY]

You can use nested IIF statements.
IIF(DateDiff("d", [Visit date], [End Date]) <30, "Under 1 month",
IIF(DateDiff("d", [Visit date], [End Date]) Between 30 And 90, "1-3 months",
IIF(DateDiff("d", [Visit date], [End Date]) Between 90 And 120, "3-6 mothns",
">6 months")))