How to pass date parameter to query via VBA ?

[Kalle via AccessMonster.com]

I use Access 2000.
The following query works totally fine if I double-click on the following query in the queries folder. It asks for the parameter 'todate' as expected and returns the expected rows.

Query1:
PARAMETERS todate DateTime;
SELECT Data.CDATETIME
FROM Data
WHERE Data.CDATETIME = workday(todate,-1);

But if I try to execute the query via VBA, the parameter 'todate' is not passed to the workday function. I use the following code:

Dim query As Object
Dim toDate241 As Date

toDate241 = CDate(InputBox("date:", , date))

Set query = CurrentDb.QueryDefs("Query1")
query.Parameters("toDate").Value = toDate241 ' seems to works
query.openrecordset ' in this line the parameter is not properly passed to the workday() function

The workday function is declared via
Public Function workday(dt As Date, move As Long) As Date

Workday() only gets "00:00:00" as value for dt, not the value that is shown as parameter value of the query in the watch window. dt is Empty if I use Variant as type of dt. Can someone tell me how to pass the date, please ? Thanx.

 

[[MVP] S.Clark]

set rs = currentdb.openrecordset "SELECT Data.CDATETIME FROM Data WHERE
Data.CDATETIME = workday(" & toDate241 & ",-1);"

 

[Kalle via AccessMonster.com]

Thank you, but, unfortunately, I don't compose the query within VBA. The query is saved as a normal Access object instead of. So, I can't use your proposal. In the real world, my query is multiple nested and quite complex, and furthermore, used at other locations. So your way is not appropriate for me.

Do you have any other idea ?