Changing a default date value based on the time of day

[xduy]

I have a form that has a field for the date which currently defaults to =Date() (today), but from midnight to 8AM I want it to default to =Date()-1 (yesterday). Is there an easy way to do this?

[ranman256]

In the form,set the text box at form open.
txtbox= date() & " 8:00 am"

but for yesterday, use date math functions to subtract 1 day...

txtBox = DateAdd("d",-1,Date()) & " 8:00 am"

[xduy]

In the form I have right now I have the default value for the date text box set as "=Date()" which always brings up the current day. Where do I put what you wrote?

[aytee111]

The VBA code to set the value is:

If Format(Now(), "hh") < "08" And Format(Now(), "AMPM") = "AM" Then
    Me!Text8 = Date - 1
Else
    Me!Text8 = Date
End If

The question is where and when to make this happen. Is it bound to a table? In which case it would need to happen on the BeforeUpdate event.

[Missinglinq]

The IIF() Function was made for this:

IIf(Time()<#08:01:00 AM#,Date()-1,Date())

Linq ;0)>

[aytee111]

I couldn't get this to work in the Default Value of the textbox, where does it go?

[Missinglinq]

Worked (in v2007) for me, but it has to be just as given

IIf(Time()<#08:01:00 AM#,Date()-1,Date())

especially the bits in red.

I'm glad you posted, though! I reset my PC's clock, to test this, and forget to reset the time! Realized this when I went to recheck my code, which does still work.

Linq ;0)>

[aytee111]

I was going from design view to form view and it wasn't working. As soon as I closed and saved the form and reopened it then it worked! That's why I did the VBA thing.

[xduy]

Thank you so much everyone. I got it working now.

[Missinglinq]

Glad we could help!

@aytee111: The Access Gnomes like to mess around like that sometimes!

I think they just get bored!

Linq ;0)>