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.

[trey3143]

I tried the code and changed a little syntax to run it. After I ran it it pulled up parameter boxes, so I input them with the data to test to see if it would pull out any data. I made all equal without leaving any blank to test it before I left a field blank. I still got the same error saying that it was too complex. I tried earlier in a test DB making the 5 other tables have a common key and puling the data out but even distinctly I was running too large a query to retrieve data. I tried to link all the tables back to the one key userID but with no luck. I am stumped. Maybe a night away from it and I will come in with fresh ideas. I appreciate your help on this issue.

[Seth Schrock]

Lets try to narrow it down. First, try the query without the WHERE clause. If that works (I'm almost positive that it will), then try adding the WHERE clause up to and including line 17. Make sure each field has something in it and then run the query. Let me know what the results are.

[zmbd]

Spaces and dashes in field names can cause issues... even when enclosed within square brackets, (so can reserved words). I've had databases choke on such things with "too complex" errors that were often resolved by renaming fields. (I inherit a lot of broken databases - yea me)

You really should consider removing the spaces in all field names and converting dashes to underscores.

It is still considered best practice not to use anything except alphanumeric characters and the underscore when naming fields, files, directories etc...

[trey3143]

K without the where clause it does work, when data is inputed into the parameter, I took out all the other where clauses except the Iif statement and I got the same error. I am going to try and change up the Iif(isnull statement) to see if I can get it to work without all the other parameters.

[Seth Schrock]

Here is my other option. Try this in the WHERE clause:

WHERE IIF(Not IsNull(Forms![User Profile Query]![User Employee Type]), [tbl-Master List].[User Employee Type] Like Forms![User Profile Query]![User Employee Type], '*')
AND IIF(Not IsNull([Forms]![User Profile Query].[Company]), [tbl-Master List].Company Like [Forms]![User Profile Query].[Company], '*')
AND IIF(Not IsNull([Forms]![User Profile Query].[UserLocation]), [tbl-Master List].UserLocation Like [Forms]![User Profile Query].[UserLocation], '*')
AND IIF(Not IsNull([Forms]![User Profile Query].[UserDepartment]), [tbl-Master List].UserDepartment Like [Forms]![User Profile Query].[UserDepartment], '*')
AND IIF(Not IsNull([tbl-Master List].[User Title]), [tbl-Master List].[User Title] Like [Forms]![User Profile Query].[User Title], '*')

You should be able to copy and paste this directly into your query.

[Seth Schrock]

Oops. I found a mistake in my previous post.

WHERE IIF(Not IsNull(Forms![User Profile Query]![User Employee Type]), [tbl-Master List].[User Employee Type] Like Forms![User Profile Query]![User Employee Type], '*')
AND IIF(Not IsNull([Forms]![User Profile Query].[Company]), [tbl-Master List].Company Like [Forms]![User Profile Query].[Company], '*')
AND IIF(Not IsNull([Forms]![User Profile Query].[UserLocation]), [tbl-Master List].UserLocation Like [Forms]![User Profile Query].[UserLocation], '*')
AND IIF(Not IsNull([Forms]![User Profile Query].[UserDepartment]), [tbl-Master List].UserDepartment Like [Forms]![User Profile Query].[UserDepartment], '*')
AND IIF(Not IsNull([Forms]![User Profile Query].[User Title]), [tbl-Master List].[User Title] Like [Forms]![User Profile Query].[User Title], '*')

[trey3143]

This code works if all parameters are filled in but if I leave one blank it returns no data. I tried to leave user employee type blank and it did not error this time but it returned no data. I am going to test to see if it returns people that have null values in these fields.

[trey3143]

I think you might have it right, i will let you know as soon as i get through testing

[trey3143]

It works, thanks you so much for your help, the only reason I didn't think it worked that last time was because there is another query that counts and pulls out data equal to another table but I think I can get that one going. I really appreciate your help you are a life saver.

[Seth Schrock]

Not a problem. Glad I was finally able to get it to work for you. Please select the post that best helped you and select it as best answer.