Weeknum In Access

[paul.clarke]

I Have a excel spreadsheet that uses weeknum formula, this works fine (in excel), but i have created a form which when you click on a command button, it opens this excel report, but for some reason weeknum does not work, only get the #name? value, I know you cannot use weeknum in an access query, so i tried using format date,"ww" on the excel report, but then excel does not recognise this function, How can i resolve this, as i need weeknumber to work as the entire report forecasts based on week numbers.

Thanks in advance

 

[GUIDO22]

Hi - any chance you can post a copy here for us to view?

 

[gemma-the-husky]

weeknum in an access query,

access does have a formula to evaluate week numbers, but its called format

(its just one of the several uses of format)

just format("ww",mydate)

will turn a date into a week number with several options to determine which week is week1 - its probably the same as your excel formula

 

[paul.clarke]

I know access uses "format "ww" ,mydate. my problem is i am running an existing excel report from a form i created so when you click the command button the report loads in excel, but for some reason the existing weeknum formula is not recognised, but it is when i open the report in excel itself, and if i change the formula to format"ww" in excel and save it, excel does not recognise it, so appears im stuck either way. any thoughts??

 

[missinglinq]

There is a "Microsoft Excel XX.X Objects Library" available in Access References, where XX.X depends on the version you're running. It's supposed to make Excel functions available in Access. From the code window of any form, goto Tools - References and scroll down then add it to your references.

 

[paul.clarke]

Ive found microsoft excel 11 reference, i clicked the tick box, but still does not work, do i have to manuaally add code or anything else?

 

[paul.clarke]

Try this for yourselves.
1. create an excel sheet with just the weeknumber formula in it, then save it,
2. create a form in access and add a command button to open the above report
3. does the weeknum display when the excel sheet is opened from your form button >, or is it like mine and just displays #name? in the cell.?

this is the problem im having, i have tried to explain it as simply as i can