Form text box syntax - expression help

[fraser_lindsay]

Hi,

I have an unbound text box which I am trying to get to return a value based on other text boxes. This is my expression:

=IIf([F3]<=4,10,IIf([F3]>=15,30,IIf([F3]=Null,0,20)))


[F3] is the field.

Problem is I cannot get it to retun "10" if the F3 has no value. F3 may not have a vlue depending on other fields within the form.

I have tried "Null" but it isn't working.

Can anyone help please?

Thanks

 

[WIS]

Hi,

I have an unbound text box which I am trying to get to return a value based on other text boxes. This is my expression:

=IIf([F3]<=4,10,IIf([F3]>=15,30,IIf([F3]=Null,0,20)))


[F3] is the field.

Problem is I cannot get it to retun "10" if the F3 has no value. F3 may not have a vlue depending on other fields within the form.

I have tried "Null" but it isn't working.

Can anyone help please?

Thanks

The above is pretty vague, but do you have 0 or "" as any defaults in the F3 fild.

 

[fraser_lindsay]

Sorry - I meant my expression should return a "0" if the F3 textbox is empty/null, not 10, like I have written.

I tried setting the default value of the text box to 0 but that didn't seem to do anything.

At the moment if [F3] has no value the text box returns 20, as it thinks the cell value is neither below 4 or above 15.

 

[DCrake]

This is what you are saying
=IIf([F3]<=4,10,IIf([F3]>=15,30,IIf([F3]=Null,0,20)))

If F3 is less than or equal to 4 then n = 10 otherwise if F3 is greater than or equal to 15 then n = 30 otherwise if f3 is null then n = 0 otherwise n = 20

Null = 0
1 to 4 = 10
5 to 14 = 20
15+ = 30

I would wrap a function around it to make it more efficient

Function Test(AnyVal As Integer) As Integer
    Select Case AnyVal
        Case 0: Test = 0
        Case 1 To 4: Test = 10
        Case 5 To 14: Test = 20
        Case Is > 15: Test = 30
    End Select

End Function

Then in your control source for your textbox you would code

=Test(Nz([F3],0))

The Nz() takes care of any null values found.

David

 

[fraser_lindsay]

David,

Fantastic! Works perfectly.

Thanks for your help, that's another little skill learned for the day.

Fraser

 

[DCrake]

Glad to be of help. If you learn at least one thing in a day then that makes the day worth while