Access 2010 Date and time Macro

[Mechanical Pencil]

I have data that is imported/dumped into my Access 2010 data base using the “dd mmm yyyy hh:nn” format. 95% of the data imported comes in with the hh:nn as 00:00 which is what I need. However, the remaining 5% comes in with random times, 09:54 or 22:11. Those random times causes problems down the line with my tables and charts. I’ve been editing that 5% manually for a long time. It’s time consuming and labor intensive. I’ve been trying to figure out a macro for just as long but cannot.

I’m not good with macros and looking for some ideas. I know date and times are taboo so I’m not sure if this is possible.

Thank you in advance, MP

[pjwhitfield]

DO you need a macro? If theyre in a table then you really should just use an UPDATE statement to change them.

What is the format of the field they are stored in?

[Mechanical Pencil]

That is much easier to use. Thanks. I was able to do just that except I can't figure out the correct date and time format. dd mmm yyyy hh:nn is the format and I just need to change the times and leave the dates alone.

I was able to update the times to 00:00 but it also changed the dates of 71K records to all the same. Opps. Good thing it was a copy. Make another and start over.

[pjwhitfield]

What is the field type though? Is it a date format? a Long? or text?

If its text then update all the records with LEFT([your fieldname],12) & "00:00"
If its a Long value (as dates should be stored) then I believe that rounding the value DOWN to 0 places will also set the time to 00:00

[Mechanical Pencil]

Right, Date and time.