Syntax error (missing operator) in query expression when using DLOOKUP function

[Bcanfield83]

I have a form that is used to manage incoming requests submitted. There's a process which imports them into the database. The form is bound to a query which essentially just includes requests in which "Open" is equal to true.
So at any given time, there could be 10 items in the form, or there could be none whatsoever.


In the Form_Current event, I have some code that pulls in the Request Type (e.g.: users submitting the form can pick a few diff options) through DLOOKUP.. This is then used in the subsequent "Select Case" portion to Show or Hide certain controls on the form.
The problem I'm running into is when there's NO open requests - e.g.: the underlying data source of the form returning no results -- the form is error'ing out with the "Syntax error (missing operator) in query expression" error.
When I click debug, it's on the "strAppType" variable.. So it's obviously not liking the fact that it's returning a null value..
But I can't seem to figure out how to get past this. I tried using the NZ function around the DLOOKUP syntax but the same error returned when trying to open up the form.
Any help would be appreciated. Thanks!

Dim strAppType As Variant
Dim strEndDate As Variant
Dim UserRoleCtrl As Control
Dim UserToMirrorRoleCtrl As Control
Dim frmHRS0014s As Form


Set frmHRS0014s = Forms!frmSystem_Change_Requests_New


strAppType = DLookup("[System_Affected]", "[qry_System_Changes_Open]", _
                   "[Request_Number] = " & frmHRS0014s!txt0014ID)
                     


strRequestTypeENS = DLookup("[ENS_Request_Type]", "[qry_System_Changes_Open]", _
                     "[Request_Number] = " & frmHRS0014s!txt0014ID)




On Error GoTo Err_Handler




ShowControls True, "Default Info"
ShowControls False, "User Info", "ENS", "HRIS PB", "HRIS.NET", "WexHealth"
ShowControls False, "NewRole", "NewRoleENS", "NewRolePB", "NewRoleWex", "NewRoleNet"
ShowControls False, "Screen Info", "Screen Info Net", "Screen Info ENS", "Screen Info PB"
 
Select Case strAppType
    Case "ENS"
       ShowControls True, "ENS"
       Select Case strRequestTypeENS
            Case "Modify_Existing_User_Add", "Modify_Existing_User_Remove"
            ShowControls True, "User Info"
            Case "Create_New_Group"
            ShowControls True, "NewRole"
            ShowControls True, "NewRoleEns"
            Case Else
            ShowControls True, "Screen Info"
        End Select

[pbaldy]

You can try

strAppType = Nz(DLookup("[System_Affected]", "[qry_System_Changes_Open]", "[Request_Number] = " & Nz(frmHRS0014s!txt0014ID, 0)), "")

But you may need this:

http://www.theaccessweb.com/forms/frm0022.htm

for the form reference.

[Bcanfield83]

Thank you! This worked perfectly!
strAppType = Nz(DLookup("[System_Affected]", "[qry_System_Changes_Open]", "[Request_Number] = " & Nz(frmHRS0014s!txt0014ID, 0)), "")

You can try

strAppType = Nz(DLookup("[System_Affected]", "[qry_System_Changes_Open]", "[Request_Number] = " & Nz(frmHRS0014s!txt0014ID, 0)), "")

But you may need this:

http://www.theaccessweb.com/forms/frm0022.htm

for the form reference.

[pbaldy]

Happy to help!