Dllokup

[Deltic]

I have a table called weeknumber in which i have 3 fields. Field one
is weeknumber and contains a list of numbers 1 - 52, field 2 is called
fromdate and contains a date to define the start of a week, field 3 is
called todate and contains the date that defines the end of the week.
What i am trying to do is display the week munber in a form but am not
sure of the correct Dlookup string i need and where to place it within
the textbox criteria.
can anyone help?

 

[Douglas J. Steele]

Assuming you're looking for today's week number, set the text box's
ControlSource to:

=DLookup("[weeknumber]", "[weeknumber]", "[fromdate] <= Date() And [todate]

= Date()")

Note that there's built-in functionality in Access to calculate week number.
Instead of having the table, you could simply set the ControlSource to

=Format(Date, "ww")

or

=DatePart("ww", Date)

(Check the help file for the optional parameters to use for variations on
how to calculate week number)

 

[BruceM]

Note too that you can use any date in place of Date in the functions as
Douglas has written them. For instance:

=DatePart("ww",[YourDateField])

If all you want is the week number for a given date there is no need for a
lookup table and DLookup.

The DatePart function can be used to specify details about the date. For
instance, if by you reckoning the week begins on Monday, and you want week
#1 to be the first full week of the year:
=DatePart("ww",[YourDateField],vbMonday,vbFirstFullWeek)

Or to have the first day of the week be the default (Sunday):
=DatePart("ww",[YourDateField], , vbFirstFullWeek)

There are more options. See VBA Help for DatePart for details.

 

[Deltic]

Note too that you can use any date in place of Date in the functions as
Douglas has written them.  For instance:

=DatePart("ww",[YourDateField])

If all you want is the week number for a given date there is no need for a
lookup table and DLookup.

The DatePart function can be used to specify details about the date.  For
instance, if by you reckoning the week begins on Monday, and you want week
#1 to be the first full week of the year:
=DatePart("ww",[YourDateField],vbMonday,vbFirstFullWeek)

Or to have the first day of the week be the default (Sunday):
=DatePart("ww",[YourDateField], , vbFirstFullWeek)

There are more options.  See VBA Help for DatePart for details.

I have a table called weeknumber in which i have 3 fields. Field one
is weeknumber and contains a list of numbers 1 - 52, field 2 is called
fromdate and contains a date to define the start of a week, field 3 is
called todate and contains the date that defines the end of the week.
What i am trying to do is display the week munber in a form but am not
sure of the correct Dlookup string i need and where to place it within
the textbox criteria.
can anyone help?- Hide quoted text -

- Show quoted text -

Thanks for your help