Sort DAO table as numeric value not as string

[Eyal Semo]

Hi all
I have a Snapshot DAO table which I'd like to sort on a field that may have
different kind of values
When it holds a numeric values only, I need it to be sorted as numeric.
I know how to detect num values only
but when I sort, I still get an order like an alfa-num values
(10,11,2,3,4…)
Eny thoughts
eyal

 

[Ken Snell \(MVP\)]

Use a calculated field that converts the field's value into a number, then
sort on that field. For example:

SortField: Val(YourFieldName)

 

[Eyal Semo]

Hi ken
(I probably should have mentioned that I use DISTINCT statement)
I've tryed that already
But got the following error msg:
ORDER BY clause ([SIZE_DESC]*1) conflict with DISTINCT

the DAO select:
Set REL1 = CurrentDb.OpenRecordset("Select DISTINCT SIZE_DESC, SIZE_NO
,season From Barcodes where ITEM_CODE = '" & sku & "' and SIZE_DESC <> '01'
and season='" & season_now & "' ORDER BY [SIZE_DESC]*1", dbOpenSnapshot)

TNX Eyal

 

[Eyal Semo]

I figured it out tnx

Hi ken
(I probably should have mentioned that I use DISTINCT statement)
I've tryed that already
But got the following error msg:
ORDER BY clause ([SIZE_DESC]*1) conflict with DISTINCT

the DAO select:
Set REL1 = CurrentDb.OpenRecordset("Select DISTINCT SIZE_DESC, SIZE_NO
,season From Barcodes where ITEM_CODE = '" & sku & "' and SIZE_DESC <> '01'
and season='" & season_now & "' ORDER BY [SIZE_DESC]*1", dbOpenSnapshot)

TNX Eyal

Use a calculated field that converts the field's value into a number, then
sort on that field. For example:

SortField: Val(YourFieldName)

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/