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

Question

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.

Nisam Ja · Answer

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): Normal 0 false false false MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable {mso-style-name:Table Normal; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:Times New Roman; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} 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 Hobbs · Answer

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 Ja · Answer

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 Hobbs · Answer

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.

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