Custom Counter Question (Repost)

[AccessIM]

Hello Everyone -

I haven't received a response back to one of my posts so I am re-posting
with the hope that someone will pick up the new post.

I have created a field in a query to assign a sequence number to records.
The code is below:

Sequence_Number: SELECT Count (*) FROM [qryTotalDetail] AS [XX] WHERE
[qryTotalDetail].[SSN]=[XX].[SSN] AND [qryTotalDetail].[INCIDENTDATE]
&[qryTotalDetail].[TYPE]<=[XX].[INCIDENTDATE] & [XX].[TYPE])-1

I was entering past information as a test for when the database is running
live and it ran perfect for 6 weeks and then, for some reason, on the 7th
week, the sequence number of one employee was out of order. Below are the
results I saw:

EmployeeID SSN IncidentDate Seq# Type Points
AccumTotal
6 111223333 8/13/09 1 LATE 0.50
1.00
6 111223333 8/5/09 0 LATE 0.50
1.50
6 111223333 7/30/09 2 LATE 0.50
0.50
6 111223333 4/6/98 3 START 0.00
0.00

As you can see, the top two sequence numbers are reversed and the AccumTotal
field, though correct, is also in the wrong order.

I have all but given up and moved on since I have a deadline nearing for
this project but I still believe this is the best way to design the query.

Could anyone help with some suggestions for why this may have happened and
how I might correct it? Thank you so much.

 

[John W. Vinson]

Hello Everyone -

I haven't received a response back to one of my posts so I am re-posting
with the hope that someone will pick up the new post.

I have created a field in a query to assign a sequence number to records.
The code is below:

Sequence_Number: SELECT Count (*) FROM [qryTotalDetail] AS [XX] WHERE
[qryTotalDetail].[SSN]=[XX].[SSN] AND [qryTotalDetail].[INCIDENTDATE]
&[qryTotalDetail].[TYPE]<=[XX].[INCIDENTDATE] & [XX].[TYPE])-1

I was entering past information as a test for when the database is running
live and it ran perfect for 6 weeks and then, for some reason, on the 7th
week, the sequence number of one employee was out of order. Below are the
results I saw:

EmployeeID SSN IncidentDate Seq# Type Points
AccumTotal
6 111223333 8/13/09 1 LATE 0.50
1.00
6 111223333 8/5/09 0 LATE 0.50
1.50
6 111223333 7/30/09 2 LATE 0.50
0.50
6 111223333 4/6/98 3 START 0.00
0.00

As you can see, the top two sequence numbers are reversed and the AccumTotal
field, though correct, is also in the wrong order.

I have all but given up and moved on since I have a deadline nearing for
this project but I still believe this is the best way to design the query.

Could anyone help with some suggestions for why this may have happened and
how I might correct it? Thank you so much.

Your expression [qryTotalDetail].[INCIDENTDATE]&[qryTotalDetail].[TYPE] is
converting a date and a string to... a string.

The text string "8/13/09LATE" in fact does sort before "8/5/09LATE", not
chronologically, since the first different character is 1 and 5 respectively.

Try specifying the rank order on the two fields separately rather than
concatenating them.

 

[Duane Hookom]

You are converting a general date field and changing it to a string. You then
expect it to sort like a date. 8/1 comes befor 8/5. If you really want to
store or compare dates like this, change their format so the order is correct
as a string Format([datefld],"yyyymmdd")

 

[AccessIM]

That was it. Thank you Duane.

You are converting a general date field and changing it to a string. You then
expect it to sort like a date. 8/1 comes befor 8/5. If you really want to
store or compare dates like this, change their format so the order is correct
as a string Format([datefld],"yyyymmdd")

--
Duane Hookom
Microsoft Access MVP

Hello Everyone -

I haven't received a response back to one of my posts so I am re-posting
with the hope that someone will pick up the new post.

I have created a field in a query to assign a sequence number to records.
The code is below:

Sequence_Number: SELECT Count (*) FROM [qryTotalDetail] AS [XX] WHERE
[qryTotalDetail].[SSN]=[XX].[SSN] AND [qryTotalDetail].[INCIDENTDATE]
&[qryTotalDetail].[TYPE]<=[XX].[INCIDENTDATE] & [XX].[TYPE])-1

I was entering past information as a test for when the database is running
live and it ran perfect for 6 weeks and then, for some reason, on the 7th
week, the sequence number of one employee was out of order. Below are the
results I saw:

EmployeeID SSN IncidentDate Seq# Type Points
AccumTotal
6 111223333 8/13/09 1 LATE 0.50
1.00
6 111223333 8/5/09 0 LATE 0.50
1.50
6 111223333 7/30/09 2 LATE 0.50
0.50
6 111223333 4/6/98 3 START 0.00
0.00

As you can see, the top two sequence numbers are reversed and the AccumTotal
field, though correct, is also in the wrong order.

I have all but given up and moved on since I have a deadline nearing for
this project but I still believe this is the best way to design the query.

Could anyone help with some suggestions for why this may have happened and
how I might correct it? Thank you so much.