Fetch record by time ASC

Question

Hello Members, Greetings of the day... I wrote a query for fetching records of employees logged in with time ascending order but it is not showing in proper order. First let me tell you the parameteres I am using. table field is Text in MS Access. the time is recorded in the field as '09:15 AM'. the date and the time are in different fields. I want to get the data as Order By EmpTime with ASC so, it should be... 1. ABC 11:30 AM 2. DEF 01:00 PM 3. GHI 02:20 PM but the result shows... 1. DEF 01:00 PM 2. GHI 02:20 PM 3. ABC 11:30 PM it only orders for the digits and not by AM to PM. my query is... "Select EmpName, EmpDesig, EmpTime from LogReg Where TheDate=' "+DateTime.Today.To.ShortDateString()+" ' Order By EmpTime ASC Request your help where I am mistaking. using different combination of 'Order By' and ASC multiple filelds not working. either output is incorrect or error. Thank You. Mayank Jani

Nitin Sontakke · Answer

First the the MOST IMPORTANT thing to understand in computing is, data as seen / understood by computers and data as seen / understood by humans are two different things. ALWAYS REMEMBER THIS. So AM and PM has technically NO MEANING as far as computer is concerned. ONLY YOU associated meaning to it. So you should have stored time as 24 hours time. Meaning 18 instead of 6 PM. Then it becomes sortable. Hope you understand this. Is this change possible? After you do this rest is easy enough.

Mayank Jani · Answer

Hi Amit, I have created two fields, one for date and another for time because the insertion of the date is automatic but time has to entered by the user. If I keep the field as datetime and Format as Medium Time, then I get the exact format what I have choose for the data entry as AM or PM. but at the time of fetch the record, it does not show only time but date and time together. that's why I will have to change the entire structure. can you please explain a query with both example of concatination and parameterized? as I am not used to 'technical' words. I have learnt most of from net and based on trial and error. :))) Thank You Mayank Jani

Amit Gupta · Answer

In addition to what @nitin sir said, AM/PM are only for display purpose, if you have datetime datatype, you can display it in various format to what user want to view. You can google for parameterized query like Use parameterized query in msaccess using c# You will get plenty of resources. Hope we had clarified enough. Good Luck

Amit Gupta · Answer

Hey It was a good practice to use datetime fields as datetime and not as text, if you are doing so then you are loosing many datatype structures and functionality like sorting and arithmetic operations. You should consider the below points 1.change datatype to datetime instead of text 2. Avoid inserting DateTime.Now.ToShortDate(), use DateTime.Now 3. Avoid building query using string concatination instead use parameterized query.