Roger's Access Library Homepage
Forum Home Forum Home > Other Download Libraries > MS Access MVP Libraries > Tejpal, A.D.
  New Posts New Posts RSS Feed - Query_CriteriaStyles
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Please consider donating $1 per sample downloaded, (find out why here)


 Post Reply Post Reply
Message Reverse Sort Order
A.D. Tejpal View Drop Down
Microsoft MVP
Microsoft MVP

Joined: 30 Jun 2008
Status: Offline
Points: 192
Post Options Post Options   Thanks (0) Thanks(0)   Quote A.D. Tejpal Quote  Post ReplyReply Direct Link To This Post Topic: Query_CriteriaStyles
    Posted: 18 Oct 2010 at 1:20pm

Explanatory Notes

    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:
(a) Criteria string is complete, inclusive of field names and operators.
    (b) Criteria string is partial, excluding field names. Operators are included. In other words it represents the right hand portion of a criteria expression, including operators.

    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.

DAO 3.6


Back to Top
Sponsored Links

Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.03
Copyright ©2001-2019 Web Wiz Ltd.