Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2015-01-24
4
Medium Priority
?
1,998 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 83

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 2000 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

597 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