Maybe the date that inventory was first acquired by the organization (or some other date before any known items)? Yep, it's not accurate, but if someone is going through old invoices, it could serve as a flag of the unknowns? All unknowns have the same date until known (if ever). Time for a fire sale?

The other option for "unknown" is not allowed for dates or numbers, that is a "Zero Length String" (ZLS).

You could have a DateAcquiredUnknown Yes/No field.
In your form if the value is True you could control the Visible or Enabled properties and/or set a different Backcolor for the field.

Good question. There are a couple of things you could do.

You could make another field indicating that you don't have a date, "DateUnknown" y/n.

OR... you could use a date that's impossible... either way in the past or in the future. Like 1900-01-01 or 2100-01-01.

You could even make a button on the form saying "Unknown" which would set the field to 1900-01-01 and then use an event to have it say "Unknown" instead of a date there. (try CF, if not, a hidden box)

I'll talk about this in a future TechHelp video for you.

Yes, I know that by using 2100-01-01 that I'm setting future generations up for a Y2100 problem.

THAT'S THEIR PROBLEM!!!!!

Which is what I'm sure programmers in the 1960s said. LOL.

I thought dates only started in 1970-01-01 :p

Nope. 1899-12-30 is date 0.

And you can even go further than that, because date values can be negative. The earliest date you can store in Access is 0100-01-01. Yes, that's the year 100.

So we could build a time travel app and travel back as far as the year 100?

> 1 January 1970 ' this date is also known as the Unix epoch or POSIX time, and is used by many Unix and Unix-like systems and programming languages, such as C/C++, Java, JavaScript, Perl, PHP, Python, and Ruby and many more

Hi Dorothy, obviously, you need to make the best effort in your business to know the date; that is something Access can't help you with.  For instance, you wouldn't buy merchandise from a seller without asking when the shipping date would be, or at least for an estimate ship date.  Many industries also require important dates to be put on business transactions.  In my industry (retail, wholesale), all orders required start ship date and cancel date, otherwise the buyer couldn't even place an order.

If a date is really unknown, then look for any supporting information that would let you make a "best estimate."  For instance, if a customer places an order with a start ship date of today, but with a cancel date of unknown.  (Btw, in retail, "cancel date" is the date when an order is automatically cancelled if the shipper can't ship it by then.)  Then you can reasonably guess that the cancel date is probably within a short time, since the customers wants you to start shipping today.

@Richard What would be the Y2100 problem? The old Y2K problem wasn't with date fields per say, it was with text strings that had a 2 digit year instead of an actual date. In 1999 I didn't have a problem with dates, as like Access, dates were an integer number that was converted to a date in the old db I used. The problem was in some of my tables that used a code like 9501 for the first month of 1995, or 954 for the 4th quarter of 1995 (funding applications). It wasn't really a problem as I didn't have any 1900 records to deal with. The problem only started giving headaches when records were sorted for processing. I.e. 011 would come before 951 (qtr 1 of 2001 before qtr 1 of 1995).

However, there were a lot of legacy systems that didn't have an integer date conversion, hence the big fear.

That all said, I did have a Y2K party, and one of my prankster friends went looking for my breaker box to throw the switch at midnight. Thankfully he didn't find it, as I had a few computers running upstairs that wouldn't have liked his prank. Along with all my digital clocks. A few UPS batteries had died months before. So why didn't I shut them all down before hand? I was in the know; most of the Y2K scare was just that, a scare. My other IT friends were happily cashing in on all the overtime.

Thank all of you for your responses and suggestions.  This inventory database is for a small town museum that was opened in 1974 so the actual acquired date of many items is unknown.  Our new director started an inventory in an excel spreadsheet but many items are still on index cards.   I've decided to insert the date of 11/11/1111 for any unknown date as I import the data from the excel spreadsheet.  I do like the "Date Unknown" button idea for future entries of old data.  It would standardize the unknown date entry.  Thank you again everyone.

It might be easier for your reporting if you did have a second field that said DateUnknown (y/n). And if the date is NULL, then you can make that checkbox visible. This might make it easier later on for your reporting purposes if you want to show all of the items that you don't know the date because NULL by itself might indicate that you just don't know what it is but you could find out. So again, it's all dependent upon your usage and how you want to deal with it.