Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 631
  • Last Modified:

DoCmd.RunSQL syntax problems

Hi Experts...

Here's what I have so far...

DoCmd.RunSQL "INSERT INTO tblTimeClock(EmployeeID,Date,ClockIN) Values ('" & txtEmployeeID & "', '" & txtDate & "', '" & txtTime & "');"

txtEmployeeID, txtDate and txtTime are all text boxes on my form.

I would really like to insert the value in text box txtEmployeeID, the current date and the current time.

TIA,
Die-Tech
0
Die-Tech
Asked:
Die-Tech
  • 5
  • 3
  • 2
4 Solutions
 
perkcCommented:
DoCmd.RunSQL "INSERT INTO tblTimeClock(EmployeeID,Date,ClockIN) Values ('" & txtEmployeeID & "', #" & Date() & "#, #" & format(now(),"hh:mm:ss AMPM") & "#);"

perkc
0
 
perkcCommented:
If you want the current date and time why do you need textboxes for them? Do you really need the date and time to be in two different fields? You could just store the date and time in one field and get that value using the 'Now()' function.

Just a thought.

perkc
0
 
Die-TechAuthor Commented:
Thanks perkc,

This was the kind of feedback I'm looking for.
I didn't think I needed to get the date and time from the textboxes.
And I was wondering about storing both the date and time together in one field.

Can you do a DateDiff using the date and time stored together?

Thanks,
Dan
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
perkcCommented:
Sure, any time you want to only refer to the date you would use:

Format(DateTimeField,"mm/dd/yy")

or for time:

Format(DateTimeField,"hh:mm:ss AMPM")

You can also use the datediff function like:

msgbox datediff("d",format(now-1,"mm/dd/yy"),format(now,"mm/dd/yy")) 'Just an example

perkc

0
 
Die-TechAuthor Commented:
Thanks again perkc!

I think this approach will work out much better.
I was wondering about when people work 2nd shift and they clock in on one day but clock out on the next day.

Will datediff be able to handle that issue? If so, great!

I just changed my table so that ClockIN, LunchOUT, LunchIN and ClockOUT are all date/time fields with no formatting.
I also changed the DoCmd.RunSQL line to the following...

DoCmd.RunSQL "INSERT INTO tblTimeClockTEMP(EmployeeID,ClockIN) Values ('" & txtEmployeeID & "', '" & Now() & "');"

And I successfuly added a new record to the table with the EmployeeID (taken from the inputbox) and Now() for the ClockIN time/date

Now comes the tricky part....

When the same employee wants to clock out for lunch, how do I find the record he clocked in on and add the LunchOUT "Now()" time to the record?

Also, I've asked about doing the same thing via CurrentDb instead of RunSQL
Would I be better off adding and editing the records via CurrentDb
Do you prefer one over the other?


0
 
Steve BinkCommented:
Here's a good explanation of the difference:

http://www.experts-exchange.com/Q_20629573.html
0
 
Die-TechAuthor Commented:
Thanks routinet,

I read that this morning... that post was what got me thinking about should I use RunSQL or CurrentDb.

Looks like CurrentDb is going to be best for my situation.

0
 
perkcCommented:
A couple of things:

On the currentdb vs. runsql, I'm in the habit of using the currentdb method but I'm not really sure which method is quicker. I will however say that unless the sql statement involves alot of data you should be able to notice a performance difference.

If you post the applicable table structures that you're using I'll give my thoughts on the in/out issue.

perkc
0
 
perkcCommented:
By the way, that was should NOT be able to notice a performance difference.
0
 
Steve BinkCommented:
I agree with perkc.  Unless you are using enormous amounts of data for the purpose of generating a benchmark to be examined later, you will likely never notice the speed difference between the two.  The main benefits derived from using CurrentDB are the additional pieces of functionality, like error-trapping, return codes, RecordsAffected, transactions, etc.
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now