Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2009
    Posts
    13

    Unanswered: wrong week number in date format

    Hello,
    I have a problem with existing database within the form where I have date {default value is 'Date()' } which is formatted as week ('ww') showing week number.

    It was ok for the previous year but now it reads date Jan, 4th as week No 2 while I need it to be No 1.

    I understand I can input into VBA immediate window:
    "? format(date,"ww",,vbFirstFullWeek)" which returns correct number 1 but I cannot accomplish to have Date() shown correctly as WW. (still reads 2)

    Is there a way to fix this in form / format property only OR some hard coding is necessary?
    Please provide detailed description in case of the latter !
    TIA

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so set the controls value to be =date(mydatevalue,"ww",,vbFirstFullWeek)
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    per a post at our sister-site (xtremevbtalk.com,)
    Quote Originally Posted by Flyguy
    The datepart and the format functions have a bug which has never been fixed since VB5, same for VBA
    He supplies code for a working WeekNumber function in the above-linked post.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  4. #4
    Join Date
    Jun 2009
    Posts
    13
    I have perfect solution from Michiel:

    >On Jan 5, 4:23 pm, Michiel wrote: I suppose that when you use
    > =Format(Date();"ww";0;3)
    > as ControlSource for the field, you'll be a happy man.
    > You can leave the 'Format' and 'DefaultValue' of the field empty.
    > hope this helps
    > Michiel

    However, I believe that you're right Loquin
    Now week No for my past dates is wrong (?)

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can use the ISO_WeekNumber function in the attached code to solve this problem. It returns the week number according to ISO standards.
    Attached Files Attached Files
    Have a nice day!

  6. #6
    Join Date
    Jun 2009
    Posts
    13
    Thankx very much Sinndho. Seems that everything works fine now!

  7. #7
    Join Date
    Jul 2010
    Posts
    2
    i was trying to use your code but i got compilation error :

    http://img1.vpx.pl/up/20100702/20100702_14162990.jpg

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This is probably because you copied and pasted the contents of the file into your application. If it's the case, simply comment or remove the offending line.

    When you want to load a .bas file in an application such as Access, the usual method consists in loading it from the VBA Editor (Menu: File --> Import File).
    Have a nice day!

  9. #9
    Join Date
    Jul 2010
    Posts
    2
    you have right, now it works
    thank you !

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •