We help IT Professionals succeed at work.

Using a  time stamp correctly in sql

Fordraiders
Fordraiders asked
on
74 Views
Last Modified: 2018-11-21
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
Comment
Watch Question

NorieAnalyst Assistant
CERTIFIED EXPERT

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

CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
It should be OK. What about the other values?

Author

Commented:
"ttt"  is a time stamp also; but getting the value from a field on a form.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

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

Author

Commented:
Gustav, no sorry , no issues..
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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 LambertConsulting
CERTIFIED EXPERT
Distinguished Expert 2017

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

Author

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

Thanks
ste5anSenior Developer
CERTIFIED EXPERT

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

Author

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
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
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

Author

Commented:
Thanks all

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions