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.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

DoCmd.RunSQL "INSERT INTO tblTimeClock(EmployeeID,Date,ClockIN) Values ('" & txtEmployeeID & "', #" & Date() & "#, #" & format(now(),"hh:mm:ss AMPM") & "#);"

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.


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

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!

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


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


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?

Steve BinkCommented:
Here's a good explanation of the difference:
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.

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.

By the way, that was should NOT be able to notice a performance difference.
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.
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.