Solved

VBA:  How to extract short date string out of dtpicker control

Posted on 2015-01-24
4
940 Views
Last Modified: 2015-01-24
I've got a date picker control on a userform in my Word 2010 VBA macro.

I simply want to extract value of the user chosen date as a string in short date format, e.g. "1/24/2015" so I can stick it into a document.  The date picker is showing a valid date (say, today).

If I use Me.MyDatePicker.value, I always get 12:00:00 AM

If I try to first assign the value to a date variable, I get some bizarre date in 1941.

dim mydate as date
mydate = Me.MyDatePicker.value
MsgBox mydate

Open in new window


What am I missing?
0
Comment
Question by:versatilebb
  • 2
4 Comments
 
LVL 79

Expert Comment

by:David Johnson, CD, MVP
ID: 40568577
me.datepicker returns a datetime not a date only. What you want is done by formatting the datepicker display using a format i.e. .format("mm/dd/yy").  keep it as a datetime if you want to use it as a date or convert it to a string.
0
 

Author Comment

by:versatilebb
ID: 40568596
David:

Thanks for your quick response.  I tried the format command and can't get it to work.  Unfortunately, I have to quit right now for the day, and it might be Monday night before I get back to this. I'll get back to you then.

Thanks
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
ID: 40568622
'Short Date' format is a setting at OS Level. In Windows it can be set via the 'Control Panel' with the 'Region and Language' dialogue .

To convert a date to a string in a particular format regardless of the Windows settings you can use the Format method. From your example of "1/24/2015" you would need:
MsgBox Format(mydate, "M/d/yyyy")

Open in new window

0
 

Author Closing Comment

by:versatilebb
ID: 40568885
Thanks, Graham.   That was all I needed.

Another key to making this work, that I learned once before only after much searching, and just now remembered:  If you have date pickers on a MultiPage in your dialog box, you have to activate the page where the date picker is located in order to set the date, change the date, or even read the date value.  I'm using 4 date pickers so I had to activate each page in turn (e.g. Me.Multipage1.value = 2) and store the value, then use your solution to display it as desired.  I ended up using format ("MMMM d, yyyy") to fully spell it out.  Seems like a lot of work, but that's the only thing I've found that works.

Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Outlook Free & Paid Tools
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

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

15 Experts available now in Live!

Get 1:1 Help Now