Solved

DoCmd.RunSQL syntax problems

Posted on 2004-10-19
10
619 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
 
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

911 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now