Date Time Format

[Guest]

I want to change the format of some code in a timer form that records the
date in a table to tell if the code was sucessfully run. What I would like to
do is change that code to include the time in the same field as the date if
possible as well. The following is the code that includes only the date:

strSQL = "UPDATE tblTimerDate SET LastTimerDate = " & _
' Format(Date, "\#mm/dd/yyyy\#")

On Error GoTo Err_Handler

If Time() > #5:10:00 AM# Then

If DLookup("LastTimerDate", "tblTimerDate") < Date Then

If DLookup("LastTimerDate", "tblTimerDate") < Date Then

Set dbs = CurrentDb
Set wrk = DAO.DBEngine.Workspaces(0)

' begin transaction
wrk.BeginTrans

****Several Code query instructions here then*****

' Update tblTimerDate table
strQuery = "embedded SQL to update tblTimerDate"
dbs.Execute strSQL, dbFailOnError

' no error so commit transaction This means that all of
' the queries above will be created. If the code stops,
' before commiting the transaction, the tables that are
' to be created will be created but only in the memory
' and if you keep running the code to debug the database
' you will get an error reporting that the tables exist
' even when you check the tables area in database window
' and you do not see them. The tables will be created once
' the commit transaction completes.

wrk.CommitTrans

The table that receives the date/time is formatted to General Date.

How can I add the time to this?

Thanks,

Dennis

 

[Rick Brandt]

I want to change the format of some code in a timer form that records
the date in a table to tell if the code was sucessfully run. What I
would like to do is change that code to include the time in the same
field as the date if possible as well. The following is the code that
includes only the date:

strSQL = "UPDATE tblTimerDate SET LastTimerDate = " & _
' Format(Date, "\#mm/dd/yyyy\#")

On Error GoTo Err_Handler

If Time() > #5:10:00 AM# Then

If DLookup("LastTimerDate", "tblTimerDate") < Date Then

If DLookup("LastTimerDate", "tblTimerDate") < Date Then

Set dbs = CurrentDb
Set wrk = DAO.DBEngine.Workspaces(0)

' begin transaction
wrk.BeginTrans

****Several Code query instructions here then*****

' Update tblTimerDate table
strQuery = "embedded SQL to update tblTimerDate"
dbs.Execute strSQL, dbFailOnError

Formatting means diddly squat. DateTimes are always STORED exactly the same.
If your "embedded SQL that you didn't share with us" includes a non-midnight
time component then it will be stored in the table along with the date.

 

[John Vinson]

How can I add the time to this?

strSQL = "UPDATE tblTimerDate SET LastTimerDate = " & _
' Format(Now, "\#mm/dd/yyyy\ hh:nn:ss\#")

John W. Vinson[MVP]

 

[Guest]

Thank you John.
--
Thanks,

Dennis

strSQL = "UPDATE tblTimerDate SET LastTimerDate = " & _
' Format(Now, "\#mm/dd/yyyy\ hh:nn:ss\#")

John W. Vinson[MVP]

 

[Douglas J. Steele]

<picky>
Just in case the user's date and time separators aren't standard

Format(Now, "\#mm\/dd\/yyyy hh\:nn\:ss\#")

AFAIK, there's no need to escape the space between the date and time.
</picky>

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

 

[Rick Brandt]

strSQL = "UPDATE tblTimerDate SET LastTimerDate = " & _
' Format(Now, "\#mm/dd/yyyy\ hh:nn:ss\#")

John W. Vinson[MVP]

Would not the following accomplish the same?

strSQL = "UPDATE tblTimerDate SET LastTimerDate = Now()"

I fail to see what the formatting is accomplishing. Unless this is being stored
in a text field.

 

[Douglas J. Steele]

Would not the following accomplish the same?

strSQL = "UPDATE tblTimerDate SET LastTimerDate = Now()"

I fail to see what the formatting is accomplishing. Unless this is being
stored in a text field.

You're correct: it should work. I think John & I are just so use to
referring to dates in controls on the form.