how change a Date to a category.

[ptiernan]

The problem I have is that I have to change a Date record into a category. The problem is for unemployment length, I have a form with a field for unemployment length but need to update another table with the length in the categories 0-6m, 6-12m, 12-18m, 18m-3y and 3y+.

I can get the form to calculate the number of months unemployed by using the DateDiff function. Ideally what I want is after the date is inputted another field on the form is update to show which category the unemployment length falls into.

Any help would be greatly appreciated. Thanking you in advance.

Much Thanks
Paul Tiernan

 

[xlbo]

If you are using Access, use the textbox lost focus event
and use something like
if testbox1.text = "" then exit sub
mNoMonths = DateDiff("m", Now, textbox1.value)
Select case mNoMonths
case <6
res = &quot;0-6m&quot;
case >=6 and <12
res = &quot;6-12m&quot;
case >=12 and <18
res = &quot;12-18m&quot;
case >=18 and <36
res = &quot;18m-3y&quot;
case else
res = &quot;3y+&quot;
end select
Textbox2.text = res
end sub

Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde