Solved

Getting a TIME from MS Access 2010 Date Picker

Posted on 2012-03-14
10
2,830 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 119

Expert Comment

by:Rey Obrero
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
 

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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 39

Accepted Solution

by:
als315 earned 500 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 119

Expert Comment

by:Rey Obrero
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 84
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now