Returning Null Values and Blank Criteria in Form

[trey3143]

Hello,
I am trying to get a query that runs off input values in a form in access 2010. I would like to return null values in fields and if a box is not filled out in the form to ignore this parameter. I whave been trying to do this without creating a union query. But this is what I have so far but it keeps saying I have a syntax error and sometime I have gotten you are missing an operator. I know what those errors mean but I am getting a little stuck at the moment. Please help

SELECT [Tbl-Master List].Group, [Tbl-Master List].User, [Tbl-Master List].[User ID], [tbl-UserEmployeeType].[User Employee Type], [tbl-Company].Company, [tbl-UserLocation].UserLocation, [tbl-UserDepartment].UserDepartment, [tbl-UserTitle].[User Title]
FROM [tbl-UserTitle] INNER JOIN ([tbl-UserDepartment] INNER JOIN ([tbl-UserLocation] INNER JOIN ([tbl-Company] INNER JOIN ([tbl-UserEmployeeType] INNER JOIN [Tbl-Master List] ON [tbl-UserEmployeeType].[User Employee Type] = [Tbl-Master List].[User Employee Type]) ON [tbl-Company].Company = [Tbl-Master List].Company) ON [tbl-UserLocation].UserLocation = [Tbl-Master List].[User Location]) ON [tbl-UserDepartment].UserDepartment = [Tbl-Master List].[User Department]) ON [tbl-UserTitle].[User Title] = [Tbl-Master List].[User Title]
WHERE
(([tbl-UserEmployeeType].[User Employee Type]) Like IIf(IsNull[User Profile Query].[User Employee Type]), _ "*",[Forms]![User Profile Query].[User Employee Type]) AND (([tbl-Company].Company) Like [Forms]![User Profile Query].[Company]) AND (([tbl-UserLocation].UserLocation) Like [Forms]![User Profile Query].[UserLocation]) AND (([tbl-UserDepartment].UserDepartment) Like [Forms]![User Profile Query].[UserDepartment]) AND (([tbl-UserTitle].[User Title]) Like [Forms]![User Profile Query].[User Title])) OR ((([tbl-UserEmployeeType].[User Employee Type]) Is Null) AND (([tbl-Company].Company) Is Null) AND (([tbl-UserLocation].UserLocation) Is Null) AND (([tbl-UserDepartment].UserDepartment) Is Null) AND (([tbl-UserTitle].[User Title]) Is Null));

[Seth Schrock]

For anyone else working on this, here is the code in a nicer format:

SELECT [Tbl-Master List].Group
, [Tbl-Master List].User
, [Tbl-Master List].[User ID]
, [tbl-UserEmployeeType].[User Employee Type]
, [tbl-Company].Company
, [tbl-UserLocation].UserLocation
, [tbl-UserDepartment].UserDepartment
, [tbl-UserTitle].[User Title]
FROM [tbl-UserTitle] 
INNER JOIN ([tbl-UserDepartment] 
INNER JOIN ([tbl-UserLocation] 
INNER JOIN ([tbl-Company] 
INNER JOIN ([tbl-UserEmployeeType] 
INNER JOIN [Tbl-Master List] 
ON [tbl-UserEmployeeType].[User Employee Type] = [Tbl-Master List].[User Employee Type]) 
ON [tbl-Company].Company = [Tbl-Master List].Company) 
ON [tbl-UserLocation].UserLocation = [Tbl-Master List].[User Location]) 
ON [tbl-UserDepartment].UserDepartment = [Tbl-Master List].[User Department]) 
ON [tbl-UserTitle].[User Title] = [Tbl-Master List].[User Title]
WHERE
(([tbl-UserEmployeeType].[User Employee Type]) Like 
	IIf(IsNull[User Profile Query].[User Employee Type])
		, _ "*"
		,[Forms]![User Profile Query].[User Employee Type]) 
AND (([tbl-Company].Company) Like [Forms]![User Profile Query].[Company]) 
AND (([tbl-UserLocation].UserLocation) Like [Forms]![User Profile Query].[UserLocation]) 
AND (([tbl-UserDepartment].UserDepartment) Like [Forms]![User Profile Query].[UserDepartment]) 
AND (([tbl-UserTitle].[User Title]) Like [Forms]![User Profile Query].[User Title])) 
OR ((([tbl-UserEmployeeType].[User Employee Type]) Is Null) 
AND (([tbl-Company].Company) Is Null) 
AND (([tbl-UserLocation].UserLocation) Is Null) 
AND (([tbl-UserDepartment].UserDepartment) Is Null) 
AND (([tbl-UserTitle].[User Title]) Is Null));

Trey, I am working on this, but don't have an answer yet.

