Fordraiders
asked on
Using a time stamp correctly in sql
Access vba
Using ian INSERT statement in vba.
What I need: sometimes the " InProgress_or_Closed_at" value does not get inserted?
i'm i using the "Now()" correctly in the sql statement ?
Or is there a better way to write it to make sure a time stamp is posted correctly ?
Thanks
fordraiders
Using ian INSERT statement in vba.
What I need: sometimes the " InProgress_or_Closed_at" value does not get inserted?
i'm i using the "Now()" correctly in the sql statement ?
Or is there a better way to write it to make sure a time stamp is posted correctly ?
ttt = Forms!dataentry.txtReOpened
strsql_sql = "INSERT INTO [dbo_t_nsc_trackcode_trans_time_entry](NSC_ID_Ref, [opened_at], InProgress_or_Closed_at)" & vbCrLf
strsql_sql = strsql_sql & "VALUES (" & Me.NSC_Id & ",'" & ttt & "' , now());"
CurrentDb.Execute strsql_sql, dbSeeChanges
Thanks
fordraiders
It should be OK. What about the other values?
ASKER
"ttt" is a time stamp also; but getting the value from a field on a form.
Yes, but are those values saved leaving the new timestamp empty?
ASKER
Gustav, no sorry , no issues..
Ehh ..? so:
means that nothing is inserted, or only that field is not inserted?
sometimes the " InProgress_or_Closed_at" value does not get inserted?
means that nothing is inserted, or only that field is not inserted?
Hmm, what about setting a default value on the server for the InProgress_or_Closed_at column ?
Or a trigger setting the value ?
this way you won't have to worry about it in the front-end.
Also, keep in mind that with any date or time type columns, the value must be formated accordingly.
Or a trigger setting the value ?
this way you won't have to worry about it in the front-end.
Also, keep in mind that with any date or time type columns, the value must be formated accordingly.
ASKER
Gustav, The value "Now()" is not getting inserted into the field..at some point(but usually does).
Thanks
Thanks
Just a comment:
A DATETIME column named InProgress_or_Closed_at is semantic nonsense when used in a relational data model.
The normal usage here would be:
1) A clean relational data model. Thus you have a separate table to track that state. Were an existing row signals Is Closed and the row itself only consists of the key, the time stamp and maybe the user who closed it.
2) Using SQL NULL-value logic. The column would be named ClosedAt. When it is NULL, then the state is implicit In Progress. When a value is set, then it is implicit Closed.
A DATETIME column named InProgress_or_Closed_at is semantic nonsense when used in a relational data model.
The normal usage here would be:
1) A clean relational data model. Thus you have a separate table to track that state. Were an existing row signals Is Closed and the row itself only consists of the key, the time stamp and maybe the user who closed it.
2) Using SQL NULL-value logic. The column would be named ClosedAt. When it is NULL, then the state is implicit In Progress. When a value is set, then it is implicit Closed.
ASKER
Fabrice,
"Also, keep in mind that with any date or time type columns, the value must be formated accordingly."
So that is an interesting statement?
So would Norie's comment be correct ?
Thanks
fordraiders
"Also, keep in mind that with any date or time type columns, the value must be formated accordingly."
So that is an interesting statement?
So would Norie's comment be correct ?
Thanks
fordraiders
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The following should work. Note that with Access, you need to wrap literal date values like your ttt value in # (as in #11/16/2018#). But because the Now() function returns a date, you should not need to wrap that in anything.
Dale
ttt = Forms!dataentry.txtReOpened
strsql_sql = "INSERT INTO [dbo_t_nsc_trackcode_trans_time_entry] (NSC_ID_Ref, [opened_at], InProgress_or_Closed_at) " _
& "VALUES (" & Me.NSC_Id & ",#" & ttt & "# , Now());"
CurrentDb.Execute strsql_sql, dbSeeChanges
HTHDale
ASKER
Thanks all
Open in new window