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
490 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
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: 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
 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

631 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