HELP! Using VB to alternate fields.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Im working on a project for work and I need my code to look at the last
record of a query. The code then needs to look at 2 fields in that code and
set the current time in the oppiset field in the next record. For example,
If there is a time in the last record in the first field, it will set the
current time in the current record in the second field.

Thanks
 
Define "last". What you mean and what Access means may not match.

If you can sort the records on some field to get what you would consider
"first" and "last" rows, you could use the Top property of the query to get
the Top 1 (after sorting in the "correct" direction).

Once you are getting the row you want, you can create new fields in the
query to do the calculations...
 
The query is sorted by Date then Time so by last i mean the latest date and
the latest time. The code I currently have will select the correct record,
but doesn't use that record, to deside where to put the the current time in
the new record.
 
Perhaps if you posted your query's SQL it would help make it clearer. I'm
afraid I'm still not following you...
 
Here is the SQL...
SELECT punches.[Emp#], punches.Date, punches.I, punches.O
FROM punches
WHERE (((punches.[Emp#])=[Enter Employee Number]));

Im trying to build a timeclock, the employee will enter an Emp# on a form,
the form brings up the query and goes to the last record.
(((DoCmd.OpenQuery "punch qry", acNormal, acEdit
' Goes to last record in qry
DoCmd.GoToRecord acQuery, "punch qry", acLast)))
Here is where the problem starts, I want the code to evaluate the "Last"
record of the query and determine which field is empty I or O (In and Out)
then go to a new record and punch the current time in the opposite field.
(i.e. if the "Last" record was I the new record would be O)

Thank you so much for your help.
 
I'm not familiar with this -- consider re-posting.

--
Good luck

Jeff Boyce
<Access MVP>

Dragon_Silveroak said:
Here is the SQL...
SELECT punches.[Emp#], punches.Date, punches.I, punches.O
FROM punches
WHERE (((punches.[Emp#])=[Enter Employee Number]));

Im trying to build a timeclock, the employee will enter an Emp# on a form,
the form brings up the query and goes to the last record.
(((DoCmd.OpenQuery "punch qry", acNormal, acEdit
' Goes to last record in qry
DoCmd.GoToRecord acQuery, "punch qry", acLast)))
Here is where the problem starts, I want the code to evaluate the "Last"
record of the query and determine which field is empty I or O (In and Out)
then go to a new record and punch the current time in the opposite field.
(i.e. if the "Last" record was I the new record would be O)

Thank you so much for your help.

Jeff Boyce said:
Perhaps if you posted your query's SQL it would help make it clearer. I'm
afraid I'm still not following you...

--
Good luck

Jeff Boyce
<Access MVP>

date
and time
in to
get that
code set
the
Click to expand...
Click to expand...
 
Back
Top