sort report by Time (HH.mm), when in database time column is type of Text

Nisam Ja

I have to sort report table by column where times in Access database are entered as Text. There is a problem because my times are ordered as text, like 10.30, 11.20, 23.40, 7.10, 9.25, and I like them to be ordered from morning to midnight (7.10, 9.25, 10.30, 11.20, 23.40). I'd like to know if it's possible to sort report in correct way without changing column type in database.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nisam Ja16y ago
I understand that it suppose to be temporary field, but I don't know how to create it and use it in report.

I've tried this, but it didn't worked (there is an error):

OleDb.OleDbConnection myCon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\myDb.mdb");

            DataTable tb = voznjaDataSet.Tables["myTable"];
            tb.Columns.Add("newField");

        string dbcommand = "UPDATE myTable SET newField = (timeField.ToDateTime("HH.mm"))"; //timeField is type of string

            da = new OleDbDataAdapter(dbcommand, myCon);

            da.Fill(myDataSet);
Sam Hobbs16y ago
I did not suggest that you add a field to the table on disk. I mean that you can write SQL such that a field is created from the SQL. The field would be temporary and only exist in memory, never in the disk file. Does that sound useful?

You might get more help with SQL in DB Talks.
Nisam Ja16y ago
Did you min that I have to temporary add field (type of DateTime) which I have to populate with values from my field where times are entered as Text, and sort by that new field?
Sam Hobbs16y ago
I hope someone helps you more than I can, but I hope I am able to help you some.

You probably can use a SQL statement that creates a date field from the date string and then sort on that and you don't need to show that field in the report. I don't know if htere is a function you can call in SQL to convert to a date from a string but I assume there is.