Which number type to use? (Access 97, so SR)

Topic

New Reply

Hey All,

I’m still working on my magazine collection.

My quandry is this;

If I set my Issue Numbers to text, and then sort, I get results like: 1,11,12,2,3,31,32. I need to be able to put alpha characters and punctuation in some issue numbers, that’s why I chose text originally.

But, my list is obviously not sorted properly…

If I set my Issue Numbers to numeric, I get a proper sorting, but then I can’t enter my alpha and punctuation.

Can anyone give some advice on which road to take, and how to work around either problem?

Reply | Quote

Replies

In a query, use the following expression

SortOrder : Space(3 - Len([IssueNumber]) & [IssueNumber]

Replace the 3 with the maximum length of your IssueNumber field.
This will sort the numbers correctly but if you have
10
11
10A
the 10A will be sorted after the 11.
In this case I suggest you to use two fields, one for the numbers, and one for the Alpha characters.

Reply | Quote

What rules can you rely on as to what an issue “number” can look like? That is, does it ALWAYS start with a number? Based on what you have shown so far, I’d use a 2-field approach; that is an IssueNumber (being numeric) and IssueSuffix (text), in which they together form the PK.

Reply | Quote

Folx,

Here’s a sample list of my data.
Preview
Prelude
Prequel
0
1
2
3
4
43/2
49/4
Alpha
Omega
1-Alpha
0-Omega

I know this is a rather eclectic list, but it is accurate and representational.

With some of the entries, the 2 field idea would work. But it may not work with the the “Alpha” issues for instance.

Though, on the other hand, I suppose if I had 2 fields, and left the first one blank, and the second was a text field containing Alpha, it may work.

But how do I compare 43/4 with Alpha?

There will be a Prelude, 1,2,3,50,51,52, Omega for instance, all with the same title and publisher.

I guess the 2 field system would probably be the best path to go. Anyone else have any more thoughts?

Reply | Quote

I think the 2 field approach is about all you can do. I’d make both text fields, however, setting their “Required” property to Yes and “Allow Zero Length String” to Yes. I would right-justify the first field, and try to only put numbers in it. For “Prelude”, etc, I’d make the 1st field blank and put “Prelude” in the 2nd field.

Reply | Quote

You can order your data with Val(Field), (Field). Then your data: 1a,11b, 12a, 2, 20, 21, etc. will sort in the expected 1a, 2, 11b, 12a, 20, 21 order. Look up “Val” in help. It extracts the numeric part of the field up to the first non-numeric character.

Reply | Quote

Wulfgar
If you use two fields you can amalgamate both fields into one to show the number as you want it (IssueNo =Field1 & Field2). If you can automate the entry to the two fields in some way you can then hide them and of course you sort on Field1 assuming this is the one with the number.
Peter

Reply | Quote