Form code to query

[eb1mom]

I have this code in a form, it calculates how many vacation
hours an employee has avaliable. Wtime is datediff between
hired date and today. I am trying to rework this code and
use in a query for same result. I have Wtime field in query
working fine but, getting the syntax correct for the
lenghty vacation hours field is not going well. Syntax help
would be appreciated. thank you

If WTime >= 1 And WTime < 3 Then
Me.vacationhours = 80
End If
If WTime >= 3 And WTime < 5 Then
Me.vacationhours = 100
End If
If WTime >= 5 And WTime < 10 Then
Me.vacationhours = 120
End If
If WTime >= 10 And WTime < 15 Then
Me.vacationhours = 136
End If
If WTime > 15 Then
Me.vacationhours = 160

 

[Nikos Yannacopoulos]

Paste this function in a general module:

Function Vacation_Hours(DateHired As Date) As Long
Dim WTime As Long
WTime = DateDiff("m", DateHired, Date)
Select Case WTime
Case 1 To 2
Vacation_Hours = 80
Case 3 To 4
Vacation_Hours = 100
Case 5 To 9
Vacation_Hours = 120
Case 10 To 14
Vacation_Hours = 136
Case Is > 15
Vacation_Hours = 160
End Select
End Function

Then in your query design, add a calculated field like:

VacHours: Vacation_Hours([HireDate])

where I have assumed the date hired field to be called HireDate; change
to the actual field name.

HTH,
Nikos

 

[Van T. Dinh]

You cannot use VBA "If ... Else ... End If" statement in queries.

The equivalent that you can use in Query is the IIf() function (IIf =
Immediate If) but it will be very messy for the different alternative you
have.

The neater way is to use the Switch() function.

Alternatively, you can write a UDF (VBA User-Defined Function where you can
use the If statement) to calculate the VacationHours and use this UDF in the
Query.

Check Access VB Help on IIf() and Switch() function.

 

[eb1mom]

Thank-you for your help. Nikos the code works great and Van
you have given me many things to study. I appreciate you
taking the time to reply to my post.