How can I install Weeknum function in access 2000?

[Guest]

I need this function to work but I did not find it in the list of the
functions. where can I get it? it is not in the addins.
Thanks
Nawel

 

[Van T. Dinh]

Check Access VB Help on the DatePart() function.

Make sure you read the different options in "Settings" section.

 

[Guest]

Are you referring to the custom function produced to overcome the bug
associated with using the DatePart function to obtain the week number when
using the 'first four days' option for week 1? If so the function is as
follows. Just paste it into a standard module and call it like so by passing
a date expression into it:

=WeekNumber(#07/04/2005#)

''''function starts''''
Function WeekNumber(InDate As Date) As Integer

Dim DayNo As Integer
Dim StartDays As Integer
Dim StopDays As Integer
Dim StartDay As Integer
Dim StopDay As Integer
Dim VNumber As Integer
Dim ThurFlag As Boolean

DayNo = Days(InDate)
StartDay = Weekday(DateSerial(Year(InDate), 1, 1)) - 1
StopDay = Weekday(DateSerial(Year(InDate), 12, 31)) - 1
' Number of days belonging to first calendar week
StartDays = 7 - (StartDay - 1)
' Number of days belonging to last calendar week
StopDays = 7 - (StopDay - 1)
' Test to see if the year will have 53 weeks or not
If StartDay = 4 Or StopDay = 4 Then ThurFlag = True Else ThurFlag =
False
VNumber = (DayNo - StartDays - 4) / 7
' If first week has 4 or more days, it will be calendar week 1
' If first week has less than 4 days, it will belong to last year's
' last calendar week
If StartDays >= 4 Then
WeekNumber = Fix(VNumber) + 2
Else
WeekNumber = Fix(VNumber) + 1
End If
' Handle years whose last days will belong to coming year's first
' calendar week
If WeekNumber > 52 And ThurFlag = False Then WeekNumber = 1
' Handle years whose first days will belong to the last year's
' last calendar week
If WeekNumber = 0 Then
WeekNumber = WeekNumber(DateSerial(Year(InDate) - 1, 12, 31))
End If

End Function

Function Days(DayNo As Date) As Integer

Days = DayNo - DateSerial(Year(DayNo), 1, 0)

End Function


Function LastWeek(intYear As Integer)

Dim intLastWeek As Integer
Dim dtmDate As Date

dtmDate = DateSerial(intYear, 12, 31)

Do Until intLastWeek > 1
intLastWeek = WeekNumber(dtmDate)
dtmDate = dtmDate - 1
Loop

LastWeek = intLastWeek

End Function
'''function ends''''

 

[Guest]

Thanks ken for your answer but it is not what I need, I just need the
weweknum function to display the week number of a certain dates, it is
existing in Excell but I need to install in Access as well.
Nawel

 

[Guest]

Thanks for your answer but it is not what I need , this weeknum function
display the week number of a date, It is existing in Excell but I want to use
it in access as well, I just need to install it then I can use it, the
problem I did not find it in the addins.
Nawel

 

[Douglas J Steele]

Why do you need to use the Excel weeknum function? What's wrong with using
the equivalent function that exists in Access?

The DatePart function, using an interval of "ww", should give you exactly
the same as the Excel Weeknum function.

In other words, DatePart("ww", MyDate) should give you the same as
Weeknum(MyDate)

In fact, from what I can see, the Access function is far more flexible than
the Excel one, since you can specify any day of the week as the first day of
the week (Excel seems limited to Sunday or Monday starts), plus you can
specify how you want to define the first week of the year.

If for some reason you need to keep the function name, create your own
function that wraps the Access one:

Function Weeknum(MyDate As Date) As Integer

Weeknum = DatePart("ww", MyDate)

End Function

 

[chakatheapeman]

Is This what you need

Don't make us guess!


Public Function intStartOfWeek(txtWeekNo As Integer, txtYear As Integer) As Date
Dim TempDate As Date
TempDate = CDate("01/01/" & txtYear) - weekDay("01/01/" & txtYear, vbMonday) + 1
TempDate = TempDate + 7 * (txtWeekNo - 1)
intStartOfWeek = DatePart("m", TempDate) & "/" & DatePart("d", TempDate) & "/" & DatePart("yyyy", TempDate)
End Function

 

[Douglas J Steele]

That would appear to be the opposite of what Nawel is looking for.

That gives you a date, given a year and a week number. I believe Nawel is
looking for a weeknumber, given a date.

And while admittedly a picky point, I'd recommend using

TempDate = DateSerial(txtYear, 1, 1) - weekDay(DateSerial(txtYear, 1, 1),
vbMonday) + 1

instead of

TempDate = CDate("01/01/" & txtYear) - weekDay("01/01/" & txtYear, vbMonday)
+ 1

(it's picky in this case, because 01/01/yyyy will always be interpretted
correctly, regardless of how the Regional Settings are set. For other dates,
such as 02/10/yyyy, that isn't the case)

 

[Guest]

I'm using a table imported from excell into a database, this excell file is
changing everytime because updated from an other software, the problem is in
access I need this table to give me the count of each field , I need this
calculation to be used in an other table, it is possible to use it one if the
table have a relationship but in my import macro I first have to delete this
table to have the updated one, how can I do to use these fields ????
Thanks for answers

 

[John Vinson]

I'm using a table imported from excell into a database, this excell file is
changing everytime because updated from an other software, the problem is in
access I need this table to give me the count of each field , I need this
calculation to be used in an other table, it is possible to use it one if the
table have a relationship but in my import macro I first have to delete this
table to have the updated one, how can I do to use these fields ????
Thanks for answers

I would suggest using File... Get External Data... Link to *link* to
the spreadsheet, rather than importing it. This will leave the data
stored in Excel, and reflect any updates to the data; you can create
your queries, do counts, etc. on the linked table just as if it were a
local Access table.

If you want to use an imported table, I'd suggest that you have a
table in your Access database; rather than creating a new table each
time, run a Delete query

DELETE * FROM tablename;

and import *into* the table. You can run the Delete query and the
import from VBA code or from a Macro, so you only need to click one
button to do both. Be sure you compact the database frequently in any
case!

John W. Vinson[MVP]

 

[Guest]

Hello John,
Thanks a lot for your answer, I want to know if it is possible to create a
relationship between the link table and an other table to make the
calculations on this one .
Thanks
Nawel

 

[Guest]

Hello,
I'm creating a query whish count the number of no blank fields for a certain
field using an expression but every time I get this error message ''Cannot
Group on fields selected with' *''''??? can someone tell how to solve it, I'm
getting crasy !!!!!

 

[Douglas J. Steele]

Relationships cannot be created between tables in two different databases.

 

[Douglas J. Steele]

What's the SQL of the query that's failing?

 

[Guest]

Hi Douglas,
I'm in the same database , I just have a linked table and I need to have the
number of records and other info for this table to use it in an other for
calculation .

 

[Guest]

Expr1: Count([K110 PO1 Received from KPN Actual End]) , this K110 is taken
from a linked table

 

[Douglas J. Steele]

Ok, so you've got a front-end database, with a linked table pointing to a
table in a back-end database. Your table doesn't really exist in your
front-end database: what you've got is a pointer to the actual table in the
back-end. You cannot use a relationship with the linked table in the
front-end database. The relationship would need to be created in the
back-end database.

However, a relationship won't give you "the number of records and other info
for this table to use it in an other for calculation"

 

[Douglas J. Steele]

No, I want to see the entire SQL statment, not simply one field.

With your query open, select SQL from the View menu. Copy and paste the
entire SQL here.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Expr1: Count([K110 PO1 Received from KPN Actual End]) , this K110 is taken
from a linked table

What's the SQL of the query that's failing?

 

[John Vinson]

Hello John,
Thanks a lot for your answer, I want to know if it is possible to create a
relationship between the link table and an other table to make the
calculations on this one .
Thanks
Nawel

You cannot create a relationship with relational integrity enforced,
as Douglas and Sunil correctly state.

However you *can* create a Query joining the two tables, for the
purpose of doing calculations. You may need to manually drag the
linking fields together; since there's no defined relationship Access
can't automatically fill in the join lines, but you can do so
yourself.

The query will almost surely not be updateable and may be slow -
you'll have to weigh the advantages and disadvantages of the imported
vs. linked table approaches.

John W. Vinson[MVP]

 

[Guest]

Hello John,
Thanks for your answers, I create a table on my database with a macro which
will make the delete records+import excell workbook.It is working thanks to
you and Douglas, What is the easy way to use the record count from this table
in an other table ???
Thanks a lot