format blank field date field on report in MS Access 2007

Posted on 2011-04-25
Last Modified: 2012-06-22
My Access 2007 report has a date field.  The string date is in the database as 20110101.  I can successfully format it in the report using:
=Mid$([STOP_DATE],5,2) & "/" & Mid$([STOP_DATE],7,2) & "/" & Mid$([STOP_DATE],3,2)

If the field is blank it gives #error.  What syntax can I use to incorporate the "=Mid$([STOP_DATE],5,2) & "/" & Mid$([STOP_DATE],7,2) & "/" & Mid$([STOP_DATE],3,2)" and something like "If isNull[STOP_DATE] then [STOP_DATE] = ""

Thank you,
Question by:LindaOKSTATE
    LVL 13

    Accepted Solution

    Put this in your textbox:

    =iif(isnull([stop_date]),null,Mid$([STOP_DATE],5,2) & "/" & Mid$([STOP_DATE],7,2) & "/" & Mid$([STOP_DATE],3,2))

    Author Closing Comment

    exactly what I needed

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now