[AccessD] Query Criteria problem

Doug,

    Expressing reservation in use of static functions, you have stated:
    "If I use the static function I still have to set it when the form is updated to hold the value that is already on the form."

    This limitation can be overcome by pulling (instead of pushing)  form's data into the function, as demonstrated in sample functions named Fn_CustID() and Fn_EmpID() given below. This ensures that always the latest value in form's control is returned whenever the function is called.

    There are certain other strong reasons to prefer use of such functions, since direct reference to form's controls (in query SQL) suffers from the following drawbacks:

    (a) For a query with parameters clause, a recordset can not be created directly via Currentdb.OpenRecordset method. Leads to Error 3061 (Too few parameters. Expected 1).

    (b) Action query with parameters clause can not be run directly via Currentdb.Execute method. Leads to Error 3061 (Too few parameters. Expected 1).

    A self contained note on the subject is placed below, for ready reference.

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

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

    1 - Use of parameters in queries suffers from following limitations:

        1.1 - For a query with parameters clause, a recordset can not be created directly via Currentdb.OpenRecordset method. Leads to Error 3061 (Too few parameters. Expected 1).

        1.2 - Action query with parameters clause can not be run directly via Currentdb.Execute method. Leads to Error 3061 (Too few parameters. Expected 1).

        1.3 - Parameter clause can not be too long (there are reports of problems encountered on Windows Vista, if it is beyond 63 characters).         An extract from a post (Jul-2007) by Allen Brown in one of the news groups, is placed below:        
        "Paul Overway alerted me to this bug, which occurs only under Access 2000 - 2003 on Windows Vista.  If a parameter name exceeds 63 characters, and your code refers to the parameter of the QueryDef by name, the code fails with Access Error 3000, reporting: Reserved Error -1038. There is no message for this error."

        1.4 - Confusion in dealing with Null value, if data type has been explicitly declared as text - in parameters clause of the query. (This anamoly was brought up in one of Allen Browne's posts in July 2007).

    2 - Suggested Universal Remedy:

        2.1 - The limitations outlined above can be overcome by replacing form based parameters with user defined functions. 

        2.2 - As an illustration, functions Fn_CustID() returning text type, and Fn_EmpID() returning number type, are given below. These functions grab the values entered in relevant controls (TxtCustomerID and TxtEmployeeID respectively) on the target form (Form1). 

        2.3 - Use of functions in this manner greatly simplifies the SQL, at the same time getting rid of parameters clause. Sample select queries Q_CustSelect and Q_EmpSelect, based upon this approach, are given below. These have criteria clause based upon CustomerID and EmployeeID fields respectively. In both cases, if the target text box on the form is blank, all records get included.

        2.4 - Sample append query Q_CustEmpAppend, using functions in lieu of form based parameters, as given below, can be run directly by using Currentdb.Execute method.

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

Q_CustSelect
(Query Filtered as per CustomerID (text type field))
===================================
SELECT Orders.* 
FROM Orders 
WHERE (Fn_CustID() = "") Or (Orders.CustomerID=Fn_CustID());
===================================

Q_EmpSelect
(Query Filtered as per EmployeeID (number type field))
===================================
SELECT Orders.* 
FROM Orders 
WHERE (Fn_EmpID() = 0) Or (Orders.EmployeeID=Fn_EmpID());
===================================

Q_CustEmpAppend
===================================
INSERT INTO Orders ( CustomerID, EmployeeID )
VALUES (Fn_CustID(), Fn_EmpID());
===================================

Fn_CustID()
===================================
Function Fn_CustID() As String
    On Error Resume Next
    Dim Rtv As String
    
    Rtv = Nz(Forms("Form1")("TxtCustomerID"), "")
    Fn_CustID = Rtv
    On Error GoTo 0
End Function
===================================

Fn_EmpID()
===================================
Function Fn_EmpID() As Long
    On Error Resume Next
    Dim Rtv As Long
    
    Rtv = Nz(Forms("Form1")("TxtEmployeeID"), 0)
    Fn_EmpID = Rtv
    On Error GoTo 0
End Function
===================================

  ----- Original Message ----- 
  From: Doug Murphy 
  To: 'Access Developers discussion and problem solving' 
  Sent: Wednesday, August 22, 2007 03:16
  Subject: Re: [AccessD] Query Criteria problem


  Hi Arthur,

  I use static functions where they make sense but in this case I should be, and have always been, able to just refer to the value of a control on an open form.  If I use the static function I still have to set it when the
  form is updated to hold the value that is already on the form.  I was 
  looking for a reason why am am getting this error.  I like to understand why things happen. 

  If I can't figure this out I may go with your suggestion just to get this
  done, but I won't feel comfortable that the original problem is still
  lurking.

  Doug

  << SNIP >>