Solved

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

Posted on 2015-01-24
4
808 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 78

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Preface: When I started this series, I used the term CommandBars because that is the Office Object class that it discusses. Unfortunately, when Microsoft introduced Office 2007, they replaced the standard Commandbar menus with "The Ribbon" and rem…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…
In a previous video Micro Tutorial here at Experts Exchange (http://www.experts-exchange.com/videos/1358/How-to-get-a-free-trial-of-Office-365-with-the-Office-2016-desktop-applications.html), I explained how to get a free, one-month trial of Office …

743 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

10 Experts available now in Live!

Get 1:1 Help Now