Mike said:
I have created a date table master with week-ending dates and wanted to
know
the following:
Example: I have files that are named lwy0205 with the 0205 being the
weekending date. I wanted to have (a switchboard?) prompt me for the month
and lookup the date table master, thus it knowing its week 1, week 2,
etc...Is this possible?
It's almost always (1) a violation of relational database design principles
and (2) a really bad idea, to have the name of tables be significant data.
Names are a way to identify files, but to be useful, the data must be in
fields (aka columns) in the table.
You may know what a "date table master with week-ending-dates" is, but it is
not patently obvious, nor a generally-accepted definition. There are so
many various date and time manipulation functions, either built-in, or easy
to create, that there may be some other way to accomplish your purpose. Many
of them allow you to specify the "first day of the week" (from which it is
trivial to arrive at "week ending date", and "first week of year" -- in
Access 2003, open any module window, choose Help and search on "date
functions"... the first example allowing specification of these two values
is probably "DateDiff".
Given a date, then according to the rules for what is used as default, or
what you have in your system settings (for which also see the Help), you can
return the week number as an integer with the following VBA statements:
Dim intWeek as Integer
intWeek = CInt(Format(datYourDate,"ww"))
Of course, we would have to know what you are trying to accomplish, not just
how you are trying to accomplish it, to offer really useful suggestions. But
it seems unlikely that you need a table of week-ending dates to accomplish
any purpose of which I can conceive.
But, from the information you've provided, I'd say that it is _likely_
possible to do what you want with the data you have. On the other hand, I
wouldn't want to pursue for you the difficult way to do something simple.
Larry Linson
Microsoft Office Access MVP
