Defaulting Blank Fields to "------"

[Mara]

I have date fields that show up on various reports. To make the reports
easier on the eye, I would like empty fields to be automatically filled with
dashmarks ("------"). I tried using the IFF function in Expression Builder
for the Field's Control Source , but perhaps I am not doing it right.

I cannot change the default for the field in the table because Access will
not let me use a non date default for a date fields. Besides, it is only in
certain reports where users want to see the dases if the date is blank.

I have tried using the following formula for a field called DateApproved:

=IIF([DateApproved]="","--------",=DateApproved)

If there is a value in Date Approved, I want the report to show the date
value. If there the field is null, I want it to show the dashmarks. Can
this be done? How so?

Thank you so much for your help.

I

 

[Duane Hookom]

You should be able to simply set the Format property to display ------- for
all null values.
Format: dd-mmm-yyyy;;;-----------

 

[Jasonm]

Mara, I think you are close try this:
=IIF(isnull([DateApproved]),"--------",[DateApproved])

Alternately I have also used: =nz([DateApproved],"--------")

It is a little cleaner, and I sure that someone else may even have a simpler
way yet...

Hope it helps.

Jm

 

[fredg]

I have date fields that show up on various reports. To make the reports
easier on the eye, I would like empty fields to be automatically filled with
dashmarks ("------"). I tried using the IFF function in Expression Builder
for the Field's Control Source , but perhaps I am not doing it right.

I cannot change the default for the field in the table because Access will
not let me use a non date default for a date fields. Besides, it is only in
certain reports where users want to see the dases if the date is blank.

I have tried using the following formula for a field called DateApproved:

=IIF([DateApproved]="","--------",=DateApproved)

If there is a value in Date Approved, I want the report to show the date
value. If there the field is null, I want it to show the dashmarks. Can
this be done? How so?

Thank you so much for your help.

I

=IIf(IsNull([DateApproved]),"--------",[DateApproved])

 

[Mara]

Hi Jason,

Thank your for your reply. I tried both formula's in the field in the
Control Source area of the Properties using the Build feature.

I keep getting the same error message of Invalid Control Source. Is the
Control Source the appropriate place to use the formula?

Mara, I think you are close try this:
=IIF(isnull([DateApproved]),"--------",[DateApproved])

Alternately I have also used: =nz([DateApproved],"--------")

It is a little cleaner, and I sure that someone else may even have a simpler
way yet...

Hope it helps.

Jm

I have date fields that show up on various reports. To make the reports
easier on the eye, I would like empty fields to be automatically filled
with
dashmarks ("------"). I tried using the IFF function in Expression
Builder
for the Field's Control Source , but perhaps I am not doing it right.

I cannot change the default for the field in the table because Access will
not let me use a non date default for a date fields. Besides, it is only
in
certain reports where users want to see the dases if the date is blank.

I have tried using the following formula for a field called DateApproved:

=IIF([DateApproved]="","--------",=DateApproved)

If there is a value in Date Approved, I want the report to show the date
value. If there the field is null, I want it to show the dashmarks. Can
this be done? How so?

Thank you so much for your help.

I

 

[Mara]

Hi Duane,

Thank you. That did work! If I am using Count functions based on this
field, will the ---------- be counted or will the record only be counted if
there is a date in the field?

Thanks again!

You should be able to simply set the Format property to display ------- for
all null values.
Format: dd-mmm-yyyy;;;-----------

--
Duane Hookom
Microsoft Access MVP

I have date fields that show up on various reports. To make the reports
easier on the eye, I would like empty fields to be automatically filled with
dashmarks ("------"). I tried using the IFF function in Expression Builder
for the Field's Control Source , but perhaps I am not doing it right.

I cannot change the default for the field in the table because Access will
not let me use a non date default for a date fields. Besides, it is only in
certain reports where users want to see the dases if the date is blank.

I have tried using the following formula for a field called DateApproved:

=IIF([DateApproved]="","--------",=DateApproved)

If there is a value in Date Approved, I want the report to show the date
value. If there the field is null, I want it to show the dashmarks. Can
this be done? How so?

Thank you so much for your help.

I

 

[Mara]

Nevermind. I see that I can change the field format in the report and I do
not have to make that change in the table where the field resides. Thank you!

Hi Duane,

Thank you. That did work! If I am using Count functions based on this
field, will the ---------- be counted or will the record only be counted if
there is a date in the field?

Thanks again!

You should be able to simply set the Format property to display ------- for
all null values.
Format: dd-mmm-yyyy;;;-----------

--
Duane Hookom
Microsoft Access MVP

I have date fields that show up on various reports. To make the reports
easier on the eye, I would like empty fields to be automatically filled with
dashmarks ("------"). I tried using the IFF function in Expression Builder
for the Field's Control Source , but perhaps I am not doing it right.

I cannot change the default for the field in the table because Access will
not let me use a non date default for a date fields. Besides, it is only in
certain reports where users want to see the dases if the date is blank.

I have tried using the following formula for a field called DateApproved:

=IIF([DateApproved]="","--------",=DateApproved)

If there is a value in Date Approved, I want the report to show the date
value. If there the field is null, I want it to show the dashmarks. Can
this be done? How so?

Thank you so much for your help.

I