[AccessD] Re: DatePart Question

Ahh Grasshopper,

And how will you learn if you do not create it?  I mean, I could send it to 
you, or post the table to the files section, but that would not help you or 
anyone else understand the use of the functions that went in to create 
it.  I teach an MS Access Developer's Workshop and have been doing that for 
about 8 years.  One of the things I do in there is come up with an idea for 
a function, process, etc. and have them as a group build it.  And about the 
holiday and fiscal year "modifications," since I leave that up to the user 
to define after the table is created, there are not really any 
modifications to make to the data for me.  Now, having said all of that, I 
did send the MDB to John Colby, but that was primarily because I know he 
could have created it in his sleep and because he has shared he work rather 
freely with us and the Access development world in general.  And, it is not 
reinventing the wheel.  It is learning how to build a wheel so you can 
build the cart that goes with the wheel.  ;-)  Besides, you may build it 
quite differently from the way I do it or the way John would do it.

Here is another example of "thinking outside of the box."  How do you do 
entry for addresses?  Well, I use a table of Zip codes.  From that, I get 
the city and state.  So they enter the zip code first and then they may 
have to select the correct city but the state will always be correct.  I 
use a data-limited combobox for the city so that, for example, they can 
select one of the 4 "towns" that use 77418 (my zip code).  I also default 
each of the limited comboboxes to the first value in their list, so if 
there is only one city (77002, Houston) in the list, they do not have to do 
anything else, thus saving many keystrokes.

Actually, you would get both Jan 2004 and Jan 2003, etc. data because it is 
looking at only the month with what you were looking at doing.  What I do 
is give the user a form for selecting a beginning month, beginning year, 
ending month, and ending year for reports.  If they leave the month off, I 
only look at the year.  If they only give the beginning month and year, I 
do a year-to-date.  And so on.  Since I dynamically build the where clause 
of the SQL statement, I just implement it as a business rule and explain to 
the users how their entries will power the criteria for their reports.

The end result is that the Date Dimension table will give you a lot of 
control and flexibility in being able to gather criteria and validating it.

Robert

At 03:46 AM 2/3/2004 -0600, you wrote:
>Date: Mon, 02 Feb 2004 12:12:19 -0600
>From: "Gina Hoopes" <hoopesg at hotmail.com>
>Subject: [AccessD] Re: DatePart Question
>To: accessd at databaseadvisors.com
>Message-ID: <Sea1-F130hhDpB8ZdL300037f77 at hotmail.com>
>Content-Type: text/plain; format=flowed
>
>Thanks to you and to Gustav for giving me more good ideas.  I guess your
>question kind of goes back to my original one.  If it looks at the whole
>date before giving me the "1" for the month, then I'll get Jan 05 data
>rather than Jan 04 data.  I guess the question is senseless since both Joe
>and Gustav have given me good alternatives, but I may still have to plug
>some fake data just to see what I get back.
>
>I have been following the dates table discussion and I've got another
>(stupid) question.  Except for fiscal years and holiday schedule
>modifications, is this not the type of table that one of the people who has
>already spent the time to create it could just put out for everyone to use?
>I'm afraid I started following the discussion a bit late, so maybe I missed
>something.  It's not that I mind a day spent creating something really
>useful, it just seems a bit like re-inventing the wheel.
>
>Gina