Solved

DoCmd.RunSQL syntax problems

Posted on 2004-10-19
10
621 Views
Last Modified: 2010-07-27
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
Comment
Question by:Die-Tech
  • 5
  • 3
  • 2
10 Comments
 
LVL 10

Expert Comment

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

perkc
0
 
LVL 10

Accepted Solution

by:
perkc earned 450 total points
ID: 12349511
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
 
LVL 4

Author Comment

by:Die-Tech
ID: 12349950
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 10

Assisted Solution

by:perkc
perkc earned 450 total points
ID: 12350097
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
 
LVL 4

Author Comment

by:Die-Tech
ID: 12350476
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
 
LVL 50

Expert Comment

by:Steve Bink
ID: 12350606
Here's a good explanation of the difference:

http://www.experts-exchange.com/Q_20629573.html
0
 
LVL 4

Author Comment

by:Die-Tech
ID: 12350628
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
 
LVL 10

Assisted Solution

by:perkc
perkc earned 450 total points
ID: 12350633
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
 
LVL 10

Expert Comment

by:perkc
ID: 12350663
By the way, that was should NOT be able to notice a performance difference.
0
 
LVL 50

Assisted Solution

by:Steve Bink
Steve Bink earned 50 total points
ID: 12351240
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question