If...Then involving time and dates 2

[merlynsdad]

I've downloaded a bunch of time & date data from Oracle to Access, as dates and times using TimeSerial and DateSerial. Now I need to specify that if the time is between midnight and 4AM, the date should be the day before. I don't think I can do that in the SQL statement, so I'm using an IF...THEN structure as follows:

If tblTemp_Event.time_stamp > #12:00:00 AM# And tblTemp_Event.time_stamp <= #4:00:00 AM# Then
tblTemp_Event.date_stamp = (tblTemp_Event.date_stamp - 1)
End If

I'm getting a variable not defined error on tblTemp_Event, which is confusing me more than I'm already confused. Where have I gone wrong?

If the square peg won't fit in the round hole, sand off the corners.

 

[SkipVought]

hi,

you must use Date/Time. A timestamp has BOTH elements!

What Date Reference does your code need?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

Why not simply an update query ?

UPDATE tblTemp_Event
SET date_stamp = (date_stamp - 1)
WHERE time_stamp BETWEEN #00:00:01# And #16:00:00#

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MazeWorX]

Your error is simply because you cannot just add SQL to an if statement. you must first open the recordset then reference the fields in the if statement something like.

Dim db as DAO.Database
Dim rs as DAO.Recordset

Set db = Currentdb()
Set rs = db.OpenRecordset("tblTemp_Event")

If rs!time_stamp > #12:00:00 AM# Then
   If rs!time_stamp <= #4:00:00 AM# Then
      with rs
             .edit
             .Fields("date_stamp") = rs!date_stamp - 1
             .update
      End With
   End if
End if

as PHV has suggested this simple update is much easier to accomplish with a simple update query


HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work