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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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?
 
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


Duane Hookom said:
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
--

MarkInSalemOR said:
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
Click to expand...
Click to expand...
 
Back
Top