Solved

Getting a TIME from MS Access 2010 Date Picker

Posted on 2012-03-14
10
3,076 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

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…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

820 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