Solved

DoCmd.RunSQL syntax problems

Posted on 2004-10-19
10
624 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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 51

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 51

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

623 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