Possibly to fill textbox with text if it is null?

[Sketchin]

I am trying to use this expression in my textbox to show when the textbox value is null:

=IIf(IsNull([ContractStatus]),"Null",[ContractStatus])

It gives me a circular reference error (#error in the textbox). If I change it to this:

=IIf(IsNull([ContractStatus]),"Null","ContractStatus"), it will display the text "Contract Status" (obviously).

What am I overlooking here? Does this need to be done in VBA?

 

[pbaldy]

Try changing the name of the textbox to something else, like txtContractStatus. Watch out when you do that as sometimes Access will change the formula.

 

[namliam]

Try using NZ

http://office.microsoft.com/en-us/access-help/nz-function-HA001228890.aspx

 

[vbaInet]

I'm thinking it's probably not Null then. It's the empty string "". So test for vbNullString:

=IIf([ContractStatus] = vbNullString,"Null",[ContractStatus])

If it returns the right result, then I would suggest a data cleansing exercise to convert your empty strings to Null and changing the Allow Zero Length property of your field to No (from within the table properties).

Otherwise you'll have to use the Nz([ContractStatus]),"") function from namlaim.

 

[BlueIshDan]

Learned this one from paul:

check for null and vbEmptyString

=IIf(Len([ContractStatus] & vbNullString) = 0,"Null",[ContractStatus])

 

[pbaldy]

I suspect that vbNullString will only work in VBA code. In this instance you probably need "".

 

[vbaInet]

Oops, completely forgot about that one. Cheers Paul.

 

[Sketchin]

Paul for the win! Totally overlooked the textbox name itself...normally I use the txtTextboxName convention, but this was a subreport so I overlooked it.

Thanks for the help everyone.

 

[pbaldy]

Happy to help! Pet peeve of mine that by default Access names the control the same as the field, and then barfs on it in certain circumstances.

 

[vbaInet]

Pet peeve of mine that by default Access names the control the same as the field, and then barfs on it in certain circumstances.

You're not alone on that one. It should disambiguate between control and field names by prefixing it with something. You would expect that newer versions would have something like this in place.