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
LVL 3
FordraidersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
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 BrockCIOCommented:
It should be OK. What about the other values?
FordraidersAuthor Commented:
"ttt"  is a time stamp also; but getting the value from a field on a form.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Gustav BrockCIOCommented:
Yes, but are those values saved leaving the new timestamp empty?
FordraidersAuthor Commented:
Gustav, no sorry , no issues..
Gustav BrockCIOCommented:
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?
Fabrice LambertConsultingCommented:
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.
FordraidersAuthor Commented:
Gustav, The value   "Now()"  is not getting inserted into the field..at some point(but usually does).

Thanks
ste5anSenior DeveloperCommented:
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.
FordraidersAuthor Commented:
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
Gustav BrockCIOCommented:
No. Now() is a function that in SQL returns a Date vaiue, so nothing to format.

But you seem to face a timing issue.
Try this which should force a call to Date() and Time() before the insert:

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 & "' , (Date() + Time()));"
CurrentDb.Execute strsql_sql, dbSeeChanges

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Developing Solutions LLCCommented:
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
FordraidersAuthor Commented:
Thanks all
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.