How to a function from a query

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
EndIf
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????????????

Regards

Chris

ASKER CERTIFIED SOLUTION

Jim Dettman (EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fabrice Lambert

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 Gurudu

DECLARE
v_DATE_START DATE;
v_DATE_END DATE;

--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 ;