How to sort a text field that contains numbers only?

[clarencelai]

Hi..

I need to query a TEXT field in an ODBC table (Oracle) that contained numbers.

1) How can I sort those numbers in numeric order? Currently, number 1 ~ 11 is sorted in this order.. 1, 10, 11, 2, 3... 9.

2) If #1 is not possible, I would like to export the query result to a table. How do I then change the data type from TEXT to NUMBER using VB codes? Currently, I am changing this manually which is not user-friendly.

Cheers!
Clarence

[JamieHowarth0]

Hi Clarence,

1) is not possible because the database recognises the field as type TEXT so it sorts it alphabetically (by ASCII codes like a dictionary), not numerically - so a bit like sorting a, ab, adbc, b, ba, be, etc.

2) is possible using a very swift, automatic and simple bit of ASP:

[code=asp]
Set oConn = Server.CreateOb ject("ADODB.Con nection")
sConn = "Driver={Micros oft ODBC Driver for Oracle};DBQ=Map ToYourDB"
oConn.Open sConn

'First: you want to add a new field to your table with type INTEGER
Set rs = Server.CreateOb ject("ADODB.Rec ordset")
rsSQL = "ALTER TABLE yourtable ADD COLUMN ('new_integer_c olumn', int);"
rs.Open oConn, rsSQL
rs.Close
'Done

'Second: you want to retrieve your text value and store it as integer
rsSQL = "SELECT * FROM yourtable;"
rs.Open oConn, rsSQL

'Set value of new column to be the INTEGER value of the old text column (VB has the built-in function CInt - short for Convert to Integer type)
For I = 1 To rs.RecordCount
rs("new_integer _column") = CInt(rs("old_te xt_column")
rs.Update
rs.MoveNext
Next
rs.Close
'Done

'Third: you want to (ideally) delete your text column as it's now redundant.

rsSQL = "ALTER TABLE yourtable DROP COLUMN ('old_text_colu mn');"
rs.Open oConn, rsSQL
rs.Close
'Done!
%>
[/code]

Obviously this needs some tweaking to make it work for your own circumstances and server setup but it should be sufficient.

Hope it helps.

medicineworker

P.S. Should really be in the Oracle or VB/ASP forum :-)

[clarencelai]

Hi medicineworker,

Thanks for your prompt advise.

I may not have made myself clear in my questions.

I am developing a MS Access database that requires me to Link Oracle tables via ODBC.

Thus, if #1 is not feasible, I would then need to export the Oracle table to a new table within MS Access. How do I then change the data type of this particular text field to a number field?

Cheers!
Clarence

Hi Clarence,

1) is not possible because the database recognises the field as type TEXT so it sorts it alphabetically (by ASCII codes like a dictionary), not numerically - so a bit like sorting a, ab, adbc, b, ba, be, etc.

2) is possible using a very swift, automatic and simple bit of ASP:

[code=asp]
Set oConn = Server.CreateOb ject("ADODB.Con nection")
sConn = "Driver={Micros oft ODBC Driver for Oracle};DBQ=Map ToYourDB"
oConn.Open sConn

'First: you want to add a new field to your table with type INTEGER
Set rs = Server.CreateOb ject("ADODB.Rec ordset")
rsSQL = "ALTER TABLE yourtable ADD COLUMN ('new_integer_c olumn', int);"
rs.Open oConn, rsSQL
rs.Close
'Done

'Second: you want to retrieve your text value and store it as integer
rsSQL = "SELECT * FROM yourtable;"
rs.Open oConn, rsSQL

'Set value of new column to be the INTEGER value of the old text column (VB has the built-in function CInt - short for Convert to Integer type)
For I = 1 To rs.RecordCount
rs("new_integer _column") = CInt(rs("old_te xt_column")
rs.Update
rs.MoveNext
Next
rs.Close
'Done

'Third: you want to (ideally) delete your text column as it's now redundant.

rsSQL = "ALTER TABLE yourtable DROP COLUMN ('old_text_colu mn');"
rs.Open oConn, rsSQL
rs.Close
'Done!
%>
[/code]

Obviously this needs some tweaking to make it work for your own circumstances and server setup but it should be sufficient.

Hope it helps.

medicineworker

P.S. Should really be in the Oracle or VB/ASP forum :-)

[JamieHowarth0]

Hi Clarence,

Aha! A much easier solution is in order!

If your linked table is already set up, create a query in Access, with the following SQL code:

[code=sql]SELECT CInt(old_text_f ield) AS new_int_field, field1, field2.... etc. etc. FROM linked_oracle_t able ORDER BY new_int_field;[/code]

When the query runs, it will convert all the text values in the linked table into integer values (in a temporary column called new_int_field) and automatically sort upon that field (which will be numeric, which gets the order you require).

The one great thing about SQL (especially Access SQL) is that it contains a number of VB-esque functions for data conversion, which (as shown in this case) can be incredibly handy!

Should sort your problem out quite nicely!

If you do need to import the table from Oracle into Access (thus removing the linked table bit), you create the table but instead of the text field being specified as Text in Access' Table Designer, specify it as Number, then copy and paste your existing records in.

All the best,

medicineworker

[clarencelai]

Hi medicineworker,

Thanks again.

When I run the suggested SQL code (in Query), i was prompted to enter parameter value for new_int_field.

My sql code was modified as follow.

SELECT CInt([MYFIELD]) AS new_int_field
FROM MYODBC_TABLE
ORDER BY new_int_field;

Kindly advise what could have gone wrong.

Cheers!
Clarence

Hi Clarence,

Aha! A much easier solution is in order!

If your linked table is already set up, create a query in Access, with the following SQL code:

[code=sql]SELECT CInt(old_text_f ield) AS new_int_field, field1, field2.... etc. etc. FROM linked_oracle_t able ORDER BY new_int_field;[/code]

When the query runs, it will convert all the text values in the linked table into integer values (in a temporary column called new_int_field) and automatically sort upon that field (which will be numeric, which gets the order you require).

The one great thing about SQL (especially Access SQL) is that it contains a number of VB-esque functions for data conversion, which (as shown in this case) can be incredibly handy!

Should sort your problem out quite nicely!

If you do need to import the table from Oracle into Access (thus removing the linked table bit), you create the table but instead of the text field being specified as Text in Access' Table Designer, specify it as Number, then copy and paste your existing records in.

All the best,

medicineworker

[JamieHowarth0]

Hi Clarence,

Try:
[code=sql] SELECT (CInt([MYFIELD]) AS [new_int_field]) FROM MYODBC_TABLE ORDER BY new_int_field;[/code]

Notice addition of square brackets around new_int_field and normal brackets around the field list.

Any further problems then let me know.

Best regards,
medicineworker

[clarencelai]

Hi medicineworker,

I have tried the revised code.

There seems to be some syntax error (missing operator) in the query expression. I tried to troubleshoot it but to no avail.

Cheers!
Clarence

Hi Clarence,

Try:
[code=sql] SELECT (CInt([MYFIELD]) AS [new_int_field]) FROM MYODBC_TABLE ORDER BY new_int_field;[/code]

Notice addition of square brackets around new_int_field and normal brackets around the field list.

Any further problems then let me know.

Best regards,
medicineworker

[JamieHowarth0]

Hi Clarence,

I just tried it myself, for some reason Access is being a bit weird about handling it but I finally got a piece of working code:
[code=sql]SELECT CInt(DummyID) AS Expr1 FROM tbl_Test ORDER BY CInt(DummyID);[/code]

For some reason Access accepts the Expr1 (temp column name) but then refuses to sort on it...

Anyway, it works, so should work for your code.

Best regards,

medicineworker

[clarencelai]

Hi medicineworker,

Great! It's now running!

Thanks for your great help!

Cheers!
Clarence

Hi Clarence,

I just tried it myself, for some reason Access is being a bit weird about handling it but I finally got a piece of working code:
[code=sql]SELECT CInt(DummyID) AS Expr1 FROM tbl_Test ORDER BY CInt(DummyID);[/code]

For some reason Access accepts the Expr1 (temp column name) but then refuses to sort on it...

Anyway, it works, so should work for your code.

Best regards,

medicineworker