Need "Select All Option" in Combobox for Query Parameters

[jimbo92]

Returns syntax error when I try to save query. Seems to be highlighting ORDER.

[isladogs]

I think there is a missing right bracket at the end of the previous line

[Uncle Gizmo]

'You Can simplify further by removing the Table Name (Only if there is a single Table)
'SELECT [Entry Number], [Account Name], [Employee Name], [Work Date], [Hours Worked], [Pay Per Hour], [Amount Due], Notes
'FROM [Employee Expenses]
'WHERE ((([Employee Name])=IIF([Forms]![Main Menu]![EmployeeEntered]="(All)", [Employee Name], [Forms]![Main Menu]![EmployeeEntered]) AND (([Work Date]) Between [Forms]![Main Menu]![EmpStartDate] And [Forms]![Main Menu]![EmpEndDate]))
'ORDER BY [Employee Name], [Work Date];

'And if you use Names WITHOUT SPACES you can get rid of the Square brackets too!
'SELECT EntryNumber, AccountName, EmployeeName, WorkDate, HoursWorked, PayPerHour, AmountDue, Notes
'FROM EmployeeExpenses
'WHERE (((EmployeeName)=IIF(Forms!MainMenu!EmployeeEntere d="(All)", EmployeeName, Forms!MainMenu!EmployeeEntered) AND ((WorkDate) Between Forms!MainMenu!EmpStartDate And Forms!MainMenu!EmpEndDate))
'ORDER BY EmployeeName, WorkDate;

[isladogs]

And you can get rid of superfluous bracketing added by Access

SELECT EntryNumber, AccountName, EmployeeName, WorkDate, HoursWorked, PayPerHour, AmountDue, Notes
FROM EmployeeExpenses
WHERE EmployeeName=IIf(Forms!MainMenu!EmployeeEntered="(All)", EmployeeName, Forms!MainMenu!EmployeeEntered)
 AND WorkDate Between Forms!MainMenu!EmpStartDate And Forms!MainMenu!EmpEndDate
ORDER BY EmployeeName, WorkDate

[The_Doc_Man]

For future reference, since you have something working now: You had a situation where you would have put Null somewhere - but you can never match anything to Null because Null is never equal to anything INCLUDING another null. (Yes, it is true that Null <> Null in Access). So as a beginner, try to avoid solutions involving nulls. Having two different SQL strings and pick one or the other based on some form selection is probably the easiest to implement and conceptually the easiest to understand. Fancier queries? Not necessary.

[jimbo92]

Originally Posted by arnelgp

can still be simplified:

SELECT [Employee Expenses].[Entry Number], [Employee Expenses].[Account Name], [Employee Expenses].[Employee Name], [Employee Expenses].[Work Date], [Employee Expenses].[Hours Worked], [Employee Expenses].[Pay Per Hour], [Employee Expenses].[Amount Due], [Employee Expenses].Notes
FROM [Employee Expenses]
WHERE ((([Employee Expenses].[Employee Name])=IIF([Forms]![Main Menu]![EmployeeEntered]="(All)", [Employee Expenses].[Employee Name], [Forms]![Main Menu]![EmployeeEntered]) AND (([Employee Expenses].[Work Date]) Between [Forms]![Main Menu]![EmpStartDate] And [Forms]![Main Menu]![EmpEndDate]))
ORDER BY [Employee Expenses].[Employee Name], [Employee Expenses].[Work Date];

Got this working with the extra bracket--and you're right it is cleaner. Uncle Gizmo's code works great too. Thanks for all the help and tips everyone!

[Uncle Gizmo]

There's an easy way to remove the table name. See the tip:-

"Take out the Tables" on my website here:-

https://www.niftyaccess.com/nifty-tips/


it's about the 4th one down.

Sent from Newbury UK