Avatar of Fordraiders
Fordraiders
Flag for United States of America 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 ?


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

Open in new window



Thanks
fordraiders
Microsoft AccessVBA

Avatar of undefined
Last Comment
Fordraiders

8/22/2022 - Mon
Norie

Does this work any better?
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 & "' , #" & Format(Now, "yyyy-mm-dd hh:mm:ss") &  "#);"
                     CurrentDb.Execute strsql_sql, dbSeeChanges

Open in new window

Gustav Brock

It should be OK. What about the other values?
Fordraiders

ASKER
"ttt"  is a time stamp also; but getting the value from a field on a form.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Gustav Brock

Yes, but are those values saved leaving the new timestamp empty?
Fordraiders

ASKER
Gustav, no sorry , no issues..
Gustav Brock

Ehh ..? so:

sometimes the  " InProgress_or_Closed_at" value does not get inserted?

means that nothing is inserted, or only that field is not inserted?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Fabrice Lambert

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.
Fordraiders

ASKER
Gustav, The value   "Now()"  is not getting inserted into the field..at some point(but usually does).

Thanks
ste5an

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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Fordraiders

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
ASKER CERTIFIED SOLUTION
Gustav Brock

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Dale Fye

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.
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

Open in new window

HTH
Dale
Fordraiders

ASKER
Thanks all
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.