MS Access "Undefined function in Expression"

Question

MS Access "Undefined function in Expression"

Zhang, De-An 40

I wrote the below function and am trying to use it in a query but I keep getting an "Undefined function in Expression" error:

User's image

Option Compare Database

Option Explicit

Public Function DZRxCheckEligible(Location As String, SurgeryDate As Variant) As Boolean

If Location = "NCL OR" And SurgeryDate < #5/7/2025# Then

    DZRxCheckEligible = False

Else

    DZRxCheckEligible = True

End If

End Function

George Hepworth 22,220 Reputation points Volunteer Moderator

2025-10-08T16:22:18.5666667+00:00

Please show us the SQL from the query where you want to use the expression. Open it in SQL view and copy and paste the SQL into your reply.

Also, please indicate whether this function is in a stand-alone module or a form or report module.

Thank you.

Answer accepted by question author

3 additional answers

Your answer

George Hepworth 22,220 Reputation points Volunteer Moderator

2025-10-08T16:22:18.5666667+00:00

Please show us the SQL from the query where you want to use the expression. Open it in SQL view and copy and paste the SQL into your reply.

Also, please indicate whether this function is in a stand-alone module or a form or report module.

Thank you.

Answer 1

peiye zhu 320

Most of times,the spelling cause this kind of problem. Debug this function with fixed varieties like: select DZRxCheckEligible("NCL OR",#2024-01-01#) By the way,Note the module name is also DZRxCheckEligible. Try rename module name to another name like modOne.

Answer 2

Duane Hookom 26,555 Volunteer Moderator

It looks like your module and function share the same name. This is a no-no. That's why must of us old programmers use naming conventions so the Module name would be like modDZRxCheckEligible.

Also, your where condition uses a limited set of fields. I would probably remove the logic from the query and create one or more functions that accept the field names and return an appropriate value. This would be much easier to understand in the future.

Answer 3

Karl Donaubauer 2,696 MVP

Hi,

Where is your function located? In order for it to be found by the query, it must be in a standard module (not e.g. in the module of a form). These are the modules listed in the navigation pane.

Servus
Karl

Access Forever News DevCon
Access-Entwickler-Konferenz AEK 18.+19.10. Nürnberg

Answer 4

Hi @Zhang, De-An

-Update: The issue was resolved by renaming the module to a different name from the function, as confirmed by @Zhang, De-An
Thank you for reaching out to the Microsoft Q&A Forum.

As George and Karl mentioned earlier, it would be helpful if you could provide a bit more information:

-Is your function placed in a standard module (listed under the “Modules” section in the Navigation Pane), or is it currently inside a form or report module?

-Could you please open your query in SQL View and share the SQL statement here?

Access normally recognizes Public functions that are defined in standard modules. If the function happens to be located in a form or report module, or if there are any compilation errors in your VBA project, the query might show the “Undefined function in expression” message.

I look forward to hearing from you.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

Thank you all for your help and patience:

I believe my it is a standard module:

User's image

Here is the SQL Coding:

SELECT DISTINCT AllSports.[Patient Name], AllSports.[Last Name], AllSports.DOB, AllSports.MRN, AllSports.CSN, AllSports.Location, AllSports.Surgeon, AllSports.[Resp An Provider] AS Anes, [AllSports]![Discharge Date]-[AllSports]![Surgery Date] AS LOS, AllSports.[Surgery Date], AllSports.[In Room], AllSports.[Out Room], AllSports.[Discharge Date], AllSports.Procedures, AllSports.procCPTCode AS CPT_Bill, AllSports.procICD10Code AS ICD_Bill, AllBlocks.[File Time] AS BlockDate, IIf(Int([AllBlocks]![File Time])<>Int([AllSports]![Surgery Date]) And [AllBlocks].[File Time] Is Not Null,"No","Yes") AS BlockCheck, AllBlocks.[Note Author] AS BlockBy, AllBlocks.Comments, EPIC_Control_Rx.[Order Instant] AS RxTime, EPIC_Control_Rx.[Ordering User] AS RxOrderer, EPIC_Control_Rx.[Order Description], EPIC_Control_Rx.[Administered Med Amount], EPIC_Control_Rx.[Prescribed Quantity], EPIC_Control_Rx.[Owning Pharmacy], AllSports.[Dosing Weight], InPtOpioidBenzo.[Order Name], IIf((Int([InPtOpioidBenzo]![Administration Instant])<=Int([AllSports]![Discharge Date]) And Int([InPtOpioidBenzo]![Administration Instant])>=Int([AllSports]![Surgery Date])) Or [InPtOpioidBenzo]![Administration Instant] Is Null,"Yes","No") AS DuringStay, InPtOpioidBenzo.[Administration Instant], InPtOpioidBenzo.Route, InPtOpioidBenzo.[Ordering User], InPtOpioidBenzo.[Administering User], InPtOpioidBenzo.[Administered Dose Amount], InPtOpioidBenzo.[Administered Dose Unit], InPtOpioidBenzo.MedicationName, InPtOpioidBenzo.MedType, DZRxCheckEligible([AllSports]![Location],[AllSports]![Surgery Date]) AS RxCheck

FROM ((AllSports LEFT JOIN EPIC_Control_Rx ON AllSports.MRN = EPIC_Control_Rx.MRN) LEFT JOIN AllBlocks ON AllSports.MRN = AllBlocks.MRN) LEFT JOIN InPtOpioidBenzo ON AllSports.MRN = InPtOpioidBenzo.MRN

WHERE ((([AllSports]![Discharge Date]-[AllSports]![Surgery Date])=1 Or ([AllSports]![Discharge Date]-[AllSports]![Surgery Date])=0) AND ((IIf(Int([AllBlocks]![File Time])<>Int([AllSports]![Surgery Date]) And [AllBlocks].[File Time] Is Not Null,"No","Yes"))="Yes") AND ((IIf((Int([InPtOpioidBenzo]![Administration Instant])<=Int([AllSports]![Discharge Date]) And Int([InPtOpioidBenzo]![Administration Instant])>=Int([AllSports]![Surgery Date])) Or [InPtOpioidBenzo]![Administration Instant] Is Null,"Yes","No"))="Yes"));

Dora-T 8,520 Reputation points Microsoft External Staff Moderator

2025-10-10T01:55:53.2833333+00:00

Hi @Zhang, De-An
Thank you for the update.

Please try renaming your module to a different name and check if this resolves the issue.

I look forward to hearing how it goes.
Dora-T 8,520 Reputation points Microsoft External Staff Moderator

2025-10-13T05:25:00.3366667+00:00

Hi @Zhang, De-An

Is there any update from you?

If yes, please feel free to let us know.
Zhang, De-An 40 Reputation points

2025-10-13T17:47:42.43+00:00

Thank you all!

I changed the name of the Module so that it is different from the function and that solved the problem!
Dora-T 8,520 Reputation points Microsoft External Staff Moderator

2025-10-14T02:28:00.19+00:00

Hi @Zhang, De-An

Thank you for the update.

I’m glad to hear that the issue has been resolved. It’s my pleasure to assist you.

I’ve updated my main answer to include your confirmed solution so others can find it more easily.

If the issue is fully resolved, please consider marking it as an accepted answer. This helps others in the community who may be facing similar challenges, and it can build our platform stronger.

Thank you for your time and understanding!

Share via

MS Access "Undefined function in Expression"

3 additional answers

Your answer