Tony Septav
iggy at nanaimo.ark.com
Thu Oct 7 09:20:53 CDT 2010
Hey A.D.
I gave Eval() a quick test yesterday but did not include the formatting.
I wish I had gotten this yesterday as I basically rewrote my code using
Sql strings.
So rather than Me!ListBox.Requery it is now Me!ListBox.RowSource=Sql.
Thank you very much.
When I get this app out of here I will play with what you have sent.
A.D. Tejpal wrote:
>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 *)
>
>