By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,140 Members | 1,301 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,140 IT Pros & Developers. It's quick & easy.

have access enter a date automatically that is not the current date

P: 54
I have a form where employees are typing in information throughout a shift and that information is save to a table called "anodic unload sheet". It automatically inserts the date into a field. We have a night shift from 6:00PM through 6:00AM and I want the date inserted into the table to change at the end of the shift instead of midnight. Is this possible? Thanks in advance.
Jul 13 '12 #1

✓ answered by NeoPa

The following code within the SQL of your query will do exactly what you require :
Expand|Select|Wrap|Line Numbers
  1. DateValue(DateAdd('h',-6,Now()))

Share this Question
Share on Google+
4 Replies


P: 4
modify the table design. set the default to a user-defined function that changes the default value based on the current time. In that function check the time and depending on the time value set the default date.
Jul 13 '12 #2

P: 54
I just barely discovered a way to do this. Here is what I did.
Expand|Select|Wrap|Line Numbers
  1. If Me.Time.Value > "12:00:01 AM" And Me.Time.Value < "6:00:00 AM" Then
  2. Me.date.Value = DateValue(Now - 1)
  3. Else: Me.date.Value = DateValue(Now)
  4. End If
Thanks for your suggestion anyway though.
Jul 13 '12 #3

Rabbit
Expert Mod 10K+
P: 12,357
Use the iif() function to return the prior day if the time is before 6 am.
Jul 14 '12 #4

NeoPa
Expert Mod 15k+
P: 31,419
The following code within the SQL of your query will do exactly what you require :
Expand|Select|Wrap|Line Numbers
  1. DateValue(DateAdd('h',-6,Now()))
Jul 14 '12 #5

Post your reply

Sign in to post your reply or Sign up for a free account.