Solved

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

Posted on 2015-01-24
4
1,488 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:Bryce Bassett
[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 81

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:Bryce Bassett
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:Bryce Bassett
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
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