Sorting a Custom AutoNumber Field

[J. Mullenbach]

Hello All,

I can't say thanks enough for all the help provided here. I look forward to
the day when I can spend more time providing answers than questions, but the
light at the end of that tunnel is still rather dim at the present...

I created a custom AutoNumber field (Text field) for a client based on a
MMYY000 format so that new records are assigned a unique value based on the
current month & year, incrementing the count by one for each additional
record added during the same month. So now, of course, the client wants to
know why the fields are sorting like:
0100001
0100002
0101001
0200001
....

Is there any way to create a custom sort so that the first four characters
are treated as a date in MMYY format and the last three treated as a number
in 000 format, descending so that the most recently added record is displayed
first?

For ten bonus points: The client's historical records are in the above
format, however, some of them are missing the leading zero and some are not.
Is there any way to programatically check the length of this field for each
record and add a zero to the beginning of the string if the Len value is 6
instead of 7?

 

[Dirk Goldgar]

Hello All,

I can't say thanks enough for all the help provided here. I look
forward to the day when I can spend more time providing answers than
questions, but the light at the end of that tunnel is still rather
dim at the present...

I created a custom AutoNumber field (Text field) for a client based
on a MMYY000 format so that new records are assigned a unique value
based on the current month & year, incrementing the count by one for
each additional record added during the same month. So now, of
course, the client wants to know why the fields are sorting like:
0100001
0100002
0101001
0200001
...

Is there any way to create a custom sort so that the first four
characters are treated as a date in MMYY format and the last three
treated as a number in 000 format, descending so that the most
recently added record is displayed first?

For ten bonus points: The client's historical records are in the
above format, however, some of them are missing the leading zero and
some are not. Is there any way to programatically check the length of
this field for each record and add a zero to the beginning of the
string if the Len value is 6 instead of 7?

Yes, it's all possible, but it will be very inefficient, because you'll
need to call at least one VBA function (built-in or user-defined) for
every record to be sorted, and you can't make use of any index on that
field. Much better would be to have the month and year, or maybe the
creation date alone, stored separately in the table, and have indexes on
those fields. Depending on your needs for this sorting, it may well be
worth the trouble to retroactively add the field(s) and indexes, and use
and update query to populate them.

Failing that, it would be straightforward if your custom autonumber
field were in YYMM000 format, rather than MMYY000 format. The only
problem then would come if you have dates before the year 2000, which
would sort out of order (because 99 is greater than 00). But I guess
you don't have any control over that.

If you don't want to change your table and index structure, then you can
do something like this:

ORDER BY
CDate(Mid(Right("0" & IDField, 7), 3, 2) & "/" & Left(Right("0"
& IDField, 7), 2)) DESC, Right(IDField, 3) DESC

That's untested. I would be inclined to write my own function that
translates the IDField into YYYYMM000 format, and sort on the result of
that function, rather than using the complex expression in the SQL
itself. Either way, it's going to be inefficient compared to sorting on
an indexed field, or even a simple unindexed field.

 

[Arvin Meyer [MVP]]

That illustrates one of the problems with using a concatenated key. You can
parse the column into 2 of them in a query and sort those 2 columns. The
first 4 digits aren't really a date. 0100 may be January 2000 (or 1900) but
that isn't a date. First, use an Update query to massage the data into the
correct format:

UPDATE tblMyData SET tblMyData.RecordID = Format([RecordID],"0000000")
WHERE ((Len([RecordID])="6"));

10 points

Then write another query to parse and sort the field:

SELECT tblMyData.*
FROM tblMyData
ORDER BY Left([RecordID],4), Right([RecordID],3);

Game.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

DateSort:

 

[Dirk Goldgar]

That illustrates one of the problems with using a concatenated key.
You can parse the column into 2 of them in a query and sort those 2
columns. The first 4 digits aren't really a date. 0100 may be January
2000 (or 1900) but that isn't a date. First, use an Update query to
massage the data into the correct format:

UPDATE tblMyData SET tblMyData.RecordID = Format([RecordID],"0000000")
WHERE ((Len([RecordID])="6"));

You can only do that if conditions permit you to change the historical
data. If there are (a) related records using these as foreign keys, and
without cascading update in effect, or (b) printed or other historical
documents that refer to these IDs, changing the IDs may be a major
no-no.

10 points

Maybe ...

