Filtering out part of a field

[CherryinHove]

Hi,

Apologies, I have no idea if this should go in reports, queries, macros or modules and VBA as I'm a bit stuck but hopefully it's fairly simple.

I've been teaching myself access and it's been going well. What I have now been asked to do is produce a report that generates the shift patterns for everyone in the office as an HTML document. Now, the data is all exported from another program and I've had no problem getting the data into access easily. The problem I have is the format some of the data is in.

The major one that I need to solve is showing what time people are meant to take their lunch break each day. The field for break is filled in in the following format.

07/04/2008 12:45:00

Now, I have the date from elsewhere so I really don't want the date to show up so I need something that removes the date from this field. Is this going to be easy to do? (Ideally I'd like it so that the above example actually just returned 12:45 but if it has the 00 on the end that would not be the end of the world)

Apologies if this is in the wrong part of your forum.

 

[namliam]

Just import this field as a date/time field and put the correct formatting on your control(s) on your table/form/report/query as desired.

Taking away the date would be " easy to do " but I am allways reluctant to throw away data that is offered from the source system.

 

[CherryinHove]

Fantastic! That is so so so simple. I'd been looking at vbscripts for rearranging the date format and it was making my head hurt. This has solved everything in about 30 seconds.

Thank you ever so much. Reputation added

 

[raskew]

Hi -

The TimeValue() function will return just the time portion of a date/time field or string. Example:

? timevalue(#07/04/2008 12:45:00#)
12:45:00 PM

HTH - Bob