Solved

Access 2003 Syntax for empty text box?

Posted on 2006-07-11
4
430 Views
Last Modified: 2012-08-14
Hi All,
I have to following in a text box in a report in Access 2003:

="Revised Date" & " " & Format([crpProduct_RevisedDate],"mmmm dd"", ""yyyy")

What I want to be able to do though is if this date values is empty don't show the text box on the report other wise if it does contain a date show its value. I was wondering if there is syntax like an if statement i can put in the control source to do this or do I have to set visible and not visible for the text box in the code behind the report?
Which is the best way? I would prefer to do it in the control source but not too sure of the syntax. In the code behind i have something similar done already.
Thanks again in advance for all the help.
0
Comment
Question by:sabrina_spillane
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 17082710
U can use IsNull

if IsNull(crpProduct_RevisedDate)=False then
  xx= ="Revised Date" & " " & Format([crpProduct_RevisedDate],"mmmm dd"", ""yyyy")
else
  msgbox "Isnull"
end if
0
 
LVL 18

Accepted Solution

by:
Data-Man earned 150 total points
ID: 17082716
How about this

= IIF(ISNULL([crpProduct_RevisedDate]),"","Revised Date" & " " & Format([crpProduct_RevisedDate],"mmmm dd"", ""yyyy"))


HTH,
Mike
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 100 total points
ID: 17082723
If u want this in the control source, u can do this

IIF(IsNull(crpProduct_RevisedDate),'',="Revised Date" & " " & Format([crpProduct_RevisedDate],"mmmm dd"", ""yyyy"))
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 17082818
>>>>>What I want to be able to do though is if this date values is empty don't show the text box on the report <<<<

Assuming your text box is located in the Detail section of your report you can use the OnFormat event of the Detail section as follows ...

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  If IsNull(Me.crpProduct_RevisedDate) Then
      Me.MyTxtBox.Visible = False
  Else
      Me.MyTxtBox.Visible = True
  End If
End Sub

ET
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

734 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