Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-26-2019, 11:51 AM   #16
jimbo92
Newly Registered User
 
Join Date: Oct 2019
Posts: 7
Thanks: 4
Thanked 0 Times in 0 Posts
jimbo92 is on a distinguished road
Re: Need "Select All Option" in Combobox for Query Parameters

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

jimbo92 is offline   Reply With Quote
Old 10-26-2019, 12:43 PM   #17
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,247
Thanks: 115
Thanked 3,075 Times in 2,793 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Need "Select All Option" in Combobox for Query Parameters

I think there is a missing right bracket at the end of the previous line
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 10-26-2019, 01:12 PM   #18
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,358
Thanks: 554
Thanked 946 Times in 895 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Need "Select All Option" in Combobox for Query Parameters

'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;

__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is online now   Reply With Quote
Old 10-26-2019, 01:24 PM   #19
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,247
Thanks: 115
Thanked 3,075 Times in 2,793 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Need "Select All Option" in Combobox for Query Parameters

And you can get rid of superfluous bracketing added by Access
Code:
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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 10-26-2019, 02:00 PM   #20
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,690
Thanks: 93
Thanked 1,703 Times in 1,576 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Need "Select All Option" in Combobox for Query Parameters

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 10-26-2019, 04:14 PM   #21
jimbo92
Newly Registered User
 
Join Date: Oct 2019
Posts: 7
Thanks: 4
Thanked 0 Times in 0 Posts
jimbo92 is on a distinguished road
Re: Need "Select All Option" in Combobox for Query Parameters

Quote:
Originally Posted by arnelgp View Post
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!
jimbo92 is offline   Reply With Quote
Old 10-27-2019, 02:27 AM   #22
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,358
Thanks: 554
Thanked 946 Times in 895 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Need "Select All Option" in Combobox for Query Parameters

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


__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is online now   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
select query return "text" instead of "boolean value" alain.roger Queries 1 03-26-2015 12:21 PM
Changing How Combobox Functions Based on Form's Editing Mode? ("Add" vs. "Edit") pitt_ph Forms 6 09-25-2012 08:07 PM
Adding "All" to a SELECT DISTINCT combobox hockey8837 Forms 16 01-13-2011 12:50 PM
possible to add a "select none" option to a combobox drop menu?? killyridols Forms 1 08-05-2009 10:39 AM
How to Select "Null" in combobox filter? CrystalSurfer Forms 2 11-16-2006 09:49 AM




All times are GMT -8. The time now is 01:39 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World