If Not Null

[jojo86]

Hi

Is there a query that first finds out whether a field is null that then goes in to more If functions?

This is what I have at the moment:

If mixed = "town centre" then
   mixed_ratio = "25%"
else
   If mixed = "employment" then
      mixed_ratio = "100%"
   else
      mixed_ratio = "75%"
   end if
end if

this code works fine, but if delete what is in the mixed field, it automatically places 75% in the mixed_ratio field, which is not what I want. What I want is for the mixed_ratio field to be null if the mixed field is null.

I have tried putting an If Function at the beginning that says If mixed = Not Null then do this and this etc etc and finish it with mixed_ratio = Null but it doesn't seem to work and also stops the rest of the If Functions happening.

Anyone got any ideas?

 

[KeithG]

If mixed dosen't equal town centre or employement the value is always going to come out to 75%. Are you sure the field is actual null and dosen't conatain a zero length string? Try the below code

if isnull(Mixed)=false and len(trim(mixed))>1 then
   If mixed = "town centre" then
      mixed_ratio = "25%"
   else
      If mixed = "employment" then
         mixed_ratio = "100%"
      else
         mixed_ratio = "75%"
      end if
   End if
end if

 

[jojo86]

There are 5 scenarios that can come out of mixed - housing, transport and government is 75%, town centre is 25% and employment is 100%. But when nothing is picked in the null field, i don't want anything to be placed in the mixed_ratio field.

 

[KeithG]

You could use a select case statement like below

Select case mixed
     case is ="Town Centre"
          mixed_Ratio="25%"
     case is ="Employment"
          mixed_Ratio="100%"
     case is ="Housing"
          mixed_Ratio="75%"
     case is ="Transport"
          mixed_Ratio="75%"
     case is ="Government"
          mixed_Ratio="75%"
End Select

 

[jojo86]

That's worked, thanks

 

[KeithG]

Glad I could help. You should add to my reputation, I only need one more point to earn another green square.

 

[tehNellie]

Stat *****!