Form for entering dates to run reports by

[rwilliams]

This is sort of a continuation of this thread -but I'm changing my approach.

I've been trying to figure out Bob's advice of creating a form where users can enter a semester/year and run different reports based on the criteria entered on the form. I know in the previous thread I asked about dates... but that is why I'm changing my approach. Some of the tables require a "date" field entry... while other tables only make sense to relate a "semester" and "year" to. Originally, I would have liked for users to have been able to enter a beginning/ending date OR semester/year into the form and the reports run off of that, but I soon realized that was way too complicated for me.

I have a table called "tblSemesterLookup" that contains the following data:
[Semester][BeginningDate][EndingDate]
Fall---------8/15-----------12/15
Spring------01/10----------05/10
Summer----05/13-----------08/10

Next, I have a form that contains a listbox that calls the data from [tblSemesterLookup].[Semester], and a text box for entering a year.

I decided I would like to do all searches based on a Semester Year which means, queries that include a "date" field will need to somehow figure out which semester that date corresponds to - and I would like it to do this by referencing tblSemesterLookup (so that dates can be changed later if needed).

Problem #1: How to keep track of the choices entered in Semester and Year by the user. I created "tblDateChoices" with fields [SemesterChoice] and [YearChoice] thinking this could be a place to store their choices, but I can't seem to get that to work with the form. It keeps saying "#Name?" in the boxes. Maybe it doesn't like my lookup? I'd like to know if this is even a good approach also.

Problem #2: How to get a query to run based on the criteria entered by the user. (And if the particular query to be run includes the date field, be able to relate the date to a Semester and retain the year entered to run the query by.)

At this point I've over-thought the problem so much I've confused myself. I'm quite happy to have gotten it all written out and hoping it makes sense to you guys (and gals). Help?

 

[rwilliams]

Any ideas on how to do this would be helpful... i might be going about it all wrong... if there's a different way or an easier way, I'm all for hearing.

I don't have to have the tblSemesterLookup - if getting it to work would mean writing a big IF statement, that's fine.... The users can deal with it.

I have 2 databases that I need to be able to do this for. Been racking my brain (and Google, Microsoft, and Forums) to just try to figure out how to write criteria in my query that will look to multiple form fields for its values.

 

[rwilliams]

I think I figure out how to get a qry to look to form fields for values.

Example, I placed

[Forms]![frmMain]![Semester] And [Forms]![frmMain]![Year]

in the criteria for my date field.

Now I just need it to automatically correlate semester/year entries by the user with date ranges.

 

[rwilliams]

This is bringing out some creative thinking on my end. hehe

Here's what I've come up with:

I still have my "tblSemester" that lists the semesters and their ending and beginning dates (month and day). Field names are [Semester], [BeginDate], [EndDate].

I created a form with the following:
combo box -- "Semester_combo" -- pulls its entries from "tblSemester" by row source...

SELECT tblSemester.Semester, tblSemester.BeginDate, tblSemester.EndDate 
FROM tblSemester 
ORDER BY tblSemester.Semester;

text box -- "Year"
text box -- "BeginDate"
text box -- "EndDate"

I'm trying to allow for the selection of a Semester in the combo box and the user entering a year, to automatically fill in dates from the table into the begindate and enddate text boxes.

In the control source of my begindate I've placed:

=Semester_combo.column(1)+"/"+Left([Year],4)

In the control source of my enddate I've placed:

=Semester_combo.column(2)+"/"+Left([Year],4)

So what's happening now is, when the user selects a Semester and enters a year, the beginning date box is filling in correctly. But the Ending Date box will not fill in at all. I've copied my code from an example I found here.

Any ideas on why end date won't fill?

Once I get this to work correctly, I think I am going to just hide the Beginning and Ending Date text boxes, but allow the queries for the reports to pull their data from them.

 

[rwilliams]

Figured out the filling text box problem....

What I ended up doing, was deleting my current combo box and starting over. When going through the wizard again, I added all columns from tblSemester and just changed the 2nd and 3rd column widths to 0. This way I didn't have to edit the sql after the combo box was made... I named the combo box "Semester_combo" and used the same code in my text boxes.

It works now... I have no idea why... crazy access. hehe

Maybe that will save someone else some headache. Cause it sure gave me one.

Sooo.... /self-cheer. go me.