I have a db in which the client enters a "Unique Block Number" which
is allocated by the client. Currently it is a text field. which is set
to "NO Duplicates". However, there are ocassions when the client wants
to use a secondary number e.g. "/2" . As an example -

339
339/2
626
626/1
626/2
626/3
507/1
507/2
507
etc etc

From my limited understanding, if the field is set as TEXT then you
lose the ability to ASC and DES in numerical order etc. If the "/" is
used in a Number field then it will not be accepted. Does anyone have
any ideas on how to set this up. There is so much data currently in
use that there is little chance of convinving them to change to
something else. TIA - Ray

wattles (AT) xtra (DOT) co.nz (Ray) wrote in
news:4205783b.0307271811.cc9d02a (AT) posting (DOT) google.com:

OTTOMH

Assuming version >= 2k how about

ORDER BY CDbl(Replace([Unique Block Number], "/", "."))




--
Lyle

"Ray" <wattles (AT) xtra (DOT) co.nz> wrote

Add another column and create a unique index on (Block Number, Other Number)

For sorting, just make an exprssion in the query builder like:

mysort:sortBlock([blockNumber])

Now, add the follwing function in a public module:

Public Function sortBlock(vData As Variant) As Variant

If IsNull(vData) = True Then Exit Function
sortBlock = Split(vData, "/")(0)
sortBlock = Format(sortBlock, "00000")

End Function


--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
kallal (AT) msn (DOT) com
http://www.attcanada.net/~kallal.msn

"Albert D. Kallal" <kallal (AT) msn (DOT) com> wrote

Just be aware that on a large table this method could become slow.

Good point. I did not look that close You are correct! (thank you for that
catch Lyle).

However, it still easy to sort on on that. Since it is the case, then then I
would simply extend the 2nd pard to 3 digits.

Like:

Public Function sortBlock(vData As Variant) As Variant

Dim sbuf As Variant
If IsNull(vData) = True Then Exit Function

sbuf = Split(vData, "/")
sortBlock = Format(sbuf(0), "00000")
If UBound(sbuf, 1) > 0 Then
sortBlock = sortBlock & Format(sbuf(1), "000")
Else
sortBlock = sortBlock & "000"
End If
end function


--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
kallal (AT) msn (DOT) com
http://www.attcanada.net/~kallal.msn