Solved

Date field conditional formatting?

Posted on 2012-03-12
9
230 Views
Last Modified: 2012-03-12
I have a report based on a query in which has a date field.  If there is no date I would like the report to display 'None' in the date field.  How best to do this?  Not sure how to write a conditional rule expression if this is the way to go.

Thanks in advance...
0
Comment
Question by:zpotok
9 Comments
 
LVL 9

Expert Comment

by:TonyReba
ID: 37709980
Couldnt you just set 'none' as the fields default value?
0
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
ID: 37710024
You can change field's value in query or in report from
MyDateField
to:
IIF(isnull(MyDateField),"None", MyDateFIeld)
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 37710065
Better wrap the field name with a Format expression, so you have String values for both cases of the Nz function.  You could do this in the report's record source query, which is easier to tweak until it comes out right.  Then just place the calculated field on the report.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37710183
I like Helen's approach...


...and sometimes a date field is really a text field, so you may want to go further and do this in VBA where you can add validation, error handling, ...etc;

If YourDate="" Or isnull(YourDate) then
   me.txtYourDate="None"
Else
    me.txtYourdate=YourDate
end if

Note here that the report control will not have a control source, and that the code sometimes references the field and sometimes the control...


However, this will only work if the report is opened in Print Preview specifically...
Perhaps not a big deal, ...just mentioning it.


But again, the other Experts suggestions first.

:-)

Jeff
0
 

Author Comment

by:zpotok
ID: 37711156
I used
IIf(IsNull(MedicalCertExpireDate),"None",MedicalCertExpireDate)
in query but get 'data type mismatch in criteria expression.



Don't understand Helen's answer...
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37711320
You did not explain *How* you tried to use it in a query...?

In the SQL it would be something like this:
SELECT Field1, Field2, IIf(IsNull(MedicalCertExpireDate),"None",MedicalCertExpireDate) AS DateDetermination
FROM Your Table.

In the query grid you would create a new column and use this as the Field:
DateDetermination:IIf(IsNull(MedicalCertExpireDate),"None",MedicalCertExpireDate)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37711333
This works just fine for me...
Database95.mdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37711340
If my clarifications helped, remember that this was originally posted by als315, so he should get all the points...

Jeff
0
 

Author Closing Comment

by:zpotok
ID: 37711789
Thanks for your help and thanks Jeff for the clarification.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question