drop down combo box (1 Viewer)

rainbows

Registered User.
Local time
Today, 12:00
Joined
Apr 21, 2017
Messages
425
i have a combo box that i use to select the criteria ( say 10 possibilities ) that are selected from my status field in my query for my report , is it possible to run the same query when it would just list all the item except for the ones where the status is "completed"

thanks steve
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 20:00
Joined
Jul 9, 2003
Messages
16,282
I think you need to provide a bit more information!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:00
Joined
May 21, 2018
Messages
8,529
I agree this question is very unclear and open ended. Please provide an example too, describing what you would choose from the combo and what you would like to see happen.
 

rainbows

Registered User.
Local time
Today, 12:00
Joined
Apr 21, 2017
Messages
425
1687546431910.png

this is the dropdown box that allows me to query any of these items for example if i select returned i get the following . can i get it without using another query and button showing me all the items in the table except for status "completed"

1687546539506.png


1687546965091.png
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:00
Joined
May 21, 2018
Messages
8,529
If I understand. All the selections work except if you choose "Complete".
I am assuming the query have a control parameter
where Status = Forms![Dialog:RMA]![SomeComboName]

I do not know the rowsource for the combo box and where it pulls the data. If for example it is a value list and you typed in Complete then you could have a spelling issue that you cannot see. Example is
CompIete vs Complete (These look the same but the first is a Capital "i" and not a small "L"). Sometimes you could have space " Complete" vs "Complete". Either case it looks as if you should match, but they are not the same.

Can you provide the SQL for your query and the SQL for the rowsource?

The other thing that could cause this is that you are using table lookups and that could cause all kinds of problems. I would not be able to tell you a fix without looking at the DB.

If you are
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:00
Joined
Feb 19, 2002
Messages
43,279
can i get it without using another query and button showing me all the items in the table except for status "completed"
No. Why?
Your current criteria is:
Where Status = Forms!yourform!cboStatus

The criteria to get all except "complete" would be
either:
Where Status <> Forms!yourform!cboStatus --- assuming you want to be able to select any value for <>
OR
Where Status <> "Complete" --- if you want to hardcode the value

As you can see, the "not" solution uses a different relational operator. Although queries take parameters, the parameter only replaces a data value as you can see in the two examples where the Forms! reference fetches a value which in the third example is hardcoded (a literal value in quotes). The relational operator is fixed. Unless you build the SQL on the fly, there is no way to change it in a saved querydef.

PS - always be conscious that when you are using not in the relational operator, null values will not be excluded. So, in this case, if the Status is required, you won't have a problem with <> "Complete". However, if Status is not required and so some rows might be null, the rows with nulls will NOT be returned because of the way null compares work

Where Status <> Null - will always return false (ie no rows)
Where Status = Null - will also always return false (ie no rows)

To include nulls in the set of rows with Status <> "Complete", you would need to explicitly check for nulls:

Where Status <> "Complete" OR Status Is Null
 
Last edited:

Users who are viewing this thread

Top Bottom