MS Access 2003 - Filter by Form

[Ryman1986]

Hi all,

I hope you can help me with this query, I know it is possible, but cannot work out the logistics of how to make it work.

I have a table in Access called tblActionLog, which is basically a list of actions that have been recorded for our business. The table consists of eight columns of data, which are:

ActionID - Autonumber
BusinessArea/Meeting - Number (Drop down list from tblBusinessArea/Meeting)
DateRaised - Date/Time
ActionDescription - Memo
Owner - Number (Drop down list from tblOwner)
DueDate - Date/Time
Comments - Memo
CompletionDate - Date/Time

I am attempting to create a form where you can filter by one or more criteria. The criteria being: BusinessArea/Meeting, by a range for Date Raised ie between 01/04/2011 - 31/05/2011, by Owner, by a range for DueDate ie between 01/04/2011 - 31/05/2011, by a range for CompletionDate ie between 01/04/2011 - 31/05/2011.

I would like the end results to be placed in a pre-prepared report format. rptActionLogReport, which has the same number of fields as the table.

If you can help with this query I would be highly appreciative as I have been looking for a solutions for this for ages and have come up with nothing

Thanks

Ry

 

[Joe4]

Typically, I will create a form with all my selection criteria, then use VBA to create the necessary SQL for the query behind the scenes.

Here are a few links on the topic to get you started. Post back if you get stuck:

http://www.mrexcel.com/forum/showthread.php?t=430364
http://www.mrexcel.com/forum/showthread.php?t=325798

 

[jasmith4]

I think a parameterized query is in order here. Make a new blank query, paste this into its SQL view, but then go to View/Design and pull down Query/Parameters. There are two text parameters for the bus. area and owner, and two for the dates.

PARAMETERS [Enter bus area / mtg] Text ( 255 )
, [Enter owner] Text ( 255 )
, [Enter first date] DateTime
, [Enter last date] DateTime;
SELECT tblActionLog.*
FROM tblActionLog
WHERE (((tblActionLog.[BusinessArea/Meeting])=[Enter bus area / mtg]) AND ((tblActionLog.DateRaised) Between [Enter first date] And [Enter last date])
AND ((tblActionLog.Owner)=[Enter owner])
AND ((tblActionLog.DueDate) Between [Enter first date] And [Enter last date])
AND ((tblActionLog.CompletionDate) Between [Enter first date] And [Enter last date]));

 

[boblarson]

My personal preference is to NOT limit the query by the criteria up front. I prefer to make a more GENERIC and reusable solution where you can call the report and pass the where clause at the time of opening instead of relying on having it based on a query with static references to parameters or form inputs. So, I can use the SAME report but in different ways from different forms if I so desire. That can save a LOT of space in that you only need to create a single report (well, sort of, because it wouldn't be a one-size-fits-all report but specific enough for the needs but generic enough that you can limit it by various means).

You might check out my generic report sample here

http://downloads.btabdevelopment.com/Samples/reports/Sample-GenericReport.zip

- where I use code to create a where clause that I can then pass in the DoCmd.OpenReport command and then it opens that report for my criteria.

Oh and jasmith4 - I would avoid parameter prompts in queries at all costs and here are some good reasons why:
http://www.tek-tips.com/faqs.cfm?fid=6763

 

[jasmith4]

Duly noted, Bob. I like the idea of opening a form or report with a filter rather than filtering the query, although constructing a WHERE clause makes for messy VBA occasionally. As for query parameters, indeed they're a very primitive UI tool, but I like building a form whose VBA would populate them (the querydef's Parameters) before executing the query, so the primitive prompts would be invisible to the user.

 

[boblarson]

although constructing a WHERE clause makes for messy VBA occasionally.

It can but if done right it would be rare that would be the case. If done in the method that I have in my sample, you can keep it very readible and self documenting and it will actually make things a lot cleaner than they would have been in a query as parameters which you would then also need to have each use the Or Is Null part in there in case they weren't supplied.

As for query parameters, indeed they're a very primitive UI tool, but I like building a form whose VBA would populate them (the querydef's Parameters) before executing the query, so the primitive prompts would be invisible to the user.

I would rather a form reference be used than a parameter prompt. And if you are going to use a querydef to supply the parameters you might as well just leave the prompts off as well, no?

 

[jasmith4]

What I like about parameters as part of the query is just that: they are part of the query, even the very SQL statement that underlies the query, rather than dependent on an external object (a form). That makes it more portable to other databases, although that's quite an inexact science! I also like the strong typing.

But I've done it both ways before, even switching between them frequently at users' requests, so I certainly appreciate the strengths and weakness of both approaches. One workaround I like is having a text parameter called [Prefix:] and a criterion saying LIKE [Prefix:]&"*", or an integer parameter called [Minimum:] and a criterion saying >=0+NZ([Minimum:]), each with the functionality that just hitting Enter will be the same as no criterion.

In any case, whereas I'd love to intelligently debate these and many other Access topics with an MVP such as you, perhaps we're confusing poor Ryman86, who in his circumstance should definitely go with your form-fields-as-parameters approach.

 

[Ryman1986]

Thank you both for your help with this. You both obviously know your way around MS Access far better than I do.

I have looked at your generic report template boblarson this looks great, but to add a bit more complexity to the query, is it possible to choose a date range, a business area and owner and then produce a report rather than havign them all serperate reports? or if for example you wanted to search for actions based on two of the search criteria ie date range and business area with all owners? is there a way of saying if blank (Is Null) search all??

I hope this makes sense...

but once again thank you both for your assistance with this.

Cheers

Ryman

 

[jasmith4]

The general idea is that you have three objects:
1. A query that extracts data from your tables and has no WHERE clause;
2. A report based on that query which pretties the results, summarizes, groups, etc.;
3. A form with no RecordSource, but with text boxes, check boxes, etc., to capture all the criteria you want.

The plan is that the user opens the form, chooses all the criteria, and hits "Do it". The "Do it" button's VBA code reads all the controls and constructs a properly-formatted WHERE clause. It then opens the report with the command DoCmd.OpenReport WhereCondition:=<yourclause>.

So the tricky part is the design of the form and its code, but as Bob points out, it's very flexible and user-friendly, because you can do anything you want in VBA, and you don't have to keep changing the query's criteria, because there are none.

 

[Ryman1986]

thanks Jasmith4, I am almost there...

I have the three lines of code needed, which all work individually, but i cannot work out how to include them all in the same report command.

The code is:
1 - DoCmd.OpenReport "rptActionLog", acViewPreview, , "[DateRaised] Between #" & Me.cboFrom & "# And #" & Me.cboTo & "#"
2 - DoCmd.OpenReport "rptActionLog", acViewPreview, , "[BusinessArea/Meeting]='" & Me.cboBusinessArea & "'"
3 - DoCmd.OpenReport "rptActionLog", acViewPreview, , "[Owner]='" & Me.cboOwner & "'"

Is it possible to add some code after each line such as And or & to have multiple conditions?

So basically I want one report to be produced by choosing multiple conditions example of my incorrect code:

DoCmd.OpenReport "rptActionLog", acViewPreview, , "[BusinessArea/Meeting]='" & Me.cboBusinessArea & "'" - "[Owner]='" & Me.cboOwner & "'" - "[DateRaised] Between #" & Me.cboFrom & "# And #" & Me.cboTo & "#"

I know this won't work, but is there something i need to add in place of the hyphons("-")?

Thanks in advance for your help.

Cheers