odd situation with dates

[gls858]

I import info from a program that fills the last purchase date field with
12:00AM if we've never sold anything to that customer. When I print this
field on a Access report it prints 12/30/1899. I would just like it to be
blank. I tried an IIF statement in the control source of the field.
=IIf([LastPurchaseDate]=#12/30/1899#,"",[LastPurchaseDate]), when I clicked
out off the the control source field Access automatically changed it to
this =IIf([LastPurchaseDate]=#12:00:00 AM#,"",[LastPurchaseDate])
I get a message that's this is a circular reference.
What do I need to do to get a blank field when the value is 12:00AM?

TIA
gls858

 

[Douglas J. Steele]

Presumably your text box is named the same as the field to which it's bound.
Try renaming the control to, say, txtLastPurchaseDate. That should remove
the circular reference.

If you've never sold anything to the customer, I'd recommend storing Null,
rather than 12:00 AM. In that way, you can use Nz([LastPuchaseDate], "")

 

[gls858]

Presumably your text box is named the same as the field to which it's bound.
Try renaming the control to, say, txtLastPurchaseDate. That should remove
the circular reference.

Thanks Doug. Worked perfectly.

If you've never sold anything to the customer, I'd recommend storing Null,
rather than 12:00 AM. In that way, you can use Nz([LastPuchaseDate], "")

I would make the same recommendation. Unfortunately I
don't have any control over the programming for the program that tracks
our sales. It's a program we purchased specifically designed for the
office supply industry.


gls858