Display alternate text if date is blank (1 Viewer)

PuzzledNH

Registered User.
Local time
Today, 06:57
Joined
Nov 4, 2019
Messages
36
I have a simple report that displays a number of fields. I need to modify one date field: RMAReceiptDate and am unsure how to do this.

When an item is received the date is entered in RMAReceiptDate. This date is properly displayed on the report. However, if an item has not been received, there is no data in the table to display and the report field is blank. Instead of a blank space, I would like the report to state Not Received.


I tried entering NZ ([RMAReceiptDate], "Not Received") in the control source on the property sheet. The report displays #Size! instead of Not Received and I see a invalid control property circular reference error asterisk next to the field in design view.

I also tried using conditional formatting rule with the following "Expression": NZ ([RMAReceiptDate], "Not Received") However, this is also not working.

Any suggestions on what I am doing wrong?
Thank you.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:57
Joined
Oct 29, 2018
Messages
21,357
Hi. A "circular reference" error could mean you tried to use the same alias as a field's name in your calculation. For example, did you do something like this?
Code:
[B]RMAReceiptDate:[/B] NZ([RMAReceiptDate], "Not Received")
If so, try changing the alias name.
 

Micron

AWF VIP
Local time
Today, 06:57
Joined
Oct 20, 2018
Messages
3,476
If the date field is of the date data type you can't store text in it regardless of what you try. Not sure, but that might be related to the Size# error. If the field type is date and this is just for display, you can hide the date field (which I assume is bound) and show the text field (unbound) on the report. Make the text field = an expression that returns either the date or some text.
 

PuzzledNH

Registered User.
Local time
Today, 06:57
Joined
Nov 4, 2019
Messages
36
If the date field is of the date data type you can't store text in it regardless of what you try. Not sure, but that might be related to the Size# error. If the field type is date and this is just for display, you can hide the date field (which I assume is bound) and show the text field (unbound) on the report. Make the text field = an expression that returns either the date or some text.

This was the solution, Thank you.
The ([RMAReceiptDate] field is a "short date" data type field which I was directly using in the report.
I deleted the ([RMAReceiptDate] field from the report, inserted a new text box, changed the control source of the new text box to reference NZ ([RMAReceiptDate], "Not Received"), and it works properly now.

Thank you again.
 

Micron

AWF VIP
Local time
Today, 06:57
Joined
Oct 20, 2018
Messages
3,476
That's even better seeing as how the date field is part of the record source, but I wasn't sure if the field was bound. Glad to have helped.
 

isladogs

MVP / VIP
Local time
Today, 10:57
Joined
Jan 14, 2017
Messages
18,186
Just for info, there is another way of achieving this result using field formatting as explained in post #2 of this thread https://www.access-programmers.co.uk/forums/showthread.php?t=297923

For example I have formatted the two date fields here as dd/mm/yyyy;;;"Enter a date". As a result that text appears wherever no date has been entered but correctly shows any entered date



So the OP could have achieved the same result using "Not received" as the last argument and used a bound form control. This approach works in tables, queries, forms and reports
 

Attachments

  • Capture.PNG
    Capture.PNG
    7.3 KB · Views: 175

zeroaccess

Active member
Local time
Today, 05:57
Joined
Jan 30, 2020
Messages
671
Just for info, there is another way of achieving this result using field formatting as explained in post #2 of this thread https://www.access-programmers.co.uk/forums/showthread.php?t=297923

For example I have formatted the two date fields here as dd/mm/yyyy;;;"Enter a date". As a result that text appears wherever no date has been entered but correctly shows any entered date



So the OP could have achieved the same result using "Not received" as the last argument and used a bound form control. This approach works in tables, queries, forms and reports
Is "Enter a date" used as display-only or is it actually stored text? Hopefully the former. I prefer to disallow zero-length in most fields and use an expression to display something if desired when a field is blank.
 

isladogs

MVP / VIP
Local time
Today, 10:57
Joined
Jan 14, 2017
Messages
18,186
Like any type of formatting, its display only
 

Users who are viewing this thread

Top Bottom