1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Format function returning a strange result!

Discussion in 'Business Applications' started by Unusual, Apr 26, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. Unusual

    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
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    What is your system default for the date separator?
     
  3. Unusual

    Unusual Thread Starter

    Joined:
    Jan 22, 2009
    Messages:
    92
    the short date format is yyyy-mm-dd
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    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.
     
  5. Unusual

    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.
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    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?
     
  7. Unusual

    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?
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    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.
     
  9. Unusual

    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.
     

    Attached Files:

  10. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    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
     
  11. Unusual

    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
     
  12. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Don't mention it, If I come accross the reason I'll let you know
     
  13. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/919298

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice