Query Field Formatting

[Sparky]

I've tried to format a field in a query so that if a date is present it is formmatted as mm/dd/yyyy and shows in magenta and if the field is null (not yet populated) then it shows as blue. I've tried the following:

(mm/dd/yyyy hh:nn ampm)[Blue];@;"Sched"[Magenta]

but this shows date in blue properly formatted but nothing in the empty date fields.

If I use

[Blue];@;"Sched"[Magenta]

then I get the word Sched where the date is empty, but improper formatting for the date.



I would like to format these in the query so they will then show this way in a listbox. Currently, the formatting does no carry over to the listbox.

Thanks for any help.

[RuralGuy]

I think you will find that formatting is *always* ignored in a ListBox. Maybe this link will help: http://www.lebans.com/customlistboxver3.htm

[Sparky]

Thanks RuralGuy. I'd looked at Lebans before, but it seemed to me that he was creating a different control. I thought I had read that if the formatting was part of the query then it would carry over to the list box. So I was interested in what is the proper syntax for getting the formatting right. Seemed like I could get half what I wanted with one version of syntax,the other half with another version. Ideally, I'd like to know where the "rules" for this syntax are. I've often looked at VBA help, but only found a few examples, not the actual rules.

I've moved to creating the same form with a subform that is a continuous form with text boxes that I can format as I'd like. Still, I'd like to know if it can be done using formatting in the query and a listbox.

But, thank you for responding and for your reference.