Text based on drop down fields.

[mattkbishop]

I have a drop down menu, that puts the selected data into the field "Tier".

When I select something from the drop down "Either Tier 1,2 or 3" some relating text is shown and stored in field "TierInfo" - basically cos that's the only way I could do it. The code I used (on AfterUpdate) of the drop down menu is:

If Tier = Null Then
TierInfo = Null

ElseIf Tier = "Tier 1" Then
TierInfo = "Tier 1 - Universal Comprehensive Services"

ElseIf Tier = "Tier 2" Then
TierInfo = "Tier 2 - Targeted Preventative Services"

ElseIf Tier = "Tier 3" Then
TierInfo = "Tier 3 - Intensive Preventative Services"

End If

Now that works fine, but if I delete the data from the field that the drop down puts the info in, that is blank, except the TierInfo field still has the related data stored in it. I thought the:

If Tier = Null Then
TierInfo = Null

Would solve that, but it didn't.

How can I get this to work when the info is deleted please?

 

[Robert Dunstan]

You need to test for a zero length string:

If Tier = "" Then
TierInfo = ""
End if

Also have a look at using the Nz function

 

[mattkbishop]

Also have a look at using the Nz function

How do I do that?

 

[neileg]

Look up Nz in Access Help. This function will convert a null field to whatever you want, and makesure you have a consitent result to test for.