Then write another query to parse and sort the field:

SELECT tblMyData.*
FROM tblMyData
ORDER BY Left([RecordID],4), Right([RecordID],3);

Game.

Bzzzt! There's a flag on the play. <g> Because the format is MMYY,
that will be sort by month, then year, not year then month as requested.
Also, we need the DESC keyword to put the most recent records at the
top.

 

[J. Mullenbach]

Thanks for the thorough explanation. Since the database is in a multi-user
environment, I think I'll try sorting by a different date field (closest to
the creation date) as well as the custom auto-number to get as close as
possible without the big performance penalty.

I appreciate the help.

 

[Arvin Meyer [MVP]]

Yes we need the DESC keyword, thanks. No we don't want to change the key,
merely add 2 columns for the sort. If the key were changed in the first
query, we'd be unable to run the second. I'm sorry if the answer was
ambiguous about that.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

That illustrates one of the problems with using a concatenated key.
You can parse the column into 2 of them in a query and sort those 2
columns. The first 4 digits aren't really a date. 0100 may be January
2000 (or 1900) but that isn't a date. First, use an Update query to
massage the data into the correct format:

UPDATE tblMyData SET tblMyData.RecordID = Format([RecordID],"0000000")
WHERE ((Len([RecordID])="6"));

You can only do that if conditions permit you to change the historical
data. If there are (a) related records using these as foreign keys, and
without cascading update in effect, or (b) printed or other historical
documents that refer to these IDs, changing the IDs may be a major
no-no.

10 points

Maybe ...

Then write another query to parse and sort the field:

SELECT tblMyData.*
FROM tblMyData
ORDER BY Left([RecordID],4), Right([RecordID],3);

Game.

Bzzzt! There's a flag on the play. <g> Because the format is MMYY,
that will be sort by month, then year, not year then month as requested.
Also, we need the DESC keyword to put the most recent records at the
top.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[J. Mullenbach]

Thanks for the clarification. Unfortunately, I get a syntax error on the
Update code:

UPDATE tblMyData SET tblMyData.RecordID = Format([RecordID],"0000000")
WHERE ((Len([RecordID])="6"));

I've tried adding/removing sets of parentheses around the
((Len([RecordID])="6")) part and also tried changing/removing the quotes
around the number 6 but it gives me the same syntax error each time. Any
ideas?

Here's the actual code I'm using (without error handling):
Sub UpdateField()
Dim cnCurrent As ADODB.Connection
Dim strSQL As String

Set cnCurrent = CurrentProject.Connection
strSQL = "UPDATE [tblGrievance] SET [GrievanceNum] =
Format([GrievanceNum], "0000000") " & _
"WHERE ((Len([GrievanceNum])="6"));"

cnCurrent.Execute strSQL
MsgBox "Update is complete."

cnCurrent.Close
Set cnCurrent = Nothing
End Sub

It seems that I've run across this missing zero issue several times in the
last couple years so I'm very interested in learning how to correct it.

Yes we need the DESC keyword, thanks. No we don't want to change the key,
merely add 2 columns for the sort. If the key were changed in the first
query, we'd be unable to run the second. I'm sorry if the answer was
ambiguous about that.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

That illustrates one of the problems with using a concatenated key.
You can parse the column into 2 of them in a query and sort those 2
columns. The first 4 digits aren't really a date. 0100 may be January
2000 (or 1900) but that isn't a date. First, use an Update query to
massage the data into the correct format:

UPDATE tblMyData SET tblMyData.RecordID = Format([RecordID],"0000000")
WHERE ((Len([RecordID])="6"));

You can only do that if conditions permit you to change the historical
data. If there are (a) related records using these as foreign keys, and
without cascading update in effect, or (b) printed or other historical
documents that refer to these IDs, changing the IDs may be a major
no-no.

10 points

Maybe ...

Then write another query to parse and sort the field:

SELECT tblMyData.*
FROM tblMyData
ORDER BY Left([RecordID],4), Right([RecordID],3);

Game.

Bzzzt! There's a flag on the play. <g> Because the format is MMYY,
that will be sort by month, then year, not year then month as requested.
Also, we need the DESC keyword to put the most recent records at the
top.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Thanks for the clarification. Unfortunately, I get a syntax error on
the Update code:

UPDATE tblMyData SET tblMyData.RecordID =
Format([RecordID],"0000000") WHERE ((Len([RecordID])="6"));

