Mac said:
I have a table in SQL2K. It has a employee number, today's date,
job_number, start_time and end_time.
When a user start a new job, he scans and system record start time and
when he completes the job, he scans to end the job which will record end
time. Then immediately he starts new job but somehow our barcode system
does not record the same time it skips about 2 minutes and record start
time as 12:02 instead of 12:00.
I want to write a program that I can run at the end of the day to fix the
start time which should read the end time of the previous record and
update to start time of present record so end time of previous record is
the same as start time of the new record.
I would appreciate if someone please send me code of do while statement
using update.
First, most people here consider it rude to ask for someone to write your
code for you. We're here to help, but that doesn't mean write your program
(or part of it) for you. "Helping" means that you provide code (or describe
in detail, but providing code is better) for what you've done or attempted,
explain what's not working as desired (which may be errors you're getting,
etc.) and then we can "help" by telling you what you're doing wrong, suggest
solutions or alternatives or whatever. If you want someone to write all or
part of your program for you, then you need to hire a programmer.
Also, it's poor netiquette to include non-relevant newsgroups. Why did you
include microsoft.public.access.modulesdaovba when you stated your database
is SQL Server 2000 (unless by SQL2K you meant Access 2000)? Also, including
non-MS newsgroups should be avoided because those of us that access MS's
news server can't post to those groups (you included
comp.lang.visual.basic.misc). For that matter, I don't really see what this
has to do with controls (the one VB group that you posted to, aside from the
comp.lang one). Basically, you should have chosen better groups to post to
so as to maximize your chance of getting the best help.
Now, to "help" you, you probably don't need to write a While loop (or any
other kind of loop). Just write a SQL UPDATE statement and use an
appropriate WHERE clause to specify criteria for those records that should
be updated. For example (AIR CODE, and assuming a table named EMPLOYEES)
----BEGIN SQL CODE
UPDATE EMPLOYEES SET
start_time = end_time
WHERE DATEADD(n, 2, end_time) = start_time
-----END SQL CODE
That SQL statement is NOT intended to be the actual SQL code you'd need to
use. It's just intended to give you an idea of what to do. Most likely,
the actual WHERE clause you'll need to use will have other conditions to
ensure that only records that should be updated actually get updated (and
updated correctly). It's quite possible that the WHERE clause could be
rather lengthy and complex. You will have to test things quite thoroughly
(hopefully, you have a non-production database for this that you can restore
for multiple testings).
Ideally however, if scanning the end of the job is automatically supposed to
create a new record having that exact same time for the start time, then it
appears to be a bug in the code that does this scan and THAT's what should
be fixed. But maybe that's out of your control.
