Medium Time format showing seconds

[Guest]

Hello -

I have several tables that use a time field. This field is formatted as
medium time - HH:MM AM/PM

However, some of the records in my tables are showing seconds HH:MM:SS and
this is causing issues with my queries and filters.

I can go into the tables but I have to click into each cell to see if the
seconds are there or not so I can remove them.

Is it possible to run an update query that removes the seconds? I have tried
several approaches with no luck.

Many thanks
sandy

 

[John Spencer]

You could try the following expression to clear seconds (set the seconds to
zero).

TimeValue(Format([TimeField],"hh:nn"))

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Michel Walsh]

That should not be a problem. Take, as example, a floating point number,
like 2.3, that would be formatted as 2, to display only the integer part.
The FIELD, itself, would still hold the decimal part, even if the FORMATTED
displayed 'thing' does not.


Anyhow, you can subtract the second:

DateAdd("s", - DatePart("s", yourDateTimeField ) , yourDateTimeField)


and if the floating point number precision allows it, you won't have any
second left.

(Don't miss the tiny - in front of the second argument of DateAdd )



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thanks for your quick response.

To apply this fix, would I be modifying the data using by putting this in
the UPDATE TO for the time field of an update query?

UPDATE t_Courses
SET t_Courses.Start_Time = TimeValue(Format([TimeField],"Short Time"));

thanks
sandy

 

[Michel Walsh]

You can update once for all, but that 'destroy' the original information
forever.

(Make a backup, just in case something would go wrong)


UPDATE t_Courses
SET Start_Time = DateAdd( "s", - DatePart("s", Start_Time ), Start_Time )


Alternatively, you can format(date_time_value, "hh:mm" ) each time you need
that specific format, keeping the original second presents in the field.



Hoping it may help,
Vanderghast, Access MVP

Thanks for your quick response.

To apply this fix, would I be modifying the data using by putting this in
the UPDATE TO for the time field of an update query?

UPDATE t_Courses
SET t_Courses.Start_Time = TimeValue(Format([TimeField],"Short Time"));

thanks
sandy

That should not be a problem. Take, as example, a floating point number,
like 2.3, that would be formatted as 2, to display only the integer
part.
The FIELD, itself, would still hold the decimal part, even if the
FORMATTED
displayed 'thing' does not.


Anyhow, you can subtract the second:

DateAdd("s", - DatePart("s", yourDateTimeField ) , yourDateTimeField)


and if the floating point number precision allows it, you won't have any
second left.

(Don't miss the tiny - in front of the second argument of DateAdd )



Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

oops, should be Format(date_time_value, "hh:nn")

since mm is reserved for month.


Vanderghast, Access MVP

 

[Guest]

Hi Michael -

I tried this -

UPDATE t_Courses SET t_Courses.Start_Time =
DateAdd("s",-DatePart("s","Start_Time"),"Start_Time");


and it is giving me a "Data type mismatch in Criteria expression" error

I have checked that there is data in the Start_Time field of every record in
the table.
The format for this field is medium time.

any ideas?

thanks
sandy

 

[Guest]

hi again -

I tried using

UPDATE t_Courses
SET t_Courses.start_time = TimeValue(Format([Start_Time],"Short Time"));

and that seemed to work fine

thanks again,
sandy

 

[John Spencer]

UPDATE t_Courses
SET t_Courses.Start_Time =
DateAdd("s",-DatePart("s",[Start_Time]),[Start_Time]);

Reference the fields not a string that is the name of a field

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================