IIf in Control source and text box shows 12:00 or 12/30/1899

[Guest]

Ok, Thanks to the Ken and Steve who answered Dumb Blonde's question about iif
in control source on 12/8 you helped me eliminate #Error in the field.

I'm using Access 2000 SP3 on Windows 2000 and the table is on DB2.

Now how do I get the text box to show nothing when the value is null?
Currently I get 12:00:00 AM or 12/30/1899 and my head is getting sore from
banging my head on this one. I have verified the recordset is returning a
Null and not an empty string.

Control source is =IIf(nz([DT_SERVICE_DATE],"")="","",[DT_SERVICE_DATE])
and has been =IIf(nz([DT_SERVICE_DATE],"")="",Null,[DT_SERVICE_DATE])

In my last attempt the format was Short Date, I have tried "MM/DD/YYYY"
(which is what the box displayed) and nothing in the format field. mm/dd/yyyy
ends up showing 12/30/1899. When the format is "" I get a blank field when
the value is Null which is good, but I then get a blank field when there is a
value.

Input mask has been blank and I've tried this 99/99/0000;0;_ and "" as well.

And can anyone recommend a good book or website on learning Access reporting?

Thanks
Mark

 

[Duane Hookom]

Why is there an issue? It sounds like if there is no value in the field, you
don't want to show a value and if there is a value, you want to show the
value. Why would you not just set the control source to the field and make
sure the input mask field is empty?

 

[Guest]

Duanne, thanks for the quick answer. What you describe is how I think it
should work but it didn't. A little checking and I'm my own worst enemy I was
converting empty values to 0 when I was saving. ugh. I must have made a
mistake when I tested for nulls in the data.

Mark

Why is there an issue? It sounds like if there is no value in the field, you
don't want to show a value and if there is a value, you want to show the
value. Why would you not just set the control source to the field and make
sure the input mask field is empty?

--
Duane Hookom
MS Access MVP
--

Ok, Thanks to the Ken and Steve who answered Dumb Blonde's question about
iif
in control source on 12/8 you helped me eliminate #Error in the field.

I'm using Access 2000 SP3 on Windows 2000 and the table is on DB2.

Now how do I get the text box to show nothing when the value is null?
Currently I get 12:00:00 AM or 12/30/1899 and my head is getting sore from
banging my head on this one. I have verified the recordset is returning
a
Null and not an empty string.

Control source is =IIf(nz([DT_SERVICE_DATE],"")="","",[DT_SERVICE_DATE])
and has been =IIf(nz([DT_SERVICE_DATE],"")="",Null,[DT_SERVICE_DATE])

In my last attempt the format was Short Date, I have tried "MM/DD/YYYY"
(which is what the box displayed) and nothing in the format field.
mm/dd/yyyy
ends up showing 12/30/1899. When the format is "" I get a blank field when
the value is Null which is good, but I then get a blank field when there
is a
value.

Input mask has been blank and I've tried this 99/99/0000;0;_ and "" as
well.

And can anyone recommend a good book or website on learning Access
reporting?

Thanks
Mark