Results 1 to 4 of 4
  1. #1
    MJays is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    6

    How to write if Null enter text, otherwise enter a certain date format

    Hi all,

    We have another query please. Cottonshirt, are you still around?

    In a report, if a value (date) is null, how do we write something like IIf Null, then enter the words "No Date Recorded", otherwise, enter dd mmmm", "yyyy

    We've been trying to do it in the Property Sheet for a particular group under Format, but we really don't know what we're doing.

    Any help would be greatly appreciated.



    Rgds MJ

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    I assume that the "date" value is a field in the reports Record Source query.

    Make your date field a calculated field with something like:

    Code:
    DField: IIf([DateField] Is Null,"No Value",[DateField])
    B T W "Date" is not a good name for a field because Date is a Reserved word.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    Cottonshirt is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Feb 2018
    Posts
    116
    Quote Originally Posted by MJays View Post
    Hi all,
    In a report, if a value (date) is null, how do we write something like IIf Null, then enter the words "No Date Recorded", otherwise, enter dd mmmm", "yyyy

    1. you shouldn't be having this kind of problem at report writing level. the best way to construct a report is to first get your output into one or more queries, arranged exactly the way you want them, then just load the queries into the report. it is way easier to fix problems in queries, to sort queries, to eliminate null values in queries, to set criteria in queries than it is to do any of those things in reports. don't think of a report as way to process data, but as a way to present data you have previously processed.

    we assume your date field is called [valuedate] (note, as Bob Fitz said, it is not a good idea to have a field or control called simply "date", add something to it, like paydate, or birthdate, or whatever).

    2. the IsNull() function tests for a null value, and returns True if it is Null, otherwise False. so IsNull([valuedate]) is True when the date is null.

    3. in a query, the expression:

    Code:
    IIF(IsNull(valuedate]), "No Date Recorded", Format([valuedate], "dd mmmm yyyy"))

    many thanks,


    Cottonshirt

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if you want to use the format property so the value of your date remains viable as a date, the format property would be

    dd mmmm yyyy;;;"No date recorded"

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 09-01-2017, 10:28 AM
  2. Replies: 4
    Last Post: 03-11-2014, 06:38 PM
  3. Replies: 8
    Last Post: 10-30-2013, 02:28 PM
  4. Field auto enter new line on enter event
    By Ruegen in forum Forms
    Replies: 3
    Last Post: 09-17-2013, 09:00 PM
  5. Replies: 5
    Last Post: 02-06-2011, 04:32 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums