[AccessD] Function with Operators and Conditions

Tony,

    Apparently, you wish to embed a criteria string (represented by a function acting as wrapper for a global variable) within the SQL of a saved query.

    The objective sought by you could be realized by enclosing the said function in Eval() function in SQL, adopting either of the two styles as follows:

    A - The function in question provides just the right  hand portion of criteria clause, including the comparison operators (>, <, = etc). In this approach, handling of field name featuring in the comparison, needs special care as follows:

    A.1 - Date type field:
        Applying "mm/dd/yyyy" format on date type field is found necessary as Eval() function in the query won't be able to resolve non-numeric component (like Jan, Dec etc) if short date format for local regional settings happens to include "mmm". Finally, this formatted value has to be included in outer hashes.

    A.2 - Text type field:
        Field value has to be enclosed in outer single quotes.
    
    B - The function in question accepts field name as its argument and provides the complete criteria string. Requirement of formatting and / or outer delimiters as necessary (for date or text type fields) is handled within the function. Query SQL becomes drastically simplified.

    Sample code in general module is given below (at the end of this message). strCriteria is a public global variable (variant type). Fn_Criteria_A() is a simple wrapper function for use in style A mentioned above. Fn_Criteria_B() is meant for use in style B mentioned above. It accepts the field value as its argument and handles different incoming data types suitably, providing formats and delimiters as found necessary.

    Sample query as per style A above would be as follows (T_Sales is the name of source table, having SaleDate as one of its fields):
=================================
SELECT T_Sales.*  
FROM T_Sales  
WHERE Eval("#" & Format([SaleDate], "mm/dd/yyyy") & "#" & Fn_Criteria_A());
=================================

    Sample query as per style B above would be as follows (T_Sales is the name of source table, having SaleDate as one of its fields):
=================================
SELECT T_Sales.*
FROM T_Sales
WHERE Eval(Fn_Criteria_B([SaleDate]));
=================================

    Typical statement in form's VBA module for assigning a value to the global variable could be as follows (TxtDate is the name of control on calling form):
'=================================
        strCriteria = ">= #" & _
                        Format(Me.TxtDate, _
                        "mm/dd/yyyy") & "#"
'=================================

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

' Sample code in general module
'============================
' Declarations section
Public strCriteria As String
'-------------------------------------------

Public Function Fn_Criteria_A() As String
    Fn_Criteria_A = strCriteria
End Function
'-------------------------------------------

Public Function Fn_Criteria_B( _
                FieldValue As Variant) As String
    Select Case TypeName(FieldValue)
        Case "Date"
            ' Enclose FieldValue argument within outer
            ' hashes after proper formatting
            Fn_Criteria_B = "#" & _
                Format(FieldValue, "mm/dd/yyyy") & _
                "#" & strCriteria
        Case "String"
            ' Enclose FieldValue argument within outer
            ' single quotes
            Fn_Criteria_B = _
                "'" & FieldValue & "'" & strCriteria
        Case Else
            Fn_Criteria_B = FieldValue & strCriteria
    End Select
    
    ' Note:
    '   Formating of date value as per "mm/dd/yyyy"
    '   is found necessary as Eval() function in the
    '   query won't be able to resolve non-numeric
    '   component like Jan etc if short date format for
    '   local regional settings happens to include "mmm"
End Function
'===================================

  ----- Original Message ----- 
  From: Charlotte Foust 
  To: Access Developers discussion and problem solving 
  Sent: Wednesday, October 06, 2010 20:37
  Subject: Re: [AccessD] Function with Operators and Conditions


  MyDate has to be a field in the query and you don't do it like that.
  The expression is telling the query engine to compare the field MyDate
  to the result of the function FindMyDate(StartDate).  Where are you
  trying to do this?  Me!StartDate suggests you're doing something in a
  form.  You can't just hash them together like that.  If you're doing
  it in a query the suggestion I made originally should work.  If you're
  doing it in code, post the code because it won't work the same way.

  Charlotte Foust

  On Wed, Oct 6, 2010 at 7:13 AM, Tony Septav <iggy at nanaimo.ark.com> wrote:
  > Hey Charlotte
  > Thanks
  > But if I try to put an operator in the function
  > ie. MyDate="<#" & Me!StartDate & "#"
  > FndMyDate=MyDate
  > And put FndMyDate() in the Query Grid
  > I get the error message "Data type mismatch in criteria expression"
  > No biggy I just used sqls for the list boxes rowsource.
  >
  > Charlotte Foust wrote:
  >
  >>You can put the function in the expression cell (i.e., Expr1:
  >>[MyDate]= FindMyDate(= Date variable) and then put True in the
  >>criteria cell.
  >>
  >>Charlotte Foust
  >>
  >>On Tue, Oct 5, 2010 at 8:26 AM, Tony Septav <iggy at nanaimo.ark.com> wrote:
  >>
  >>>Hey All
  >>>Is it possible to embed an operator or condition in a function that is
  >>>used as criteria in the query grid.
  >>>I am sure I have solved this problem before, blame it on old age.
  >>>What I want to do is change the criteria on the fly.
  >>>Equal to FndMyDate(= Date variable)
  >>>Less Than FndMyDate(<Date variable)
  >>>Show all FndMyDate(Like *)