I am putting together a database for a production line. We run 24/7 and the production cuttoff is at 9:00AM. I am trying to create a field that will contain a default production date. The problem is that between 12:00AM and 9:00AM i need the field to default to the previous day. I've tried several ways to do this but nothing seems to work the way that it should. I think that the default value should be something like this:
Perhaps I am way off?

Literal dates and times in Access must be given as #...#

You can't use strings (which is what '....' implies) without invoking a conversion function.

=IIf(Time()>#00:00:00# And Time()<#09:00:00#,Date()-1,Date())

Now, that *MISSES* midnight, since it is 0:00:00 exactly. You would want to use >= instead of >. But then that's silly, because *ALL* TIME() values are always >= #00:00:00#.

So K.I.S.S. and just do

=IIf( Time() < #09:00:00#, Date()-1, Date() )

And you don't want to know this, but... You could also do

=IIf( Time() < 9/24, Date()-1, Date() )

(Don't do it! It will only make your head hurt.)

p.s.: Another thing not to do, but you could do

=IIf( Time() < CDate('09:00:00'), Date()-1, Date() )

As I said, if you use a string you must use a conversion function.

p.p.s.: On the other hand, you could reasonably do

=IIf( Hour(Time()) < 9, Date()-1, Date() )

D'OH
Now my head hurts.

Thanks for the advice!