I've tried adding/removing sets of parentheses around the
((Len([RecordID])="6")) part and also tried changing/removing the
quotes around the number 6 but it gives me the same syntax error each
time. Any ideas?

Here's the actual code I'm using (without error handling):
Sub UpdateField()
Dim cnCurrent As ADODB.Connection
Dim strSQL As String

Set cnCurrent = CurrentProject.Connection
strSQL = "UPDATE [tblGrievance] SET [GrievanceNum] =
Format([GrievanceNum], "0000000") " & _
"WHERE ((Len([GrievanceNum])="6"));"

cnCurrent.Execute strSQL
MsgBox "Update is complete."

cnCurrent.Close
Set cnCurrent = Nothing
End Sub

It seems that I've run across this missing zero issue several times
in the last couple years so I'm very interested in learning how to
correct it.

It's a quotes-within-quotes problem. Try this:

strSQL = _
"UPDATE [tblGrievance] " & _
"SET [GrievanceNum] = " & _
"Format([GrievanceNum], ""0000000"") " & _
"WHERE Len([GrievanceNum])=6;"

 

[J. Mullenbach]

Thanks, Mr. Goldgar - worked like a champ.

Can you explain why the extra quotes were necessary in this case?

Thanks for the clarification. Unfortunately, I get a syntax error on
the Update code:

UPDATE tblMyData SET tblMyData.RecordID =
Format([RecordID],"0000000") WHERE ((Len([RecordID])="6"));

I've tried adding/removing sets of parentheses around the
((Len([RecordID])="6")) part and also tried changing/removing the
quotes around the number 6 but it gives me the same syntax error each
time. Any ideas?

Here's the actual code I'm using (without error handling):
Sub UpdateField()
Dim cnCurrent As ADODB.Connection
Dim strSQL As String

Set cnCurrent = CurrentProject.Connection
strSQL = "UPDATE [tblGrievance] SET [GrievanceNum] =
Format([GrievanceNum], "0000000") " & _
"WHERE ((Len([GrievanceNum])="6"));"

cnCurrent.Execute strSQL
MsgBox "Update is complete."

cnCurrent.Close
Set cnCurrent = Nothing
End Sub

It seems that I've run across this missing zero issue several times
in the last couple years so I'm very interested in learning how to
correct it.

It's a quotes-within-quotes problem. Try this:

strSQL = _
"UPDATE [tblGrievance] " & _
"SET [GrievanceNum] = " & _
"Format([GrievanceNum], ""0000000"") " & _
"WHERE Len([GrievanceNum])=6;"

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Thanks, Mr. Goldgar - worked like a champ.

Can you explain why the extra quotes were necessary in this case?

It's a quotes-within-quotes problem. Try this:

strSQL = _
"UPDATE [tblGrievance] " & _
"SET [GrievanceNum] = " & _
"Format([GrievanceNum], ""0000000"") " & _
"WHERE Len([GrievanceNum])=6;"

You're building the SQL statement from a VBA expression concatenating a
number of string literals; among them this (original):

"UPDATE [tblGrievance] SET [GrievanceNum] = Format([GrievanceNum],

"0000000") "

If you look closely at that, you'll see that it has a form that can be
simplified, for example purposes, to this:

"aaaa"bbbb"cccc"

That's not a valid string literal. As far as VBA can tell, the literal
ends after the second quote -- that is, after "aaaa" -- but then there's
a lot of extra stuff stuck on the end. VBA doesn't know what to make of
that.

What you meant to do was have the second and third quote characters be
embedded in the string. In a string literal, the only way to do that is
to double them up. There's a special provision in the parser that
understands two consecutive quotes within a quoted literal as one quote
that is part of the literal. So to assign a value of

aaaa"bbbb"cccc

to a string variable from a string literal, we have to wrap it in quotes
(to delimit the literal), *and* double up the internal quotes:

strMyVar = "aaaa""bbbb""cccc"

That's just the way string literals work.

An alternative, by the way, is to break the literal up into yet smaller
pieces concatenated together, and use the Chr() function to define the
quote character. Chr(34) is the double-quote character. So one could
write:

strMyVar = "aaaa" & Chr(34) & "bbbb" & Chr(34) & "cccc"

I often do it this way, because I find the doubled-up quotes hard to
read and hard for others to understand.