Sorting Data

[Guest]

Data is '01-06' to '100-06'.

Data is primary key, data type is text, field size is 6 and default value is
"00-06".

Problem is '100-06' is before '10-06' and of course should be after '99-06'.

How do I set this up correctly?

TIA

 

[Brendan Reynolds]

You could use two integer fields instead of one text field, one for the part
of the number before the hyphen, and one for the part of the number after
the hyphen. That way you can easily sort on the two fields ("ORDER BY
FirstField, SecondField") and string them together whenever necessary with
an expressions such as FirstField & "-" & SecondField.

With the single text field, you could do something like this ...

SELECT Test.*
FROM Test
ORDER BY Val(Left$([TestText],InStr(1,[TestText],"-")-1)),
Val(Mid$([TestText],InStr(1,[TestText],"-")+1));

But this will be much less efficient, as the database engine will not be
able to make use of any indexes when sorting on those expressions.

 

[Guest]

I am sort of a 'newbie' so bear with me.

I am thinking of using the single field option so I don't have to reorganize
the database, but the indexing I am not sure about. Now I have 'Yes(No
Duplicates) for the indexed choice and I only sort by the entire number.

I don't see a 'Test' choice, so could you give me a little more instruction.

TIA

You could use two integer fields instead of one text field, one for the part
of the number before the hyphen, and one for the part of the number after
the hyphen. That way you can easily sort on the two fields ("ORDER BY
FirstField, SecondField") and string them together whenever necessary with
an expressions such as FirstField & "-" & SecondField.

With the single text field, you could do something like this ...

SELECT Test.*
FROM Test
ORDER BY Val(Left$([TestText],InStr(1,[TestText],"-")-1)),
Val(Mid$([TestText],InStr(1,[TestText],"-")+1));

But this will be much less efficient, as the database engine will not be
able to make use of any indexes when sorting on those expressions.

--
Brendan Reynolds
Access MVP

Data is '01-06' to '100-06'.

Data is primary key, data type is text, field size is 6 and default value
is
"00-06".

Problem is '100-06' is before '10-06' and of course should be after
'99-06'.

How do I set this up correctly?

TIA

 

[Guest]

Add leading xero to '01-06' so it is '001-06' and '10-06' becomes '010-06'.

 

[Guest]

I may try this if nothing else works as I'd rather not edit 99 entries. Also,
I am not sure how it would affect other queries, reports etc. I may wait
until our IT person gets back.

Thanks.

 

[Guest]

Editing 99 records is not necessary.
Just run an update query with update like this --
Right("00"&[YorFieldName],6)

Consider making it 7 characters long at this time --
Right("000"&[YorFieldName],7)