format date field

[lilbit27]

7/19/2007 12:46:30 am. is in a field called CmplteDte. It is a date
stamp of when the user completed the item. I need the time for a
report but for a form that the supervisors use to QA the previous days
work I format the date to 7/19/2007 using a query
Format([CmplteDate],"mm/dd/yy") so that they see everything for that
date and not just that time.

My questions is that when work is stamped after 12 midnight it date
stamps to the next day, How do I convert these dates back to the
previous date so that the date says 7/18/2007. The items are still the
previous days work even though the user didn't complete the item until
the next day. It would be from the time frame of 12:00 am to 4:00 am
that needs to be converted back to the previous day.

Here is an example

7/19/2007 12:46:30 AM i want to formatt to 7/18/2007.
7/19/2007 1:18:52 AM I want to formatt to 7/18/2007
7/18/2007 8:59:49 AM formatted to 7/18/2007
7/18/2007 8:25:49 PM formatted to 7/18/2007
7/18/2007 8:15:55 PM formatted to 7/18/2007
7/18/2007 7:58:01 PM formatted to 7/18/2007
7/18/2007 7:42:10 PM formatted to 7/18/2007

 

[Jeff Boyce]

If you want to be able to query by when the item was begun, rather than
completed, couldn't you add a StartedDateTime field also?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

My questions is that when work is stamped after 12 midnight it date
stamps to the next day, How do I convert these dates back to the
previous date so that the date says 7/18/2007. The items are still the
previous days work even though the user didn't complete the item until
the next day. It would be from the time frame of 12:00 am to 4:00 am
that needs to be converted back to the previous day.

DateValue(DateAdd("h", -4, [timestamp]))


John W. Vinson [MVP]

 

[Jeff Boyce]

John

Since I enjoy breaking things, a potential downside that I can see from the
'subtract 4 hours' approach is if something started and stopped within the
'after hours' time period.

I'm not clear from the OP's description how work interval from 2am to 3am
should be treated. It obviously started in a new day, but finished before
the 4am point.

Regards

Jeff Boyce
Microsoft Office/Access MVP

My questions is that when work is stamped after 12 midnight it date
stamps to the next day, How do I convert these dates back to the
previous date so that the date says 7/18/2007. The items are still the
previous days work even though the user didn't complete the item until
the next day. It would be from the time frame of 12:00 am to 4:00 am
that needs to be converted back to the previous day.

DateValue(DateAdd("h", -4, [timestamp]))


John W. Vinson [MVP]

 

[lilbit27]

John

Since I enjoy breaking things, a potential downside that I can see from the
'subtract 4 hours' approach is if something started and stopped within the
'after hours' time period.

I'm not clear from the OP's description how work interval from 2am to 3am
should be treated. It obviously started in a new day, but finished before
the 4am point.

Regards

Jeff Boyce
Microsoft Office/Access MVP

DateValue(DateAdd("h", -4, [timestamp]))

John W. Vinson [MVP]- Hide quoted text -

- Show quoted text

Its a check box and once the user clicks it to say they have completed
that item it gives me a date and time. 3:30 am is when there shifts
end. I just stuck 4 in as a precaution. So anything that they click
on between 12 am and 4 am I wanted the date to convert back to the
previous days work. Because even though their shift carrys over into
the next date its still work from the previous day.

 

[Jeff Boyce]

Thanks for the clarification.

So it sounds like you're saying that even if work gets started at 2am and
finished at 3am, you still want the completed work credited against the
previous day's stats.

John provided a great way to do that.

Regards

Jeff Boyce
Microsoft Office/Access MVP

John

Since I enjoy breaking things, a potential downside that I can see from
the
'subtract 4 hours' approach is if something started and stopped within
the
'after hours' time period.

I'm not clear from the OP's description how work interval from 2am to 3am
should be treated. It obviously started in a new day, but finished
before
the 4am point.

Regards

Jeff Boyce
Microsoft Office/Access MVP

message

My questions is that when work is stamped after 12 midnight it date
stamps to the next day, How do I convert these dates back to the
previous date so that the date says 7/18/2007. The items are still the
previous days work even though the user didn't complete the item until
the next day. It would be from the time frame of 12:00 am to 4:00 am
that needs to be converted back to the previous day.

DateValue(DateAdd("h", -4, [timestamp]))

John W. Vinson [MVP]- Hide quoted text -

- Show quoted text

Its a check box and once the user clicks it to say they have completed
that item it gives me a date and time. 3:30 am is when there shifts
end. I just stuck 4 in as a precaution. So anything that they click
on between 12 am and 4 am I wanted the date to convert back to the
previous days work. Because even though their shift carrys over into
the next date its still work from the previous day.

 

[lilbit27]

Thanks for the clarification.

So it sounds like you're saying that even if work gets started at 2am and
finished at 3am, you still want the completed work credited against the
previous day's stats.

John provided a great way to do that.

Regards

Jeff Boyce
Microsoft Office/Access MVP

John
Since I enjoy breaking things, a potential downside that I can see from
the
'subtract 4 hours' approach is if something started and stopped within
the
'after hours' time period.
I'm not clear from the OP's description how work interval from 2am to 3am
should be treated. It obviously started in a new day, but finished
before
the 4am point.
Regards
Jeff Boyce
Microsoft Office/Access MVP
message
My questions is that when work is stamped after 12 midnight it date
stamps to the next day, How do I convert these dates back to the
previous date so that the date says 7/18/2007. The items are still the
previous days work even though the user didn't complete the item until
the next day. It would be from the time frame of 12:00 am to 4:00 am
that needs to be converted back to the previous day.
DateValue(DateAdd("h", -4, [timestamp]))
John W. Vinson [MVP]- Hide quoted text -
- Show quoted text

Its a check box and once the user clicks it to say they have completed
that item it gives me a date and time. 3:30 am is when there shifts
end. I just stuck 4 in as a precaution. So anything that they click
on between 12 am and 4 am I wanted the date to convert back to the
previous days work. Because even though their shift carrys over into
the next date its still work from the previous day.- Hide quoted text -

- Show quoted text -

Correct anything that stamps a day ahead needs to covert back to the
previous day