Solved

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

Posted on 2015-01-24
4
1,299 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
[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
  • 2
4 Comments
 
LVL 80

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
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 viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
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.

726 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