Conditional expression

[eliasam]

Hi,

Anyone able to help me with this?

I am creating a query that includes YEAR and WKSLEFT, i would like to be able to determine the YRDUE by counting the amounts of WKSLEFT.

The range is:

Between 0 and 51 = year
Between 52 and 103 = year +1
Between 104 and 155 year +2

This is what i have tried to use but it only works for one condition, i dont know how to make the others work?

yeardue: IIf(([wksleft] Between '0' And '51'),[year],IIf(([wksleft] Between '52' And '103'),[year]+1))

can anyone help me with this please!?

[Jim Doherty]

Hi,

Anyone able to help me with this?

I am creating a query that includes YEAR and WKSLEFT, i would like to be able to determine the YRDUE by counting the amounts of WKSLEFT.

The range is:

Between 0 and 51 = year
Between 52 and 103 = year +1
Between 104 and 155 year +2

This is what i have tried to use but it only works for one condition, i dont know how to make the others work?

yeardue: IIf(([wksleft] Between '0' And '51'),[year],IIf(([wksleft] Between '52' And '103'),[year]+1))

can anyone help me with this please!?

Hi Eliasam,

Welcome to the scripts!

Yes that can look rather tiresome to the eye particularly if the combinations became more than that.

I don't know how familar you are with functions but the SELECT CASE construct within a 'function' that returns a value to the query would assist you. It certainly reads better and you can always widen its scope with ease.

Paste the functional code below into a module immediately below the Option Compare Database line and use it as a calculated column in your query like so

YearDue: fn_YrDue([year],[wksleft])

 
Function fn_YrDue(intYear As Integer, intWks As Integer) As Integer
    On Error Resume Next
    Select Case intWks
    Case 0 To 51
        fn_YrDue = intYear
    Case 52 To 103
        fn_YrDue = intYear + 1
    Case 105 To 114
        fn_YrDue = intYear + 2
    Case Else
        fn_YrDue = 0
    End Select
End Function
 

Please note the Select case construct above will always return a zero if the resultant calculation falls outside of the ranges you specify. You might wish to amend it to return 'actually' what you want

Regards

Jim :)

[FishVal]

[year] + Int([wksleft]/52)

Though, I believe DateDiff() function would be preferable.

[eliasam]

Thanks Jim

works just fine!

I should read up on functions to make it less tiresome for me

Thanks again!

Elias :)

Hi Eliasam,

Welcome to the scripts!

Yes that can look rather tiresome to the eye particularly if the combinations became more than that.

I don't know how familar you are with functions but the SELECT CASE construct within a 'function' that returns a value to the query would assist you. It certainly reads better and you can always widen its scope with ease.

Paste the functional code below into a module immediately below the Option Compare Database line and use it as a calculated column in your query like so

YearDue: fn_YrDue([year],[wksleft])

 
Function fn_YrDue(intYear As Integer, intWks As Integer) As Integer
    On Error Resume Next
    Select Case intWks
    Case 0 To 51
        fn_YrDue = intYear
    Case 52 To 103
        fn_YrDue = intYear + 1
    Case 105 To 114
        fn_YrDue = intYear + 2
    Case Else
        fn_YrDue = 0
    End Select
End Function
 

Please note the Select case construct above will always return a zero if the resultant calculation falls outside of the ranges you specify. You might wish to amend it to return 'actually' what you want

Regards

Jim :)