Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2010
    Posts
    15

    Unanswered: Returning Null Values and Blank Criteria in Form from Query

    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
    Code:
    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));

  2. #2
    Join Date
    Jan 2010
    Posts
    15
    I changed the query to work but when I try and leave a form field blank i get an access error saying that it is either incorrect or it is too complex to be evaluated. If all the fields are filled out it will run.

    Here are my changes
    Code:
    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]));

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •