convert date stamp to previous date

[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

 

[Guest]

This will do it. t is the date/time value you want to move back one day if
it is before 04:01 AM

 

[lilbit27]

This will do it. t is the date/time value you want to move back one day if
it is before 04:01 AM
--
Dave Hargis, Microsoft Access MVP

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- Hide quoted text -

- Show quoted text -

what will do it.

 

[Guest]

I guess the actual answer would be useful.
dateadd("d",iif(hour(x)<4,-1,iif(hour(x) = 4 and minute(x)=0,-1,0)),x)
where x is the date/time
--
Dave Hargis, Microsoft Access MVP

This will do it. t is the date/time value you want to move back one day if
it is before 04:01 AM
--
Dave Hargis, Microsoft Access MVP

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- Hide quoted text -

- Show quoted text -

what will do it.

 

[lilbit27]

I guess the actual answer would be useful.
dateadd("d",iif(hour(x)<4,-1,iif(hour(x) = 4 and minute(x)=0,-1,0)),x)
where x is the date/time
--
Dave Hargis, Microsoft Access MVP

This will do it. t is the date/time value you want to move back one day if
it is before 04:01 AM
--
Dave Hargis, Microsoft Access MVP
:
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- Hide quoted text -
- Show quoted text -

what will do it.- Hide quoted text -

- Show quoted text -

can I just say:

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

recondate represents the date\time

 

[Guest]

No
That just subtracts 4 hours from the value. If the time were 7/20/2007 07:30
AM, it would just make it 7/20/2007 03:30 AM. I don't see that it will get
what you are asking for.
--
Dave Hargis, Microsoft Access MVP

I guess the actual answer would be useful.
dateadd("d",iif(hour(x)<4,-1,iif(hour(x) = 4 and minute(x)=0,-1,0)),x)
where x is the date/time
--
Dave Hargis, Microsoft Access MVP

This will do it. t is the date/time value you want to move back one day if
it is before 04:01 AM

:
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- Hide quoted text -

- Show quoted text -

what will do it.- Hide quoted text -

- Show quoted text -

can I just say:

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

recondate represents the date\time

 

[lilbit27]

No
That just subtracts 4 hours from the value. If the time were 7/20/2007 07:30
AM, it would just make it 7/20/2007 03:30 AM. I don't see that it will get
what you are asking for.
--
Dave Hargis, Microsoft Access MVP

I guess the actual answer would be useful.
dateadd("d",iif(hour(x)<4,-1,iif(hour(x) = 4 and minute(x)=0,-1,0)),x)
where x is the date/time
--
Dave Hargis, Microsoft Access MVP
:
This will do it. t is the date/time value you want to move back one day if
it is before 04:01 AM
--
Dave Hargis, Microsoft Access MVP
:
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- Hide quoted text -
- Show quoted text -
what will do it.- Hide quoted text -
- Show quoted text -

can I just say:

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

recondate represents the date\time- Hide quoted text -

- Show quoted text -

So how do I set it up using your query?

 

[lilbit27]

No
That just subtracts 4 hours from the value. If the time were 7/20/2007 07:30
AM, it would just make it 7/20/2007 03:30 AM. I don't see that it will get
what you are asking for.
--
Dave Hargis, Microsoft Access MVP

I guess the actual answer would be useful.
dateadd("d",iif(hour(x)<4,-1,iif(hour(x) = 4 and minute(x)=0,-1,0)),x)
where x is the date/time
--
Dave Hargis, Microsoft Access MVP
:
This will do it. t is the date/time value you want to move back one day if
it is before 04:01 AM
--
Dave Hargis, Microsoft Access MVP
:
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- Hide quoted text -
- Show quoted text -
what will do it.- Hide quoted text -
- Show quoted text -

can I just say:

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

recondate represents the date\time- Hide quoted text -

- Show quoted text -

But as long as it converts the date back to the previous date during
the 12am to 4:00 am time frame that would solve my problem. I don't
care about the time for this form, just the date. The datestamp says
the same for reporting purposes but the form needs to filter the info
by date when the supervisor goes in to see the work done for the
previous day.

 

[Guest]

You said you have this field formatted like this in your query.
Format([CmplteDate],"mm/dd/yy")

So just do it this way:

Format(dateadd("d",iif(hour([CmplteDate])<4,-1,iif(hour([CmplteDate]) = 4
and minute([CmplteDate])=0,-1,0)),[CmplteDate]), "mm/dd/yy")
--
Dave Hargis, Microsoft Access MVP

No
That just subtracts 4 hours from the value. If the time were 7/20/2007 07:30
AM, it would just make it 7/20/2007 03:30 AM. I don't see that it will get
what you are asking for.
--
Dave Hargis, Microsoft Access MVP

I guess the actual answer would be useful.
dateadd("d",iif(hour(x)<4,-1,iif(hour(x) = 4 and minute(x)=0,-1,0)),x)
where x is the date/time

:
This will do it. t is the date/time value you want to move back one day if
it is before 04:01 AM

:
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- Hide quoted text -

- Show quoted text -

what will do it.- Hide quoted text -

- Show quoted text -

can I just say:

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

recondate represents the date\time- Hide quoted text -

- Show quoted text -

So how do I set it up using your query?

 

[Guest]

I know it has nothing to do with time as far as presenting it to the user;
however, by your own specifications, time is a factor. You want anything
timestamped from midnight to 4:00 AM to be shown as the previous day.

That is what the forumula does. Here is the breakdown:
dateadd("d",iif(hour(x)<4,-1,iif(hour(x) = 4 and minute(x)=0,-1,0)),x)
{4 { 1 } { 2
}{3} 4}

1. If the hour is prior to 4 AM, return -1
2. If the hour is 4 AM and the minute is 0, return -1
3. If neither 1 or 2 is true, return 0
4. Add the number of days returned to the date. If 0 is returned, the date
remains the same. If -1 is returned, it makes the date the previous date.

Then, format it as I showed in my last post.
--
Dave Hargis, Microsoft Access MVP

No
That just subtracts 4 hours from the value. If the time were 7/20/2007 07:30
AM, it would just make it 7/20/2007 03:30 AM. I don't see that it will get
what you are asking for.
--
Dave Hargis, Microsoft Access MVP

I guess the actual answer would be useful.
dateadd("d",iif(hour(x)<4,-1,iif(hour(x) = 4 and minute(x)=0,-1,0)),x)
where x is the date/time

:
This will do it. t is the date/time value you want to move back one day if
it is before 04:01 AM

:
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- Hide quoted text -

- Show quoted text -

what will do it.- Hide quoted text -

- Show quoted text -

can I just say:

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

recondate represents the date\time- Hide quoted text -

- Show quoted text -

But as long as it converts the date back to the previous date during
the 12am to 4:00 am time frame that would solve my problem. I don't
care about the time for this form, just the date. The datestamp says
the same for reporting purposes but the form needs to filter the info
by date when the supervisor goes in to see the work done for the
previous day.