Calculate Dates

[dave_282]

Hello,

I'm in the process of creating a new table in an access database for our team to add rows in, I have a date field but I want the date to be calculated automatically based on the current time of when a new record is entered. midnight to 6:59 am I want the date to be date -1 (yesterday) and 7am to 11:59pm to be current date.

I've tried putting syntax into the default value bit but that's not working so not sure of either the syntax and where exactly in access to do this. Hoping its possible? Can anyone assist please?

[cyanidem]

=IIf(Hour(Now()<7),DateAdd("d",-1,Date()),Date())

[dave_282]

Thanks for that, I put that in the default value property of the date field, is that the correct place to put it? It appeared to work in that it gave me todays date when I went to create a new record in the table but I changed the time of my PC to before 7 and the date was still todays so I cant validate if its working or not?

[cyanidem]

Yes, it's correct place and it should work (I didn't test it though).
Actually I just realised that one bracket is in wrong place
This is correct:
=IIf(Hour(Now())<7,DateAdd("d",-1,Date()),Date())

[dave_282]

Brilliant, that's working, thanks a lot!

[cyanidem]

Happy to help