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
433 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 54

Expert Comment

by:nico5038
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
No other thoughts out there?
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
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
Comment Utility
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
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

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…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
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.
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…

771 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