error when utilizing a call function in query criteria

[JamieGMN]

I want to use a simple call function in the criteria of the query and I keep getting "Unknown Access database engine error"



Public call function is simple:


dim gdtmSpecDate as Date

Function getSpecDate() As Date
getSpecDate = gdtmSpecdate
End Function

I then incorporate into a query criteria as getSpecDate()

the crazy is the query design even pulls up my list of defined functions and plugs it in with one "(" and when I check the function in the debug it works fine - but then when I open query it sates "Ukown Access database engine error"

any help appreciated

[kd2017]

Grasping at straws here...

Can you post your sql? Maybe a syntax error?
Have you initialized a value in gdtmSpecDate?
Tried a compact & repair?

[Gicu]

Is the function in a standard module (correct) or a form module (incorrect)? Can you change the declaration of the variable from Dim to Public?

Public gdtmSpecDate as Date

Function getSpecDate() As Date
getSpecDate = gdtmSpecdate
End Function

[JamieGMN]

All Thanks for trying to help a novice. Here is what I have found. If I open the query on its own with the getSpecDate() in the criteria section it opens correctly with no errors. If I open a form and initiate VBA code to pass a value into gdtmSpecDate and then try to utilize the query while in the macro, such as opening the query as a recordset that is where I get the error from the query. It is very strange to me that while I am utilizing macros in forms that it corrupts the ability of public module to work. Related for some reason values passed into the public global variables do not seem to be sticking once teh event driven VBA sub routine is exited. I am wondering if there is some nuance to labeling seetings of my code associated with my class objects vs modules? Below is my specific code within the form - pretty straightforward cannot see why it would prevent query from accessing the public routing "getSpecDate()"

Private Sub CmdImportdata_Click()
Dim db As Database
Dim rst As Recordset


'Routine to check if data already exists in table before importing


Set db = CurrentDb()
Set rst = db.OpenRecordset("qryTempTblImport", dbOpenDynaset)
'get date of first data in the Temporary Data table containtnig raw import data
rst.MoveFirst
gdtmSpecDate = rst![TipDate]
Debug.Print gdtmSpecDate
Debug.Print getSpecDate()
rst.Close

'open query of Permanent data and look for equivalent date using GetSpecDate() in the criteria
Set rst = db.OpenRecordset("qryTipDataMatch", dbOpenDynaset)
If rst.RecordCount > 0 Then
MsgBox "There are no files to Import"
exitSub
End If

[davegri]

Try doing away with the variable and use the recordset field directly:

'dim gdtmSpecDate as Date

Function getSpecDate(dArg as date) As Date
getSpecDate = dArg
End Function


Then in the query

getSpecDate(rst!TipDate)

Another possibility, set a tempvar = rst!tipdate,
then the tempvar can be used in the query, no function required.