Getting Access to count to 10

  • Thread starter Thread starter Anne
  • Start date Start date
A

Anne

Hi! I've developed a primary key for my database that is
essentially a cataloging process incorporating numbers and
letters. The first record is C16S1F1, the second is
C16S1F2, etc. When I get to record C16S1F10, it saves
after F1 instead of F9. Is there some way to tell access
to count correctly or, better yet, to save in the order I
enter the records?
 
I believe Access does save in the order in which you enter records.

What you are talking about is the sort order when you look at the records.
You can determine the sort order (regardless of how they are save
internally).

You want

C16S1F1
C16S1F2
C16S1F3
..
..
..
C16S1F9
C16S1F10


Instead of

C16S1F1
C16S1F10
C16S1F2
..
..
..
C16S1F9

I don't know if there is a way to fix this unless you break up that 'key'
into multiple fields in your database and malke the secquence number a
numeric field. Then in your reports, queries, forms, etc, you could simply
concantenate the two fields.

From Access help...

Numbers stored in Text fields are sorted as strings of characters, not
numeric values. Therefore, to sort them in numeric order, all text strings
must be the same length with shorter numbers padded with leading zeros. For
example, the result of an ascending sort of the text strings "1", "2", "11",
and "22" will be "1", "11", "2", "22". You must pad the single-digit numbers
with a leading zero for the strings to be sorted properly: "01", "02", "11",
"22". For fields that don't contain Null values, another solution would be
to sort on the numeric values of the strings by using the Val function. For
example, if the Age column is a Text field that contains numeric values,
specifying Val([Age]) in a Field cell and specifying a sort order in its
Sort cell will put the records in the correct order. If you are only storing
numbers or dates in a Text field, consider changing the field's data type to
Number, Currency, or Date/Time in the table where the field is stored. Then,
when you sort on the field, the numbers or dates will sort in numeric or
date order without requiring leading zeros.


Hi! I've developed a primary key for my database that is
essentially a cataloging process incorporating numbers and
letters. The first record is C16S1F1, the second is
C16S1F2, etc. When I get to record C16S1F10, it saves
after F1 instead of F9. Is there some way to tell access
to count correctly or, better yet, to save in the order I
enter the records?
 
Hi Anne,

Access is sorting correctly. You could make it sort the way you want by
formatting that last number with leading zeros. Then ...F001 will be
followed by F002, not F010.

HTH
 
I believe Access does save in the order in which you enter records.

Normally they are sorted to primary key order when you compact.
So the default order in any table is usually a mixture of primary
key order and data entry order. Default order is normally preserved
when you open a table in datasheet view, but is lost in complex
queries, or when you save or specify a different view order.

(david)
 
Back
Top