[Seth Schrock]

Trey, on line 22 of the code that I posted, try removing the ending parenthesis at the end of the line: ) See what you get now.

Also, in lines 29 through 33, try making the criteria be

AND (IsNull([tbl-UserEmployeeType].[User Employee Type]))

[trey3143]

K i changed the code in the first where clause to test and it will query now but if I leave a form field blank i get an error that says This is typed incorrectly, or it is too complex to be evaluated. It will run if all form fields are fillled out.

WHERE ((([tbl-UserEmployeeType].[User Employee Type]) Like IIf(IsNull([Forms]![User Profile Query].[UserEmployeeType]),"_"*"",[Forms]![User Profile Query].[UserEmployeeType])) AND (([tbl-Company].Company) Like [Forms]![User Profile Query].[Company]) AND (([tbl-UserLocation].UserLocation) Like [Forms]![User Profile Query].[UserLocation]) AND (([tbl-UserDepartment].UserDepartment) Like [Forms]![User Profile Query].[UserDepartment]) AND (([tbl-UserTitle].[User Title]) Like [Forms]![User Profile Query].[User Title]));

[Seth Schrock]

Try making the criteria in lines 29 through 33 be:

AND (IsNull([tbl-UserEmployeeType].[User Employee Type]))

I added this to my previous post, but I think that you were probably typing at the same time as me.

[Seth Schrock]

Upon further analysis of your code, I think that your problem may be because your Joins are some of the same fields as your criteria. Thus, if one of your criteria fields are blank, then your Join field is also blank. I'm not an expert at how the Joins work, but I'm guessing that this would cause problems.

[trey3143]

I got the same error about the query being too complex

[Seth Schrock]

What are you trying to do? I might be able to come up with a way to get the functionality that you are looking for without having to do this query.

[trey3143]

So I was thinking about adding a primary key like user id to these tables and making these joins, do you think that would work?

[trey3143]

I am trying to pull data out through a form that runs a query, but I want to be able to leave a form field blank where it will ignore the parameter or this form field

[Seth Schrock]

It is hard to say without seeing your database, but I have found that changing the PK of a table envolves a bunch of work and usually isn't needed if the database was designed correctly to begin with.

What is the purpose of pulling data out through a form? In other words, what does the end user see? End users usually have no idea what a query is or that they are getting data from a query.

[trey3143]

This is going to be used with the IT department to give user provisioning rights. So when a new employee comes in they fill out areas on a form for department, employment status, etc to pull out the access rights for that specific user and the groups they need to be imported into AD. I didn't create this database they came to me after it was built and said this query doesn't work. So I am thinking I might have to make changes to how it was created to make it work correctly. Sometime they have null values in their personell record or need to leave employeement status or something else blank to pull a new employee out.

[Seth Schrock]

Okay. That makes more sense. So I'm guessing that you have a main table (possibly tbl-Master List) that has multiple tables related directly to it (for example, tbl-UserDepartment, tbl-UserLocation, etc.) Is this correct?

[trey3143]

Yes sir, then she designed the form fields drop down box to pull from a query in the smaller tables

[Seth Schrock]

Okay, then you should be able to run the query without all the joins because you'll actually have your criteria matching the values in [tbl-Master List]. Like this:

SELECT [Tbl-Master List].Group
, [Tbl-Master List].User
, [Tbl-Master List].[User ID]
, [tbl-UserEmployeeType].[User Employee Type]
, [tbl-Company].Company
, [tbl-UserLocation].UserLocation
, [tbl-UserDepartment].UserDepartment
, [tbl-UserTitle].[User Title]
FROM [tbl-Master List]
WHERE (([tbl-Master List].[User Employee Type]) Like 
	IIf(IsNull[User Profile Query].[User Employee Type]
		, _ "*"
		,[Forms]![User Profile Query].[User Employee Type]) 
AND (([tbl-Master List].Company) Like [Forms]![User Profile Query].[Company]) 
AND (([tbl-Master List].UserLocation) Like [Forms]![User Profile Query].[UserLocation]) 
AND (([tbl-Master List].UserDepartment) Like [Forms]![User Profile Query].[UserDepartment]) 
AND (([tbl-Master List].[User Title]) Like [Forms]![User Profile Query].[User Title])) 
OR ((([tbl-Master List].[User Employee Type]) Is Null) 
AND (([tbl-Master List].Company) Is Null) 
AND (([tbl-Master List].UserLocation) Is Null) 
AND (([tbl-Master List].UserDepartment) Is Null) 
AND (([tbl-Master List].[User Title]) Is Null));[tbl

If this doesn't work, then I have one more idea. It takes more coding so I'll wait to hear back.