Adding 7 hrs to Date()

[Toga]

Our production date changes @ 7:00AM and I'm trying to get my form to automatically reflect this when a new record is created and the date is added automatically.

For clarity, I don't want the date that's inserted on my form to change @ Midnight.....I want it to change @ 7:00AM

I've been trying to add 7 hrs to the date returned via the Date() function using the DateSerial & the DateAdd functions but to no avail. I can add days alright but it doesn't seem to change the date if I try adding just 7 hrs.

I only want the Short Date format. I don't want time showing in this field as well.

Can anyone hekp.....please!
Thanks....Toga

 

[StuKregor]

What about this logic?

=IIf(Time()>#7:00:00#,Date()+1,Date())

 

[Toga]

Unless I misunderstood.....no, it doesn't seem to work because if I use that right now @ 10:00PM on 6/21, that function will tell me it's 6/22. I don't want it to return 6/22 untill tomorrow morning @ 7:00AM.

Thanks anyway.....Toga

 

[Lightning]

Use the TimeSerial Function.

If Time() < TimeSerial(7, 0, 0) Then
MyDate = Date - 1
Else
MyDate= Date

End If

HTH
Lightning

 

[Toga]

Tested it out and seems to work fine!
Thanks....Toga

 

[Toga]

ACtually, going back to StuKregor's comments.....He nearly had right it as well if I just modify his suggestion to read.

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

Anyway....Thanks Everyone
Toga

 

[PhilipSchroevers]

OK, but what's wrong with

MyDate = DateAdd(&quot;h&quot;,-7,Date)

In words: give me the date of seven hours ago.

 

[Toga]

Nothing would be wrong with it if I could only get the time not to show along with it. This is the approach I started with and the result I kept getting included the time....

ie.... 6/21/01 5:00:00 PM

Condidering that users may need to edit this date, I didn't want to have some data with date / time and other data with just date...If someone edited it for example.

Thanks for the suggestion though...
Toga

PS...Yes, I have my field formatted as Short Date with the following input mask...99/99/00;0; Nothing I did with this seemed to get rid of the time showing.....except for the suggestion above of course.

 

[PhilipSchroevers]

Yes, I see what you mean. The time is displayed as soon as the field gets the focus. Access displays the &quot;true content&quot; then, including Time UNLESS there is no timepart. This explains why the other suggestions did work, they are based on Date() which does not contain a Timepart.

My solution should have been:

MyDate = DateValue(DateAdd(&quot;h&quot;,-7,Now())),

DateValue strips the Timepart.

Note that Date() was not correct, it would always subtract 7 hours from midnight.

Just wanted to let you know.