[AccessD] Query Criteria problem

A.D.


Thank you for the information.  I don't have any reservations with using
static functions in queries and do it when required.  I use form derived
values for query criteria since it is easy to do and up to now it worked.
Since I couldn't seem to understand why this isn't working in this case I
went to the static functions and things are progressing nicely.  I would
still like to understand why using form values in a queries criteria does
not work in this project.  Actually it is just in a couple of queries.  I am
sure there is a reason, I just can't figure it out.  

I will keep your message in my archive for future reference.

Thanks again.

Doug

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.TEJPAL
Sent: Tuesday, August 21, 2007 11:56 PM
To: Access Developers discussion and problem solving
Cc: A.D.TEJPAL
Subject: Re: [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 >>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com