Access and SQL backend time fields
I converted a Microsoft Access backend database to SQL. Within one of the tables I have two time fields. One is StartTime and the other is EndTime. I am trying to get the Access form to display the correct time format, HH:MM . In SQL I have set the datatype to Time(0). In the Access form, I have the format for the field setup up as shorttime. I have an input mask: 00:00;0;_. When I first enter data in the table, the time appears to be formatted correctly. However, after I close the record and re-open it, the time is displayed as hh:mm:ss. Is there any way, short of creating text fields for hours and minutes and then using calcs to put them back together to perform math functions, to make this work?
John,
The Format() function converts dates or numeric fields to TEXT strings so it is never appropriate to use Format() if you want a field to behave correctly. For example, if you Format() two dates -
01/01/19 and 02/01/17, the January date will sort before the February date even though it is two years later and it will compare less. If you performed the same sort and compare against the underlying unformatted date data, the February date would sort ahead of the January date and compare as less.
rod,
shorttime includes seconds. If you don't want seconds, you need to use a specific format such as hh:nn
Keep in mind that formats do not affect how data is stored. They only affects how it is displayed. So if you define a numeric field with a format having two decimal places and you perform a calculation that results in 5 decimal places, all 5 are stored but they are rounded to 2 for display. This is why I NEVER, EVER apply formats to the table itself. All formats are applied in forms, reports, and queries as necessary.
The Format() function converts dates or numeric fields to TEXT strings so it is never appropriate to use Format() if you want a field to behave correctly. For example, if you Format() two dates -
01/01/19 and 02/01/17, the January date will sort before the February date even though it is two years later and it will compare less. If you performed the same sort and compare against the underlying unformatted date data, the February date would sort ahead of the January date and compare as less.
rod,
shorttime includes seconds. If you don't want seconds, you need to use a specific format such as hh:nn
Keep in mind that formats do not affect how data is stored. They only affects how it is displayed. So if you define a numeric field with a format having two decimal places and you perform a calculation that results in 5 decimal places, all 5 are stored but they are rounded to 2 for display. This is why I NEVER, EVER apply formats to the table itself. All formats are applied in forms, reports, and queries as necessary.
ASKER
Pat,
I agree with you about formatting fields in the table.
I will create 2 string fields, hours(00-24) and minutes (00-59) for start/end times
User will enter hours then minutes. Code will convert to time and error check.
Another field will be used to hold the actual time data for calculations in reports/etc
I thought there had to be a way to collect/display only hh:mm. Seems I was wrong.
I agree with you about formatting fields in the table.
I will create 2 string fields, hours(00-24) and minutes (00-59) for start/end times
User will enter hours then minutes. Code will convert to time and error check.
Another field will be used to hold the actual time data for calculations in reports/etc
I thought there had to be a way to collect/display only hh:mm. Seems I was wrong.
Format is only for display ...if somehow the was a misconnception ....the underlying data just remain intact.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Rod,
Did you try putting a format in the format property of the control as I suggested? The input mask doesn't format an existing field. It just limits data entry. I would try the format property before resorting to unbound text controls.
Did you try putting a format in the format property of the control as I suggested? The input mask doesn't format an existing field. It just limits data entry. I would try the format property before resorting to unbound text controls.
ASKER
OK. I tried the following:
Properties/Format. When I entered the format command, I got the following:"=for"m"at(Start
If I change the control source to use the format command =Format([StartTime],"Short
Properties/Format. When I entered the format command, I got the following:"=for"m"at(Start
If I change the control source to use the format command =Format([StartTime],"Short
You don't use Format() for this purpose. I explained earlier that Format() is a function and in addition to applying the desired format, it outputs a text string which prevents the field from acting correctly in any date or numeric operation.
The format PROPERTY of a control is not the same as the Format() function. Using the format PROPERTY of the control will format the control's value but not change its data type.
I made a form to show you that my suggestion does work.
The format PROPERTY of a control is not the same as the Format() function. Using the format PROPERTY of the control will format the control's value but not change its data type.
I made a form to show you that my suggestion does work.
ASKER
There were many good suggestions. I tried all of them. I read all the articles. There was never a final solution to this issue. This is an input field, Regardless of what I tried, the field would still display seconds when entering the time. Remember, this is a short time format in a SQL table. Finally, I created a two tables. One for hours 00-23. One for minutes 00-59. Then, I added combo boxes to point to the tables. Actually, I was entering two times, a start time and finish time. I error checked the data to make sure it was a valid time. If the end time was less than the start time, I set the start time to the end time. If the start time was greater than the end time, I made the end time equal to the start time. I collect results of datediff calcs in another field.
ASKER
There were many good suggestions. I tried all of them. I read all the articles. There was never a good solution to this issue. This is an input field, Regardless of what I tried, the field would still display seconds when entering the time. Remember, this is a short time format in a SQL table. Finally, I created a two tables. One for hours 00-23. One for minutes 00-59. Then, I added combo boxes to point to the tables. Actually, I was entering two times, a start time and finish time. I error checked the data to make sure it was a valid time. If the end time was less than the start time, I set the start time to the end time. If the start time was greater than the end time, I made the end time equal to the start time.
Open in new window