Solved Date to DateTime

[Pat Hartman]

So here we go again ...

I don't think so. Your question was how to convert a date column to hold datetime which somehow took 18 responses before you got a non-ambiguous answer that you DO NOT need to convert anything. The problem being you asked the wrong question. Now that we know what the real question is (how can I also enter time when I enter the date in the calendar), we can give a definitive solution.

There are custom calendars that include a time option. Maybe you can search and find one or someone will post a link. OR build your own. I think @Uncle Gizmo has a date sample that might be expanded.

Here's some links I found:

MS Access - Calendar/Date Picker Control | DEVelopers HUT

A few good calendar/date picker controls for your MS Access database, all of which do not use any ActiveX controls (so no versioning issues!).

www.devhut.net

Microsoft Access Calendar Date and Time Pickers to Simplify Data Entry on Access Forms

Allow users to view and select a date or time in the appropriate format, and optionally allow a drop-down calendar.

www.fmsinc.com

The first one links to several free samples. I didn't test any and not all handle time.

The second two do handle time but they are not free.

 

[MajP]

Here is an example time picker. I did not make a combine one, but this lets you add the time portion to a date (datetime) field.

I added another time picker too!

 

[MajP]

If desired I can combine this with a calendar on to one popup.

 

[AlexN]

@MajP
Wonderful job MajP. Once again you show the way.
Still...
This will do for new records. For the existing ones problem remains. So I've been awfully wrong not only for the question asked, but in initial design as well, and yeah it's "here we go again..."
It's alright, I'm learning things
Thank you so much!!!

 

[Gasman]

This will do for new records. For the existing ones problem remains

Well as you cannot possible know the time for all the previous records, the situation is moot surely?
They just remain at 0:00:00

 

[KitaYama]

Well as you cannot possible know the time for all the previous records, the situation is moot surely?
They just remain at 0:00:00

I would use an update query and add 1 second to the field : DateAdd("s",1,fieldname)

 

[MajP]

This will do for new records. For the existing ones problem remains. So I've been awfully wrong not only for the question asked, but in initial design as well, and yeah it's "here we go again..."

As long as you understand it is only a Display problem and not a data or storage problem.
Even if the field has a format applied, 12:00:00 AM or 00:00:00 as part of the date will not display. It is there, just will not display. I prove this in my example where I simply add time to the existing field using my timepicker.
As said by @kitayam if it bothers you so much you have to see it, then have the default add 1 second to the date and get something 1/1/2023 00:00:01. I tried adding a fraction of a second hoping that would force the display, but it did not.

So I do not understand the "existing problem". There is no existing problem. All you existing records are valid they just have a time of 12:00:00 AM. You could do an update query to add one second to all the records.
Now be careful because it may break a lot of things.
if SomeField = SomeDate
Now that you add a time values as part of your datetimes it is highly unlikely that will return any records. Then you have to do something like
if int(someField) = int(someDate)
or
if SomeField => someDate and somefield < someDate + 1

 

[AlexN]

You guys are all wonderful,
@Gasman , you showed the way
@KitaYama , man you gave THE idea, that saved me from days of work!
@MajP , once again you've been so helpful!

So, I added one more field to my table, used the update query suggested to display time too, kept the "only date" one to maintain relationships, used the new field for all my calculations, and that was it. All systems go now. I'm happy again .

To all people who got involved, thank you so much, you're teaching me a lot!
Case solved (once again).