Solved

Getting a TIME from MS Access 2010 Date Picker

Posted on 2012-03-14
10
2,899 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

911 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

23 Experts available now in Live!

Get 1:1 Help Now