RePost HELP! Using VB to alternate fields

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

Guest

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 with After Update Properity. The query is
sorted by date and time in asending order (ie latest date and time on the
bottom)
and goes to the last record.
(((DoCmd.OpenQuery "punch qry", acNormal, acEdit
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)

Any ideas would be helpful!
Thank you!
 
Instead of doing the finding of the last record in VBA, I would create a
query that uses the Last option to select just the last record (after
creating a query that finds all records, click the Sum icon and change the
Total option to Last on a field that would return the last record). Then
create an append query that uses this query as its source.

Assuming that either I or O would be null in the found record, the field to
generate the value of "I" in the append query would be something like this:

IIf(isnull(I),Time(),null)

The field for "O" would therefore be something like:

IIf(isnull(O),Time(),null)
 
I created the first query, which retuns the correct record.
SELECT Last(punches.I) AS LastOfI, Last(punches.O) AS LastOfO,
Last(punches.Date) AS LastOfDate, Last(punches.[Emp#]) AS [LastOfEmp#],
Last(punches.ID) AS LastOfID
FROM punches
HAVING (((Last(punches.[Emp#]))=[Enter Employee Number]))
ORDER BY Last(punches.I), Last(punches.O), Last(punches.Date);

The second query, an append query...
INSERT INTO punches ( I )
SELECT punch1.LastOfI
FROM punch1
GROUP BY punch1.LastOfI
HAVING (((punch1.LastOfI)=IIf(IsNull("I"),Time(),Null)));

Brings up a message box saying going to append "0" rows...
Im thinking I made a mistake somewhere
Any ideas?

Thanks.
Brian said:
Instead of doing the finding of the last record in VBA, I would create a
query that uses the Last option to select just the last record (after
creating a query that finds all records, click the Sum icon and change the
Total option to Last on a field that would return the last record). Then
create an append query that uses this query as its source.

Assuming that either I or O would be null in the found record, the field to
generate the value of "I" in the append query would be something like this:

IIf(isnull(I),Time(),null)

The field for "O" would therefore be something like:

IIf(isnull(O),Time(),null)


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 with After Update Properity. The query is
sorted by date and time in asending order (ie latest date and time on the
bottom)
and goes to the last record.
(((DoCmd.OpenQuery "punch qry", acNormal, acEdit
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)

Any ideas would be helpful!
Thank you!
Click to expand...
Click to expand...
 
I do see a couple of errors in the second query

1. The I is in quotes, meaning that SQL thinks it is the letter I, not the
name of the field I. Enclose it in square brackets instead of the quotes.

IIf(IsNull("I"),Time(),Null))); -> IIf(IsNull(),Time(),Null)));

2. The IIf should not be the Criteria for I (HAVING), but in the field
itself (SELECT); that is, the IIf statement is used to generate the value of
I, not filter the value of I. It will generate null if I was blank in the
first query but the value of I if I was not blank.

Part of the SQL should look something like this instead:

INSERT INTO punches ( I )
SELECT IIf(IsNull(),Time(),Null) AS I
FROM punch1;

If there are still problems, try troubleshooting like this: modify the
second (append) query by changing it from an append query to a select query.
Run it to see if it is returning correct information. If #1 works but #2 does
not, then look at the IIF statement in #2 in more depth, retesting until it
returns the correct information, then turn it back into an append query.

Dragon_Silveroak said:
I created the first query, which retuns the correct record.
SELECT Last(punches.I) AS LastOfI, Last(punches.O) AS LastOfO,
Last(punches.Date) AS LastOfDate, Last(punches.[Emp#]) AS [LastOfEmp#],
Last(punches.ID) AS LastOfID
FROM punches
HAVING (((Last(punches.[Emp#]))=[Enter Employee Number]))
ORDER BY Last(punches.I), Last(punches.O), Last(punches.Date);

The second query, an append query...
INSERT INTO punches ( I )
SELECT punch1.LastOfI
FROM punch1
GROUP BY punch1.LastOfI
HAVING (((punch1.LastOfI)=IIf(IsNull("I"),Time(),Null)));

Brings up a message box saying going to append "0" rows...
Im thinking I made a mistake somewhere
Any ideas?

Thanks.
Brian said:
Instead of doing the finding of the last record in VBA, I would create a
query that uses the Last option to select just the last record (after
creating a query that finds all records, click the Sum icon and change the
Total option to Last on a field that would return the last record). Then
create an append query that uses this query as its source.

Assuming that either I or O would be null in the found record, the field to
generate the value of "I" in the append query would be something like this:

IIf(isnull(I),Time(),null)

The field for "O" would therefore be something like:

IIf(isnull(O),Time(),null)


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 with After Update Properity. The query is
sorted by date and time in asending order (ie latest date and time on the
bottom)
and goes to the last record.
(((DoCmd.OpenQuery "punch qry", acNormal, acEdit
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)

Any ideas would be helpful!
Thank you!
Click to expand...
Click to expand...
Click to expand...
 
IT WORKS!!!!!!
THANK YOU THANK YOU THANK YOU!!!!!!!THANK YOU THANK YOU THANK
YOU!!!!!!!THANK YOU THANK YOU THANK YOU!!!!!!!THANK YOU THANK YOU THANK
YOU!!!!!!!THANK YOU THANK YOU THANK YOU!!!!!!!THANK YOU THANK YOU THANK
YOU!!!!!!!THANK YOU THANK YOU THANK YOU!!!!!!!THANK YOU THANK YOU THANK
YOU!!!!!!!THANK YOU THANK YOU THANK YOU!!!!!!!
Brian said:
I do see a couple of errors in the second query

1. The I is in quotes, meaning that SQL thinks it is the letter I, not the
name of the field I. Enclose it in square brackets instead of the quotes.

IIf(IsNull("I"),Time(),Null))); -> IIf(IsNull(),Time(),Null)));

2. The IIf should not be the Criteria for I (HAVING), but in the field
itself (SELECT); that is, the IIf statement is used to generate the value of
I, not filter the value of I. It will generate null if I was blank in the
first query but the value of I if I was not blank.

Part of the SQL should look something like this instead:

INSERT INTO punches ( I )
SELECT IIf(IsNull(),Time(),Null) AS I
FROM punch1;

If there are still problems, try troubleshooting like this: modify the
second (append) query by changing it from an append query to a select query.
Run it to see if it is returning correct information. If #1 works but #2 does
not, then look at the IIF statement in #2 in more depth, retesting until it
returns the correct information, then turn it back into an append query.

Dragon_Silveroak said:
I created the first query, which retuns the correct record.
SELECT Last(punches.I) AS LastOfI, Last(punches.O) AS LastOfO,
Last(punches.Date) AS LastOfDate, Last(punches.[Emp#]) AS [LastOfEmp#],
Last(punches.ID) AS LastOfID
FROM punches
HAVING (((Last(punches.[Emp#]))=[Enter Employee Number]))
ORDER BY Last(punches.I), Last(punches.O), Last(punches.Date);

The second query, an append query...
INSERT INTO punches ( I )
SELECT punch1.LastOfI
FROM punch1
GROUP BY punch1.LastOfI
HAVING (((punch1.LastOfI)=IIf(IsNull("I"),Time(),Null)));

Brings up a message box saying going to append "0" rows...
Im thinking I made a mistake somewhere
Any ideas?

Thanks.
Brian said:
Instead of doing the finding of the last record in VBA, I would create a
query that uses the Last option to select just the last record (after
creating a query that finds all records, click the Sum icon and change the
Total option to Last on a field that would return the last record). Then
create an append query that uses this query as its source.

Assuming that either I or O would be null in the found record, the field to
generate the value of "I" in the append query would be something like this:

IIf(isnull(I),Time(),null)

The field for "O" would therefore be something like:

IIf(isnull(O),Time(),null)


:

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 with After Update Properity. The query is
sorted by date and time in asending order (ie latest date and time on the
bottom)
and goes to the last record.
(((DoCmd.OpenQuery "punch qry", acNormal, acEdit
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)

Any ideas would be helpful!
Thank you!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top