age calculations

[rarredon]

I have a Child care program that i want it to figure the cost per child based
on age in months. I use
DateDiff("m",[Birthdate],Now())+Int(Format(Now(),"mmdd")<Format([Birthdate],"mmdd")),
to figure the age in months. How do I set it to post this cost scheme. 0-18
months = $565.00; 19-36 months = $525.00; <37 months = $490.00

 

[fredg]

I have a Child care program that i want it to figure the cost per child based
on age in months. I use
DateDiff("m",[Birthdate],Now())+Int(Format(Now(),"mmdd")<Format([Birthdate],"mmdd")),
to figure the age in months. How do I set it to post this cost scheme. 0-18
months = $565.00; 19-36 months = $525.00; <37 months = $490.00

Where? In a query?
Charge:
IIf(DateDiff("m",[Adate],Now())+Int(Format(Now(),"mmdd")<Format([Adate],"mmdd"))
Between 0 And 18, 565,
IIf(DateDiff("m",[Adate],Now())+Int(Format(Now(),"mmdd")<Format([Adate],"mmdd"))
Between 19 And 36, 525,
IIf(DateDiff("m",[Adate],Now())+Int(Format(Now(),"mmdd")<Format([Adate],"mmdd"))>37,490)))

Directly in an unbound control on a form or report?

=IIf(DateDiff("m",[Adate],Now())+Int(Format(Now(),"mmdd")<Format([Adate],"mmdd"))
Between 0 And
18,565,IIf(DateDiff("m",[Adate],Now())+Int(Format(Now(),"mmdd")<Format([Adate],"mmdd"))
Between 19 And
36,525,IIf(DateDiff("m",[Adate],Now())+Int(Format(Now(),"mmdd")<Format([Adate],"mmdd"))>37,400)))

Change my [ADate] field name to [Birthdate].

Note: It's best not to use Now() in calculations like this. Now()
includes a time value, and while it won't affect this calculation, at
some point if used without knowing it's pitfalls, it may return
different results, depending upon the time of day the expression is
run. Use Date() whenever the time of day is not needed.

 

[Linq Adams via AccessMonster.com]

I assume you actually meant > 37 months = $490.00 not < 37 months. I've put
this code in the AfterUpdate event of the Birthday field, but you could, of
course, do it in another event. Also, this assigns the results to a string
field named ChildCareCost, but you could do other things here, if need be,
such as using the value of AgeInMonths in a calculation. Just make sure to
assign the value, i.e $565.00, to the correct type of datatype for your
purposes.

Private Sub Birthdate_AfterUpdate()

AgeInMonths = DateDiff("m", [Birthdate], Now()) + Int(Format(Now(), "mmdd") <
Format([Birthdate], "mmdd"))

Select Case AgeInMonths
Case 0 To 18
Me.ChildCareCost = "$565.00"

Case 19 To 36
Me.ChildCareCost = "$525.00"

Case Else
Me.ChildCareCost = "$490.00"
End Select
End Sub