[AccessD] Query Criteria problem

John,

    I agree. The situation in hand and stage of project would determine the best suited course of action.

    At developmental stage, while testing for errors & projected results, without having to necessarily open the relevant forms, the method using a multitude of variables might prove convenient.

    For normal use, where the user prefers to get the results as per values manually entered or selected on a form, the other method, where the function grabs the required value directly from a form control, has its advantages.

    Both the above alternatives get rid of direct form based parameters in a query, thus overcoming the attendant drawbacks.

Best wishes,
A.D.Tejpal
---------------

  ----- Original Message ----- 
  From: jwcolby 
  To: 'Access Developers discussion and problem solving' 
  Sent: Friday, August 24, 2007 01:07
  Subject: Re: [AccessD] Query Criteria problem


  A.D.

  That certainly works.  The downside is that to see data you have to have a form open, and you have to cause the form to display the values you want pulled in to your function.  Using a static function where the form pushes its value in to the function allows the query to work even if the form is not open, or even if the form has never been opened - just push the values to the function manually.  

  Of course you are correct, the form does have to "push" the right value into the function somehow.  It is not difficult to do that however and I prefer the flexibility to be able to "push" whatever values I please for testing purposes without having to have a form open and muck around getting the form to set the values I want to test.  For error testing (setting values that the form is not SUPPOSED to push) this is much easier with my static functions. 

  Pros and cons of course.

  John W. Colby
  Colby Consulting
  www.ColbyConsulting.com 
  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com
  [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.TEJPAL
  Sent: Thursday, August 23, 2007 3:15 PM
  To: Access Developers discussion and problem solving
  Cc: A.D.TEJPAL
  Subject: Re: [AccessD] Query Criteria problem

  Queries using form based values
  (Use of functions in lieu of parameters) ===========================

      Generic function grabbing values from form controls offers the benefit of global nature without using global variables. It accepts two arguments - form's name & control's name. There is no dependence upon any event related to form or control (e.g. form's current event or control's update event). 

      Whenever called, the value returned by such a function always conforms to the current status of form control. Even if the form is not open, the query will not fail, as the function returns the default value (zero length string for text type data and zero value for number type data). Similar is the case if the control in question happens to be blank.

      Sample generic functions named  Fn_TextData() for text type data and Fn_NumberData() for number type data, are given below. Benefits of using such functions in queries, in lieu of direct form based parameters, have already been brought out in my previous post (dated 22-Aug-2007).

  A.D.Tejpal
  ---------------

  Fn_TextData()
  ===================================
  Function Fn_TextData( _
                          Formname As String, _
                          ControlName As String) As String
      On Error Resume Next
      Dim Rtv As String
      
      Rtv = Nz(Forms(Formname)(ControlName), "")
      Fn_TextData = Rtv
      On Error GoTo 0
  End Function
  ===================================

  Fn_NumberData()
  ===================================
  Function Fn_NumberData( _
                          Formname As String, _
                          ControlName As String) As Long
      On Error Resume Next
      Dim Rtv As Long
      
      Rtv = Nz(Forms(Formname)(ControlName), 0)
      Fn_NumberData = Rtv
      On Error GoTo 0
  End Function
  ===================================