Vba Query Field

[DyerQ]

Hello,

I need to use a Vba expresion in a query calc field.
Presently I use the expression below to determine the shift
grouping in a query for my report but I now have 3 shifts.

Shift: IIf([Team Number]>199,"2","1&quot

I need to use a Vba expression -If Then Else -
so that If [Team Number] < 199 Shift =1
and If [Team Number] is > 199 Shift = 2 and if [Team Number] is > 299 Shift = 3

I can't find a clear example in the help can you help.

Thanks,
Dyer

 

[KenReay]

Hi

I need to use a Vba expression -If Then Else -
so that If [Team Number] < 199 Shift =1
and If [Team Number] is > 199 Shift = 2 and if [Team Number] is > 299 Shift = 3
This could still be done with the immediate IF so:

=IIF([Team Number]>299,3,IIF([Team Number]>199,2,1))

but it becomes a bit unwieldy

You could write a user function to do it

in the query put Team:=MyFunc([Team Number])

in a module put

Public Function MyFunc(strTeamNumber) As String
Select Case strTeamNumber
Case > 299
MyFunc = 3
Case > 199
MyFunc = 2
Case Else
MyFunc = 1
End Select
End Function

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK