Solved: Format function returning a strange result!

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Unusual

Thread Starter
Joined
Jan 22, 2009
Messages
92
Hey guys

Have a bit of a strange thing going on here...

I am using the following code in a UserForm in Word:

ActiveDocument.Bookmarks("DOB1").Range.InsertAfter Format(Birth, "d/mm/yyyy") ("Birth" is simply a Date)

However the date is being written to Word in this format: d-mm-yyyy. In other words instead of using "/" as I specified in the Format function, it uses "-".

Any ideas why it doesn't like "/"??

Thnx a mil
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
well, then your date separator is a - instead of a /
What happens if you force the userform field to your desired forma"t?

The thing is that you may type what you want but Windows generally tranfsorms it to wtht Windows expects it should be by default unless you explicitly give it the desired format

Userform.Date = "4/26/2010"

Format(Userform.Date,"d-mm-yyyy")
I'm not that good at these things but thsi I encounter many times, especially since I sometime have to code something for diffent country settings.
 

Unusual

Thread Starter
Joined
Jan 22, 2009
Messages
92
Thanks Keebellah

I don't quite understand why that would be the case though. I thought that the whole point of the Format function is specifically to force the type of format that you choose. Also if I use Format (X, yyyy mm dd) then that works fine even though it is also not the standard default format.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
It is strange, I run into formatting issues and don't get I expect.
I also run into strange formatting conflicts with Busienss Objects
Does it make a difference if the D or M are in uppercase?
 

Unusual

Thread Starter
Joined
Jan 22, 2009
Messages
92
I tried to run using capitals instead of lowercase...but same thing.

Another point is that I seem to remember this working fine previously using Office 2003 and Vista. Now I am using Office 2007 and Win 7....I wonder if that has something to do with it?
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
Could be, can you attach a sample doc with xome ficticious data?
I have Windows 7 and Office 2007 running at home in English, maybe I can reproduce it.
 

Unusual

Thread Starter
Joined
Jan 22, 2009
Messages
92
Ok thanks. Here is an example of what I mean. If you run the trial macro then a Userform is displayed. CLicking the ok will then write the selected date into the word document. The formatting doesn't work though.
 

Attachments

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
I tried it here and came up with the same problem, even the date field does not allow / insted of the -

I changed the code (see below) and now it displays 26/04/2010

Private Sub CommandButton1_Click()
Parag = DTPicker1.Value
Selection.TypeText Day(Parag) & "/" & Right("00" & Month(Parag), 2) & "/" & Year(Parag)
Unload Me < added by me to close the form after selecting a date
End Sub

Try and see, I don't think it;'s a question of Windos 7 and Oddice, I thinks it is Microsoft
 

Unusual

Thread Starter
Joined
Jan 22, 2009
Messages
92
Thanks...that should do the trick. (Its a bit frustrating that Format is not working properly though!)

Thanks again 4 your help
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
Don't mention it, If I come accross the reason I'll let you know
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top