Week Numbers that start at 1st April

[Steve_T]

Week Numbers that start at 1st April (HELP!!!!!)

Hello all,
Very new to Code so your help is much needed.

I need a code to show the Week number in a TextBox called 'Week No' when the form is opened/loaded to make an entry.
However i know this can normally be done using =Format(Date(),"ww") but i need Week 1 to start from April 1st instead of Jan 1st. I have seen threads already posted like what i am asking but i could also do with know where to store it.
My form is called "Main form" which is based on a Table called "Main table".

Thanks

 

[ajetrumpet]

However i know this can normally be done using =Format(Date(),"ww") but i need Week 1 to start from April 1st instead of Jan 1st. I have seen threads already posted like what i am asking but i could also do with know where to store it.

How about using the calendar year break to your advantage:

IIF(date < "4/1/" & Format(date, "yyyy"), _
   52 - datediff("ww", date, "4/1/" & Format(date, "yyyy")), _
      datediff("ww", date, "4/1/" & Format(date, "yyyy")))

Or, maybe you should use 53 in place 52?? I think that might be what you want. Either way though, it should work...

 

[Steve_T]

Thanks for reply,
Where would i place this code? Assign to function on week number teaxtbox or on funtion for form? etc

 

[ajetrumpet]

Where would i place this code?

Well, I believe a portion of your first post answers this question:

I need a code to show the Week number in a TextBox called 'Week No' when the form is opened/loaded to make an entry.

How about the Load event?

Or, maybe the default value property of the textbox? There are different choices available for you here...

 

[Steve_T]

The reason i ask is that no matter where i put it the code an error box pop's up stating "The expression you entered has an invalid . (dot) or ! operator".

 

[ajetrumpet]

The reason i ask is that no matter where i put it the code an error box pop's up stating "The expression you entered has an invalid . (dot) or ! operator".

Are you sure that error message is referring to this expression? How much other code do you have running at the same time? Or on the same event? Those operators are not in this statement, and I don't see any connection with the error message. Check on your other code.

When does it give you the message? Always at the same time?

 

[Steve_T]

No other code running, i created a new Database and created a box called "Week Number" then entered the text in the default value box. as soon as i moved the curser away from the field the error message came up. I also put the message in the "Open Form" VBA but nohing happened.

 

[ajetrumpet]

Post a screenshot of the default value attempt and the VB module attempt.

>>edit<<

I just tried it. Works fine in the load event of the form, but not for the default value. What version of Access do you have?

 

[Steve_T]

As requested, Thanks

 

[Steve_T]

Access 2000

 

[ajetrumpet]

Steve,

Regarding the VBA attempt; you have to put this:

me.TextBoxname =

...Before the IIF statement!

Regarding the Default Value attempt; your entering the infromation into the table. You need to enter it into the property sheet of the TextBox control ON THE FORM. The process has nothing to do with a table.

 

[Steve_T]

I told you i has new to Code (ha ha). Thanks alot mate, it works ok now