How to a function from a query

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc used Ask the Experts™
Is there a way to call a function from a query criteria , for example let assume we have this function below:

Function pBuildSQLWhereFormat(pvarFldValue As Variant) As String
    If Not IsDate(pvarFldValue) Then
        ' For example, some default value.
        pvarFldValue = Date
    pBuildSQLWhereFormat = "'" & Format(DateValue(pvarFldValue), "yyyy\/mm\/dd") & "'"
End Function

Below is a parameter query form

Between [Forms]![frmsalesorder].[txtstartdate] And  [Forms]![frmsalesorder].[txtstartEnddate]

What if I call the function above like below:

  pBuildSQLWhereFormat(Between [Forms]![frmsalesorder].[txtstartdate]) And pBuildSQLWhereFormat( [Forms]![frmsalesorder].[txtstartEnddate])

Am right or wrong????????????


Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Yes, you can call functions.   They must be declared public to do so.  

  As far as the statement, you were close.   But rather than pass the whole WHERE, you'd do the fields individually:

 Between  pBuildSQLWhereFormat([Forms]![frmsalesorder].[txtstartdate])   AND pBuildSQLWhereFormat( [Forms]![frmsalesorder].[txtstartEnddate])

A couple of notes when calling functions:

1. If the function has no parameters, it gets called once for the entire query.
2. If it has parameters, it gets called for each row.
3. If it is used in a criteria expression, it's called twice for each row.

Fabrice LambertConsulting
Distinguished Expert 2017

Alas wrong, because "Between [Forms]![frmsalesorder].[txtstartdate]) And pBuildSQLWhereFormat( [Forms]![frmsalesorder].[txtstartEnddate]" isn't a valid VBA instruction.

Plus, if you give a valid instruction as parameter, the function will recieve its result (in your case, probably a boolean).

Ehence why you should avoid variant type as parameters (unless no other choice is possible).
Ganesh GuruduSenior Consultant


--Then use below code to convert required format.

--you may need to check is the input is not null and valid date or not . or use exception block
--raise_application_error(-20001, :txtstartdate);

v_DATE_START := TO_DATE(:txtstartdate, 'YYYY/MM/DD');
v_DATE_END := TO_DATE(:txtstartEnddate, 'YYYY/MM/DD');

select * from table_name where column_name between v_DATE_START  and v_DATE_END ;

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial