Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2012
    Posts
    3

    Unanswered: Using a parameter in query from an unbound field

    Hi - I have a form that has several unbound fields (which are also combo boxes). In a query, I want to reference the unbound field(s) as parameters. However, if the unbound field is blank I don't want the parameter to take effect.

    Let's say one of the fields on the form is called 'Employee'.

    In the query, I refernce the field in the criteria as follows:

    [Forms]![frmReporting]![Employee]

    That works great when there is something in the Employee field. But when that field is empty, the query returns no records. What I want is that when the field is empty, the query should return all records (as if I never had any criteria at all).

    I've tried variations of the following with no success:

    IIf(IsNull([Forms]![frmReporting]![Employee]),"",[Forms]![frmReporting]![Employee])

    Any ideas?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Use the Like comparison operator and this expression:
    Code:
    IIf(IsNull([Forms]![frmReporting]![Employee]),"*",[Forms]![frmReporting]![Employee])
    Have a nice day!

  3. #3
    Join Date
    Mar 2012
    Posts
    3
    Hi - I tried adding the asterisk to my expression - "*". That didn't work (empty recordset).

    I don't understand the comment about the 'like' comparison. I tried putting it in as:

    like "*"

    but that didn't work either. Still returned an empty recordset. I didn't know where else to put the 'like' but in front of the "*"...

    Thanks

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What's the full SQL statement?
    Have a nice day!

  5. #5
    Join Date
    Mar 2012
    Posts
    3
    Code:
    SELECT TimeTracking.TimeTrackingID, TimeTracking.PayPeriodID, lkpPayPeriods.StartDate, lkpPayPeriods.EndDate, lkpPayPeriods.PayDate, lkpPayPeriods.TimeSheetDeadline, Employees.EmployeeName, TimeTracking.EmployeeReimbursementDate, TimeTracking.Notes, TimeTrackingDetail.WorkDate, Projects.ProjectID, Projects.ProjectName, TimeTrackingDetail.WorkHours, lkpWorkCategories.WorkCategory, lkpWorkCategories.WorkCategoryID, lkpWorkCategories.WorkCategoryDescription, lkpWorkCategories.ProjectTime, TimeTrackingDetail.WorkDescription, Clients.ClientID, Clients.CompanyName
    FROM (lkpPayPeriods INNER JOIN (Employees INNER JOIN TimeTracking ON Employees.EmployeeID = TimeTracking.EmployeeID) ON lkpPayPeriods.PayPeriodID = TimeTracking.PayPeriodID) INNER JOIN ((Clients INNER JOIN Projects ON Clients.ClientID = Projects.ClientID) INNER JOIN (lkpWorkCategories INNER JOIN TimeTrackingDetail ON lkpWorkCategories.WorkCategoryID = TimeTrackingDetail.WorkCategoryID) ON Projects.ProjectID = TimeTrackingDetail.ProjectID) ON TimeTracking.TimeTrackingID = TimeTrackingDetail.TimeTrackingID
    WHERE (((Employees.EmployeeName)=IIf(IsNull([Forms]![frmReporting]![EmployeeSelection]),([Employees].[EmployeeName]) Like "*",[Forms]![frmReporting]![EmployeeSelection])));

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try:
    Code:
    WHERE (((Employees.EmployeeName) Like IIf(IsNull([forms]![frmReporting]![EmployeeSelection]),"*",[forms]![frmReporting]![EmployeeSelection])));
    Have a nice day!

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
  •