conditional visible on continuous form

Question

I have a continuous form. I only want the contents of the WHO column to show IF the DATE field has a value.

I cannot evaluate it on a record by record basis, because when I put this in the control source of the WHO field, I lose the ability to sort or filter by the WHO field in the continuous form:   =IIf([txtDate]>0,[Who],"")

Any ideas?

Answer 1

well, that will not solve the problem of the built in function of sort/filter selection. So I will resolve it the way I was doing it before. Thanks anyway.

Answer 2

The only problem is that when someone tries to filter for exact matches, the  ones that appear "blank" will appear as well as the "unblank" ones.

Then you should include NOT NULL Dates in the expression, e.g.

Me.Filter = "[Who] = ""ZZZ"" And [Date] Is Not Null"

Me.FilterOn = True

Answer 3

BTW, DATE is a bad choice of name for a column (field) in a table as it is the name of a built in function, so as a 'reserved' word should not be used as an object name.  Something like TransactionDate or whatever is appropriate should be used.

1.  Thank you, Ken. I know DATE is reserved. I would love to get rid of it, but I inherited this database, and I am dealing with it the best I can.  DATE is so prevalent throughout the front end, I am a little afraid to tackle changing it.  Any suggestions?

Another way you can do this is by conditional formatting of the Who control in the form.  In the conditional formatting dialogue set the forecolor and backcolor of the control to the same colour on the  basis of the expression IsNull([txtDate])

The Who control will still be updatable with this solution, unlike the use of a computed column.

2. Thank you again. That's a good suggestion. The only problem is that when someone tries to filter for exact matches, the  ones that appear "blank" will appear as well as the "unblank" ones.

Answer 4

Another way you can do this is by conditional formatting of the Who control in the form.  In the conditional formatting dialogue set the forecolor and backcolor of the control to the same colour on the  basis of the expression IsNull([txtDate])

The Who control will still be updatable with this solution, unlike the use of a computed column.

BTW, DATE is a bad choice of name for a column (field) in a table as it is the name of a built in function, so as a 'reserved' word should not be used as an object name.  Something like TransactionDate or whatever is appropriate should be used.

Answer 5

I'd be inclined to create this column as a calculated field in the form's recordsource query, using an expression similar to the controlsource expression you tried:

    ShowWho: IIf([FollowupDate] Is Not Null, [Who], Null)

Then bind the "Who" text box on the form to the [ShowWho] field in the recordsource query.  (Of course, you may have to correct the field name "FollowupDate".)

It's not clear to me, though, whether you want to be able to sort/filter by the [Who] field even when ShowWho is Null (because the [FollowupDate] is Null).  If so, then unless you also present the Who field on the form, you won't readily be able to sort or filter by it.