Help! Filtering Report from Multiple Tables

[sushi]

Access is a new medium for me and is proving more complex than need be. But if it can get working its a potentially beneficial addition over the repeated field entry in excel workbooks.



The Current Situation:

There is a Mileage Summary Log with Fields:
[Trip Date] [Purpose] [Destination Details][Detail Summaries][Start ODO][End ODO][KM]

Each Trip has One Date, Purpose, Start ODO, End ODO, and KM (from [Trip Table])
and Many Destination Details & Detail Summaries (from [Trip Details Table])

The goal is to have this Report filterable by:
Required: Between DateA & DateB
Vehicle: Choice (only one can be displayed at a time)
Purpose: Choice (One or Many)

For whatever reason ... it all gets boggled when adding both fields and required criteria from multiple fields.

Help. I think I'm in over my head here.

[pbaldy]

You say it gets boggled, but you haven't shown your effort. Hard to fix what we can't see.

[ssanfu]

Welcome to the forum....

Not enough info to even begin to guess at a solution.

Would you post your dB?

[sushi]

Sorry for being vague, i'm kind of at a loss from where to go after creating tables, queries for the calculations required, and relationships. The database is attached.

So far the 'Mileage Summary Log All' displays the results with the correct visual format but shows all Trip & Trip Detail records.

The goal is show the Mileage Summary Log (All)
with Between Date Options
by Vehicle (only one at a time; choice)
by Purpose (Choice of Multiple or All)

I'm not sure where to start after this (its my mind that starts to get boggled). What type of querie is required to build a filterable report? (Or am I going about this all wrong?)

When I go to make a Query to test ie. Between Date Options (using Allen Brown's Method 1: Parameter query criteria " >= [StartDate] < [EndDate] + 1") it asks for between dates but returns all results ie. Query for a Specific Month will show All Months. (using trip details query & trip details query report).

PUBLICEXAMPLE MILEAGE.zip

[sushi]

Would this be on the right track?

http://www.fontstuff.com/access/acctut08.htm

[pbaldy]

Not sure where you're having trouble. Most of us would use a form to gather the user input, but if I put this in the criteria of the date field:

Between [Start] And [End]

I can enter 2 dates and only get the records between those dates.

[ssanfu]

Would this be on the right track?

It is a start. But I use a form, as Paul suggested, to set the criteria. I build the criteria on-the-fly in code rather than having popup parameters.

I modified a lot of your dB - I do not use spaces, special characters or punctuation in object names.... this includes forms, queries, tables, and reports as well as field names in tables.

Also, "Year" is a reserved word in Access ans shouldn't be used as an object name.I changed ti to "ModelYear".

I never use a table as a form record source; I use queries. In table "Trip" you have a calculated field, "TripKM".... it would be better to do the calculation in a query. See About calculated table fields - http://allenbrowne.com/casu-14.html

I removed the Look up FIELDS in the tables. See The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm

It doesn't make sense to have combo boxes in reports, so I changed that....



Look at the code in the "TRIP" Form module to see the criteria for the report..


Remember, this is an example of HOW to do what you wanted. Look over the VBA. I typically use VBA to create criteria for reports because it is easier to validate and control the criteria string than putting criteria in queries.

[sushi]

Wow, i'm looking through the modifications and trying to learn from your organizing. You make the code look so easy (as a novice it is daunting). Having never touched VBA or code of any type (html aside) I'm a little overwhelmed by the prospect. Thank you for taking the time to provide an example that can be deconstructed. It's much easier to learn about anatomy by dissection.

The evils of calculations in tables were known to me but I had not realized that even for such a small database it would be that big a deal. This will be taken into consideration and an attempt to move it to a querie will be made. Self discipline here will likely guide a good work ethic, I see this goes for naming of fields as well. This will be kept in mind as I proceed to the next phase (and revise this phase).

As for lookup fields, I had no idea it just seemed the only way from an intuitive interface perspective. But now it makes sense that the visual choice is added to the form not the internal structure. This alone will likely diminish potential errors in the future.


*grasshopper cloisters to try a rebuild*

[sushi]

It is a start. But I use a form, as Paul suggested, to set the criteria. I build the criteria on-the-fly in code rather than having popup parameters.

Look at the code in the "TRIP" Form module to see the criteria for the report..


Remember, this is an example of HOW to do what you wanted. Look over the VBA. I typically use VBA to create criteria for reports because it is easier to validate and control the criteria string than putting criteria in queries.

I've spend the allotted time yesterday reading through examples (mainly on allen browne) and going over your VBA Code in anticipation of rebuilding the database to learn how to put it together (as well as rebuilding the database with no table calculations and without a form yet until its creation is better understood). Outside of finding a typo in the word "purpose" I'm at a loss as to why it won't return a report filtered by dates from your provided example modified database.

The Run-time error '3075' persists.

Originally: Defined as "Syntax error (missing operator) in query expression 'TripDate Between = #12-01-2017# And #2017-12'. The EndDate did not show a day (only Year-Month).

Modified: Compared tbStartDate with tbEndDate and noticed tbStartDate format was set to Short Date. Modified tbEndDate to match Format ShortDate. The Run-time error changes to "Syntax error (missing operator) in query expression '#12-01-2017# And #12-31-2017#'. The EndDate now shows a full date with Year-Month-Day but still returns an error.

Thus Thinking it is a Formatting issue: Tried changing the input masks of both start and end to short date (as they were backwards). Error persists. Tried eliminated Short Date Format in Start Date instead of adding it to EndDate. Error persists. Tried eliminating input masks all together. Error persists.

The form does returns a strange situation where if the StartDate is set to 12-01-2017 and no EndDate is set then it returns only results after month 12 with nothing previous. And if the EndDate is set with no StartDate it returns results prior to the EndDate. This is technically correct but it it won't show results between the dates if both have a value greater than 0. And as it shows individually I can't see how it can be a formatting issue if both formats match.

[June7]

Should not be an = sign in the BETWEEN AND phrase.

[sushi]

Should not be an = sign in the BETWEEN AND phrase.

lolz, I just logged in to post that!

Updated to:

sCriteria = sCriteria & "TripDate Between #" & Me.tbStartDate & "# And #" & Me.tbEndDate & "# And "

Thank you all (and especially ssanfu) for the hard work in setting a noob on the correct track.

The database has been rebuilt to Phase One and now off to try Phase 2 on me.lonesome

[ssanfu]

Oops... the one option I didn't test (start and end dates). Don't know how the "=" snuck in there.....

The database has been rebuilt to Phase One and now off to try Phase 2 on me.lonesome

Nope.... not alone.

May the force ....err, forum be with you....

[sushi]

Figured you were making sure I was awake.

See you around :-)