Solved

Using the DTPicker control and need the date stored in a mm/dd/yyyy format but nothing I do seems to work.

Posted on 2006-06-29
16
445 Views
Last Modified: 2012-05-05
Can you tell me how to force the DTPicker to store the date in mm/yy/yyyy format?  Changing it to Short Date seems to still put the time in the db field.
Thanks
Kevin
0
Comment
Question by:kevin1000
16 Comments
 
LVL 26

Expert Comment

by:dannywareham
ID: 17012525
JET uses American dates, where as Access uses your local settings.
You should format it in code like so:

Format(Me.Datepicker,"mm/yy/yyyy")

An odd format, but it will work

0
 

Author Comment

by:kevin1000
ID: 17012539
Thanks for the quick reply.

Where would I put that code? In the field properties or in the OnUpdate?

0
 
LVL 5

Expert Comment

by:BPeb
ID: 17012654
If the question you're asking is can you prevent Access from storing the time in a date field then the answer is no.  But using the Format command (as suggested) when you display or export the data can hide the time portion of the field.  
0
 
LVL 54

Expert Comment

by:nico5038
ID: 17012685
Guess you need the mm/dd/yyyy format.
Just keep in mind that the date isn't stored in a particular format. Access uses a relative daynumber and when you change a datetime field to a double precision numeric field the value will be revealed.
The format is used when you want to display a date and when you use no formatting at all the Regional settings (Found on Start/Settings/Control panel) will be used.
In a query however you need to use the US (mm/dd/yyyy) format to get the correct value stored.

Need more information ?

Nic;o)
0
 

Author Comment

by:kevin1000
ID: 17012730
You're great!  Microsoft????

All I wanted to do was store a date in the field w/o the time using some type of calendar control so that I can pull it back in a report by date.  From what you've said the date is going to be stored with the time no matter what I do.  Well, can I then do something on the report query side to see if the field "contains" a specific date?

Thanks
Kevin
0
 
LVL 54

Expert Comment

by:nico5038
ID: 17013132
The date can be stored without the time by dropping it from the format in the table.
Just select the field and take e.g. the "Short date" format.
(When you use such a field on a form you can set the form field's format also to this format, but when it has been formatted in the table before defining the field on the form Access will "move" this format to the form automatically.)
This format will force Access to show just the date and not the time.
The time is stored in the digits behind the decimal dot and for more info on this you can check:
http://www.mvps.org/access/datetime/index.html and especially the part "Calculate differences in time"

Nic;o)
0
 

Author Comment

by:kevin1000
ID: 17013319
Thanks.

Details:

Table Info:
Field Name:  PN_Date
Type:  Date/Time
Format: Short Date

Form Info:
Calendar Control Properties:
Format: 3: dtpCustom
Custom Format: MM/dd/yyyy

Report Info:
Starts wth a question:  What Date?
Enter: 06/13/2006

Results:  Nothing

Viewing the table shows the field contains: 6/13/2006 2:12:02 PM

Is the Calendar Control not able to put the date in w/o the time even tho I've told it to?

Thanks
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 54

Expert Comment

by:nico5038
ID: 17013485
You obviously changed the format after filling the table with the date/time.
Now first run a query like:

UPDATE tblYours SET tblYours.PN_Date = Format([PN_Date],"yyyy/mm/dd");

(change tblYours in your tablename first)
to get rid of the already filled time.

Nic;o)
0
 

Author Comment

by:kevin1000
ID: 17013535
Thanks again.
Even if I use the form (called Progress Notes) that has the calendar control that is bound to the PN_Date field to add a new record (a new progress note) it puts the date in the format 6/13/2006 2:12:02 PM.

Maybe it's not possible?  Just want the Calendar Control to put just the date in the PN_Date field and with the field definitions as listed above it should be doing it.

Any other thoughts?
Thanks
Kevin

0
 
LVL 54

Accepted Solution

by:
nico5038 earned 125 total points
ID: 17013575
I never use the Datepicker, but the full Access date form from http://www.mvps.org/access/forms/frm0050.htm
Using the DateTime picker forces you to make sure that the ActiveX control is available on every other computer you want to run your database, otherwise you'll get a "MISSING" reference and a dumping database :-(

The download can cause trouble, just open the .mdb with SHIFT pressed to see the form, or download my downgraded version: http://www.geocities.com/nico5038/MiniCalendar2000.zip

Nic;o)
0
 

Author Comment

by:kevin1000
ID: 17020279
No other thoughts out there?
0
 
LVL 54

Expert Comment

by:nico5038
ID: 17020816
Open the DTPicker's properties and select under the Other tab for the Format property the value 3
Next type behind the CustomFormat property: Short date

This with a DTPicker bound to my ShortDate formatted tablefield gave just the date and no time.

Nic;o)
0
 

Author Comment

by:kevin1000
ID: 17054461
Nic,
Thanks for the response.

When I do that, the value shows up as "S 5 orP 6 aPe".

What the heck is that?

Thanks
Kevin
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 17067071
kevin1000,

I have Access 2000 and 2003 and I don't have a "DTPicker"

From what I gather, this is a VB (Not Access VBA) control you are trying to use.

The name of you control illuminates you problem:

The "T" in DTPicker, stands for "Time".
Seems like this control will allways spit out a Time component with the date.

I highly recommed that you use  nico5038's suggestion:
http://www.geocities.com/nico5038/MiniCalendar2000.zip

nico5038's is set up to deal with dates only.

Just drop it into you app, and you are done!


Keep it simple buddy!
:)
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

867 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

16 Experts available now in Live!

Get 1:1 Help Now