Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Getting a TIME from MS Access 2010 Date Picker

Posted on 2012-03-14
10
Medium Priority
?
3,496 Views
Last Modified: 2012-06-21
I have a front end data entry form built in Access 2010 for a SQL form. It is automatically using the date picker for two date/time fields. This is great for the user, but I'm not getting a time, which is causing trouble with our website.

I can't find anywhere to force it to put a time with the date. I want to set it so it is whatever chosen date with a time of 11:59:59 PM. Instead, the website reads the no time as 12:00:00 AM, which is a whole day earlier than what people expect when they see the expiration date.

any ideas?

thanks!
0
Comment
Question by:awhonn
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37720385
The Access date picker cannot easily be configured to get Times.

You can use an external control to do this , or build your own.

You can also forgo the date picker and just set a default value for that field to: Now()
...and format this value to display the date and the time.

JeffCoachman
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37720403
you can add the time with this

me.txtDate=me.txtDate + #11:59:59 PM#
0
 

Author Comment

by:awhonn
ID: 37720404
Well at least I know it isn't something I overlooked. We're most often using it to set future dates, so I can't use the Now() option. I've thought about just having a job run to add times to the table, but that seems more involved than it should be.

Do you have any examples of external controls that would work for this?

thanks.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:awhonn
ID: 37720450
Capricorn -

Where do I put that? Sorry,  I don't build a lot of forms.

thanks
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37720458
You can use something like this to select times...
Access-Select-Date-And-Time-Pick.mdb
0
 
LVL 40

Accepted Solution

by:
als315 earned 1500 total points
ID: 37720459
Datepicker is only Datepicker. You can try to change format of your field to:
yyyy/mm/dd\ hh:mm:ssAMPM
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37720470
or assign the value #11:59:59 PM# to a global variable
place this in a regular module
Public Const fxTime As Date = #11:59:59 PM#


in the after update event of the txtDate ( set the format of txtDate to General date)

Private Sub txtDate_AfterUpdate()
Me.txtDate = Me.txtDate + fxTime
End Sub
0
 
LVL 85
ID: 37721931
I'd be leery of adding 11 hours, 59 minutes and 59 seconds to an existing date, since doing so might end up adding that amount of TIME to the existing date - so a value with existing Hours associated could end up putting you into the next Day.

You might be better off doing this:

Me.txtDate = DateSerial(Year(YourDateValue), Month(YourDateValue), Day(YourDateValue)) + TimeSerial(11,59,59)
0
 

Author Closing Comment

by:awhonn
ID: 37747179
It didn't solve the default time issue, but it's easier to manage than the other options.
0
 

Author Comment

by:awhonn
ID: 37747181
There are a lot of good suggestions here, but in the end I just worked with the formatting of the field. Thanks for the help!
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question