Week Number selection

[Peter Bellamy]

I want to offer users a popup that enables them to select a a week number.
The number is then being used to create a product serial number, for a range of products.
I have a built a function that generates the Serial number and would like to get the week number when it is running.

Can I use the Access Control do this?
Open the Calendar, get a date, convert it to a week number and then close the Calendar?


Cheers

 

[jdraw]

I want to offer users a popup that enables them to select a a week number.
The number is then being used to create a product serial number, for a range of products.
I have a built a function that generates the Serial number and would like to get the week number when it is running.

Can I use the Access Control do this?
Open the Calendar, get a date, convert it to a week number and then close the Calendar?


Cheers

Sounds reasonable. Have you tried it? If you're working off today's Date you wouldn't need the Calendar control.

 

[Peter Bellamy]

It was how I could open just one instance of the calendar that had stumped me.
However I have found a solution at this site
http://www.fontstuff.com/access/acctut09.htm

A site which has given me a lot of very useful solutions, particularly with SQL !!!!

Cheers

 

[ChrisO]

Week number is not clearly defined and on or after the Sunday 27th December 2009 is a good date to test.

If Sunday is the first day of the week, and it isn’t regarded as such in all places, then Sunday 27th December 2009 is regarded as being in week 1. That should be the default in Access but it isn’t. Access would calculate it to be week 53 because of a bug, http://support.microsoft.com/kb/200299 , and it doesn’t appear Microsoft will fix it.

The fix is simple: -

[color=green]' datIndex equals any date
' Do the WEEK timeline.[/color]
lngWeek = DatePart("ww", datIndex, vbSunday, vbFirstJan1)
[color=green]' Watch out for Microsoft bug.[/color]
If lngWeek = 53 Then lngWeek = 1

But if your business rules state that it should be week 53 and not week 1 then don’t apply the correction.

 

[gemma-the-husky]

week numbers are often industry specific - the format function in access will return a week no, but this changes depending how you define week 1.

if you can use the inbuilt function then its easy to generate a drop down of dates/weeknos - but if your calendar is different from any of the access defaults, then
you are probably best define both the week numbers and week start dates in a table of your own.

 

[Peter Bellamy]

Thanks for your notes on week number problems, we use Sunday as the start of the week so ChrisO your fix will be ideal.

I have hit a snag though in my code.
The function I have created is the preperation of the next sequence of product serial numbers.
It manipulates the last used serial number to separate the serial number from its date part.
It then creates a new year number and then opens up a form with the Access Calendar on it so the week can be chosen.
On closing the Calendar form the week is converted to a week number assigned to a Global variable and then available for the creation of the batch of the next serial numbers.

My problem is that allthough the Calendar form works fine on it own, click on a date and it is selected and put into an unbound field. When it is opened from the function it does not work at all !!

I am using this to open the Calendar:

"DoCmd.OpenForm "FrmCalendar", acNormal
Forms!FrmCalendar.ocxCalendar.SetFocus"

Any suggestions please?

 

[gemma-the-husky]

what comes next after your code

the thing is that if there is more code after this, it will continue to run.

If you want to wait for the calendar to close, you either need to

a) open the calendar form, acDialog - not acNormal OR
b) almost the same thing - test for the calendar form to close, before continuing.

 

[Peter Bellamy]

Adding the acDialog did it, thanks !
"DoCmd.OpenForm "FrmCalendar", acNormal, , , , acDialog"

I have also added the Week No fix, which also is fine, thanks

However, weeks 1 thru to 9 don't have their leading zero, is there a format for that or do I have to paste it on in code?

Cheers

 

[gemma-the-husky]

format(somenumber, "00"), will do this