[AccessD] Query Criteria problem

jwcolby jwcolby at colbyconsulting.com
Thu Aug 23 14:37:39 CDT 2007


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
===================================

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


  Whooo boy, he's itchin for a fight eh? 

  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 Charlotte Foust
  Sent: Thursday, August 23, 2007 12:46 PM
  To: Access Developers discussion and problem solving
  Subject: Re: [AccessD] Query Criteria problem

  That's right, fan the flames!!  LOL

  Charlotte Foust 

  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com
  [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
  Sent: Thursday, August 23, 2007 9:47 AM
  To: 'Access Developers discussion and problem solving'
  Subject: Re: [AccessD] Query Criteria problem

  Hi Charlotte:

  The war has already been won... global variables are just fine and this
  discussion should never again be brought up in polite company.

  Jim 

  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com
  [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust
  Sent: Thursday, August 23, 2007 8:06 AM
  To: Access Developers discussion and problem solving
  Subject: Re: [AccessD] Query Criteria problem

  Hmmn ... Global variables = another war!  LOL

  Charlotte Foust 

  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com
  [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
  Sent: Wednesday, August 22, 2007 5:31 PM
  To: 'Access Developers discussion and problem solving'
  Subject: Re: [AccessD] Query Criteria problem

  Y yo tambien.  I might also say that you can pass values between forms and
between code modules as well.  You do need to be careful though as these
turn into "global variables" and we all know what THAT means.  ;-)

  John W. Colby
  Colby Consulting
  www.ColbyConsulting.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list