Query_CriteriaStyles |
Post Reply |
Author | |
A.D. Tejpal
Microsoft MVP Joined: 30 Jun 2008 Status: Offline Points: 192 |
Post Options
Thanks(0)
Posted: 18 Oct 2010 at 1:20pm |
Query_CriteriaStyles This sample db demonstrates use of criteria string stored in a table as embedded criteria clause in a saved query. It can of special relevance where the situation demands use of saved query.
If the developer is in a position to manage via SQL string in VBA code or programmatic application of filter on a form or report, the task is rather straight forward, as it involves routine concatenation of stored criteria string into the overall SQL string. However, if it is found necessary to use stored criteria in a saved query, careful handling become crucial to successful implementation. This is on account of the fact that when a stored criteria string is embedded in the SQL of a saved query, its elements acquire a dormant state. For example "[MyField] IN (50,100,150)" does not come across as a meaningful clause. The argument for IN clause remain frozen as string "50,100,150". For activating such an embedded string, it needs to be enclosed within Eval() function. Moreover, field names, wherever occurring within the criteria string, are required to be replaced by their respective current values, duly enclosed within appropriate delimiters as applicable. This is because, a string argument for Eval() function has to be intrinsically meaningful. Two styles are covered as follows: Style (a) has the advantage that it is universal in scope, permitting complex criteria, involving interaction between multiple fields for unlimited what-if scenarios, while the SQL for saved query remains unchanged. On the other hand, style (b) is simpler, not requiring any pre-processing of criteria string before application of Eval() function. However, in this case, the expression is confined to only one field at a time, with separate saved query per criteria field of interest. For each of the two styles, two alternative methods for fetching the criteria string are demonstrated (either via form control or via DLookUp() function)' The sample has interactive feature, wherein the user can play with the originally stored criteria strings and watch the outcome. At each stage, SQL of saved query currently in force, also gets displayed. Whenever desired, original criteria strings can be restored on click of a command button. Version: Access 2000 file format. Download >>: uploads/37/Query_CriteriaStyles.zip
|
|
Sponsored Links | Want a good read? Try The Summer of His Life (available on Amazon!) |
Post Reply | |
Tweet
|
Forum Jump | Forum Permissions You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum |