Report Filters
The single most important use of embedded SQL is the flexibility it provides in filtering reports. Since it is common to have many reports based on a similar set of filter parameters, it is often a good idea to have one standard form where the user can select the filters before the report is run. When the report is run, the SQL string can be constructed based on the filters established on the form. This concept is far easier to observe than it is to describe, so let’s take this one step at a time. Begin by looking at the frmReportFilter form. This form is used by several reports to build the generic filtered recordsource for the reports. You will see that it is user friendly and guides the user through the possible options.
As the user selects the parameter to filter by, the corresponding field for the filter appears on the form for data entry.
When all the filters are selected, the user chooses the appropriate report. Using Report 1 as an example, we now get to examine the code behind the button.
The first thing you will note is that the majority of the processing for this button occurs in the buildfilter function. Generally, the block of code used to build the filter is very generic and called from several places. Rather than duplicating the code over and over, we have placed it in a function for convenience and have it returned to our main functions as a string. We have also placed the names of the recordsource and the filter into local string variables so we can verify them during the debug process.
The buildfilter function is fairly straightforward. The function goes through each of the possible filter check boxes to see if the box is checked and if a filter is to occur. If checked, the code fragment is built for that specific conditional. Look at the first possible filter, which is Customer. If the user has opted to filter by customer, that customer name is appended to the filter string as "customerID = " followed by the ID of the customer. This process is repeated for each of the other possible filters. Also of interest here is the way the AND operator is added to the filter string. If something exists before the current conditional, the program inserts the " AND" operator. If there is nothing before the current condition, there is no need for the " AND" so it is not inserted.
One of the major potential trouble areas is determining when the special delimiters for strings and dates are needed and how to construct them. The process is identical for strings and dates and is illustrated with the date filter fragment above. In our case, we want the date to be between a start date and a stop date. The user enters two dates in the filter form and it is then the program’s job to parse that into a valid string. The first step is to build the start date by setting up the conditional "dateSold = " followed by 原文请找腾讯752018766辣,文-论~文;网http://www.751com.cn becomes "dateSold = #". To this we append the date from the text box, startdate. This value is currently a string that we want to append to our current string, so we now have "dateSold = #" & startdate. We finish off this filter fragment with a final "#" also expressed as a string, giving us the final "dateSold = #" & startdate & "#". Note that the startdate is not included in quotes since we want the value of the field startdate, not the word “startdate.” Also note that the string concatenation symbol “&” is preceded and followed by a space while the “#” symbols are not. Let’s put this through a manual code-generation process using the date 2/5/2004. Plugging in all the values and evaluating it produces the string "startdate = #2/5/2004#". The same approach is used for strings to be inserted, but the “#” character is replaced by the single quote ('). If we were to use the salesman’s last name instead of the ID value, that string would be "lastname = '" & lastname & "'".
Sidebar: If you haven’t guessed, things can get very hairy if you use the #, quote, or double quote characters in field names. In those cases you have to go through the very careful gyrations of making sure the code knows how to process the characters correctly. It can easily become a mess if you are not careful. One database that we inherited used the fieldname “father’s name.” We spent several hours figuring out why the filter was not working properly before we realized that the quote was fouling up how the string was being handled. Sure, you can use paired double quotes, but in this case a bit of planning at the start can save you many hours of grief later.
Sidebar 2: One of the more unusual errors that popped up was when we used the last name rather than the index in a dynamic SQL query. Everything went fine until we had the name O’Brian. Access decided that the single quote in the name was a string delimiter and the programming went crazy. Besides being faster, these problems do not occur when you use indexes.
上一页 [1] [2] [3] [4] [5] [6] [7] 下一页