Insert

[Steve]

I have a table and query that use the date fields delay date, denial date.
In the report I would like to replace the date with the word denial or delay.
Thanks for your help.
Steve

 

[Duane Hookom]

I expect you only want the text to display if there actually is a date
present. I would not mess with the control source of the text box. Use the
Format property with values of either
Format: "Delay"
or
Format: "denial"

 

[Steve]

Thanks,
I am using only one colum to replace the date with one of three possible
responses.
I tried the formula:
=Iif(([CertificationDate],â€certâ€,0),Iif([DelayDate],â€delayâ€,0),Iif([DenialDate],â€denyâ€,0))
but when I saved it, the program put [] around the quoted words.

 

[Duane Hookom]

I assumed you had multiple text boxes. If a date is only allowed in one of
the fields then use my solution with transparent background text boxes that
are layered one on top of the other.

--
Duane Hookom
Microsoft Access MVP

Thanks,
I am using only one colum to replace the date with one of three possible
responses.
I tried the formula:
=Iif(([CertificationDate],â€certâ€,0),Iif([DelayDate],â€delayâ€,0),Iif([DenialDate],â€denyâ€,0))
but when I saved it, the program put [] around the quoted words.

I expect you only want the text to display if there actually is a date
present. I would not mess with the control source of the text box. Use the
Format property with values of either
Format: "Delay"
or
Format: "denial"

 

[Steve]

Thanks,

I also need to change the date to a word, either "delay", "deny", or
"certify".

I assumed you had multiple text boxes. If a date is only allowed in one of
the fields then use my solution with transparent background text boxes that
are layered one on top of the other.

--
Duane Hookom
Microsoft Access MVP

Thanks,
I am using only one colum to replace the date with one of three possible
responses.
I tried the formula:
=Iif(([CertificationDate],â€certâ€,0),Iif([DelayDate],â€delayâ€,0),Iif([DenialDate],â€denyâ€,0))
but when I saved it, the program put [] around the quoted words.

I expect you only want the text to display if there actually is a date
present. I would not mess with the control source of the text box. Use the
Format property with values of either
Format: "Delay"
or
Format: "denial"

--
Duane Hookom
Microsoft Access MVP


:

I have a table and query that use the date fields delay date, denial date.
In the report I would like to replace the date with the word denial or delay.
Thanks for your help.
Steve

 

[Duane Hookom]

I understand you want to display some text rather than the date. This is
exactly what my first reply to you does. Did you try it? Did you understand
it?

A date is a number. You can use the format property to display different
formats based on a numeric value being positive, negative, zero, or null. My
suggestion takes advantage of this functionality.

--
Duane Hookom
Microsoft Access MVP

Thanks,

I also need to change the date to a word, either "delay", "deny", or
"certify".

I assumed you had multiple text boxes. If a date is only allowed in one of
the fields then use my solution with transparent background text boxes that
are layered one on top of the other.

--
Duane Hookom
Microsoft Access MVP

Thanks,
I am using only one colum to replace the date with one of three possible
responses.
I tried the formula:
=Iif(([CertificationDate],â€certâ€,0),Iif([DelayDate],â€delayâ€,0),Iif([DenialDate],â€denyâ€,0))
but when I saved it, the program put [] around the quoted words.



:

I expect you only want the text to display if there actually is a date
present. I would not mess with the control source of the text box. Use the
Format property with values of either
Format: "Delay"
or
Format: "denial"

--
Duane Hookom
Microsoft Access MVP


:

I have a table and query that use the date fields delay date, denial date.
In the report I would like to replace the date with the word denial or delay.
Thanks for your help.
Steve

 

[Steve]

I think I have the idea of the transparent text boxes figured out.
All 3 are stacked and only the one that is true will be represented.
Ingenious...
Thanks

I understand you want to display some text rather than the date. This is
exactly what my first reply to you does. Did you try it? Did you understand
it?

A date is a number. You can use the format property to display different
formats based on a numeric value being positive, negative, zero, or null. My
suggestion takes advantage of this functionality.

--
Duane Hookom
Microsoft Access MVP

Thanks,

I also need to change the date to a word, either "delay", "deny", or
"certify".

I assumed you had multiple text boxes. If a date is only allowed in one of
the fields then use my solution with transparent background text boxes that
are layered one on top of the other.

--
Duane Hookom
Microsoft Access MVP


:

Thanks,
I am using only one colum to replace the date with one of three possible
responses.
I tried the formula:
=Iif(([CertificationDate],â€certâ€,0),Iif([DelayDate],â€delayâ€,0),Iif([DenialDate],â€denyâ€,0))
but when I saved it, the program put [] around the quoted words.



:

I expect you only want the text to display if there actually is a date
present. I would not mess with the control source of the text box. Use the
Format property with values of either
Format: "Delay"
or
Format: "denial"

--
Duane Hookom
Microsoft Access MVP


:

I have a table and query that use the date fields delay date, denial date.
In the report I would like to replace the date with the word denial or delay.
Thanks for your help.
Steve

 

[Evi]

Steve, can you explain something to me? I'm used to seeing IIF formulas as
arguments eg
IIF(IsNull([CertificationDate]),"Cert",0)
Or
(if CertificationDate is null then print Cert else print 0

IIF([MyTextBox]=[CertificationDate]), "Cert",0)
(if MyTextBox contains the same value as the CertificationDate field then
print Cert else print 0

but what does it mean if you just say

IIF([CertificationDate], "Cert",0?

Evi

 

[John Spencer]

Evi,

IIF(AnyNumberOtherThanZero, True, False) will return TRUE. It only return
false if the number is zero.

Or stated another way, TRUE is any number that is not zero. False is Zero.

The other part of this is that if the value is Null, an IIF statement will
return the false argument. And a date is stored in Access as a number and
can often be treated as a number.

Personnally, I would specifically test for the value being null as I find it
a lot clearer then the above method. In a query I would use
IIF({Certification Date] is not Null,"Cert",Null)

As a control source, I would use the VBA function IsNull(...)

Hope that helps
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Evi]

Thanks John, I didn't know you could use IIF that way but I will take your
recommendation and use Is Not Null or IsNull( ) otherwise I will totally
confuse myself when I look back at it.

Evi

Evi,

IIF(AnyNumberOtherThanZero, True, False) will return TRUE. It only return
false if the number is zero.

Or stated another way, TRUE is any number that is not zero. False is Zero.

The other part of this is that if the value is Null, an IIF statement will
return the false argument. And a date is stored in Access as a number and
can often be treated as a number.

Personnally, I would specifically test for the value being null as I find it
a lot clearer then the above method. In a query I would use
IIF({Certification Date] is not Null,"Cert",Null)

As a control source, I would use the VBA function IsNull(...)

Hope that helps
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Steve, can you explain something to me? I'm used to seeing IIF formulas as
arguments eg
IIF(IsNull([CertificationDate]),"Cert",0)
Or
(if CertificationDate is null then print Cert else print 0

IIF([MyTextBox]=[CertificationDate]), "Cert",0)
(if MyTextBox contains the same value as the CertificationDate field then
print Cert else print 0

but what does it mean if you just say

IIF([CertificationDate], "Cert",0)?

Evi