Changing Dates if time is greater than Midnight

[Guest]

Hi,

I am using Access 2003.

I have a schedule that runs from 7am (Date) - 7am (Date +1).

Considering that the 'Date' field can be any date in the future (or the
current date) when a schedule is built, I am trying to write some VBA code
that will change any times after 00:00 to (Date+1).

For example, lets say I build a schedule for the (dd/mm/yy) 30/06/05. All
entries in the Schedule are entered between 23:59 - 00:00 for the 30/06/05.

But because the Schedule starts at 07:00 am (not 00:00) everything after
00:00 am needs to have the date incremented to the next day (ie 01/07/05).

So basically what I am trying to acheive is:

If the TIME field is between 07:00 am - 23:59:59 pm (for the date the
schedule is to be built for) then the date will remain unchanged.

If the TIME field is between 00:00 am - 07:00 am (for the date the schedule
is to be built for) then the date will increase by + 1.

The schedule is built by a query and I'm thinking that maybe this problem
might be better being fixed at the query level rather than after the query
has run. If anyone can shed some light on the best way to acheive this I
would be most greatful.

Cheers,
GLT.

 

[Guest]

For anyone interested I worked this one out - much better using SQL and I
used the following which worked:

strSQL = "UPDATE [2 Current Schedule] SET [2 Current Schedule].[Job Run
Date] = '" & tdyDte & "' " & _
"WHERE ((([2 Current Schedule].[Job Start Time]) >= #7:00:00 AM# And ([2
Current Schedule].[Job Start Time]) <= #23:59:59 AM#))"

strSQL1 = "UPDATE [2 Current Schedule] SET [2 Current Schedule].[Job Run
Date] = '" & tmwDte & "' " & _
"WHERE ((([2 Current Schedule].[Job Start Time]) >= #12:00:00 AM# And ([2
Current Schedule].[Job Start Time]) <= #06:59:59 AM#))"

The tdyDte and the tmwDte are the Date feilds that are input via the user.