Solved

Getting a TIME from MS Access 2010 Date Picker

Posted on 2012-03-14
10
2,972 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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 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 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 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

770 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