Query won't sort based on form combo box

[hllary]

I have a query that uses a text box and a combo box from a form to filter the data. I'm using the query to export the data to an excel.

The query works when it is filtered by the text box but it does not work when I use the combo box.

SELECT SearchAll_qry.ID, SearchAll_qry.[Alpha Designator], SearchAll_qry.ICP, SearchAll_qry.[Cage Code], SearchAll_qry.[PART NUMBER], SearchAll_qry.NSN, 
SearchAll_qry.NIIN, SearchAll_qry.NOMENCLATURE, SearchAll_qry.FIG, SearchAll_qry.ITEM, SearchAll_qry.QTY, SearchAll_qry.FY14AMDFPRICE, SearchAll_qry.Unit, 
SearchAll_qry.EXTCost, SearchAll_qry.[QTY TO ORDER (12 Vehicles)], SearchAll_qry.[TOTAL COST (12 Vehicles)], SearchAll_qry.[Source of Fig/Item Numbers], SearchAll_qry.
[Source of Part], SearchAll_qry.Tab
FROM SearchAll_qry
WHERE (((SearchAll_qry.[Alpha Designator]) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) OR (((SearchAll_qry.ICP) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) 
OR (((SearchAll_qry.[Cage Code]) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) OR (((SearchAll_qry.[PART NUMBER]) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) 
OR (((SearchAll_qry.NSN) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) OR (((SearchAll_qry.NIIN) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) 
OR (((SearchAll_qry.NOMENCLATURE) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) OR (((SearchAll_qry.FIG) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) 
OR (((SearchAll_qry.ITEM) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) OR (((SearchAll_qry.QTY) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) 
OR (((SearchAll_qry.FY14AMDFPRICE) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) OR (((SearchAll_qry.Unit) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) 
OR (((SearchAll_qry.EXTCost) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) OR (((SearchAll_qry.[QTY TO ORDER (12 Vehicles)]) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) 
OR (((SearchAll_qry.[TOTAL COST (12 Vehicles)]) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) 
OR (((SearchAll_qry.[Source of Fig/Item Numbers]) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) OR (((SearchAll_qry.[Source of Part]) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) 
OR (((SearchAll_qry.Tab) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) OR (((SearchAll_qry.[Source of Fig/Item Numbers])=[Forms]![SearchMulti_frm]![CboSource]));

 

[Gasman]

What happens if you hardocde a value for the combo?
What is actually in the combo.?

 

[hllary]

What happens if you hardocde a value for the combo?
What is actually in the combo.?

I have not tried hardcoding the values, since the values will change with the data. The values in the combo box are text (there are some numbers but they always follow letters).

 

[Gasman]

I realise that, but was suggesting some steps to attempt to debug the problem.?

My thoughts are if you can search succesfully with a value that *should* be in cbosource by hardcoding it, look to see what cbosource actually is.?
Are you using lookup fields in a table.? That could be one reason.?

You need to break your problem down into smaler steps to identify the problem.

What happens if you just use the cbosource to search on, nothing else.?

 

[hllary]

Sorry for being a pain. This seems too small of a problem to have. when I do a query just based on the combo box it works. But it does not work when I hard code the combo box. I even tried moving the location of the combo box in the WHERE statement.

 

[Gasman]

Well that proves the contents of the combo will work?

I'd put all that back into design mode in a new query, with the form open and try from there.?

Next thing might to put it at the start instead of the end.

I laways hate the multiple parentheses Access uses (can't see the wood for the trees ), so I would revert back to the design mode to get all the syntax correct and then working.

I cannot see the error in your SQL, but that is not to say there is not any.?

Alternatively upload a cutdown version of your DB if possible, but I only have 2007 and continually am unable to open later version Dbs. Someone else could look though.

 

[Gasman]

Possibly try

SELECT SearchAll_qry.ID, SearchAll_qry.[Alpha Designator], SearchAll_qry.ICP, SearchAll_qry.[Cage Code], SearchAll_qry.[PART NUMBER], SearchAll_qry.NSN,
SearchAll_qry.NIIN, SearchAll_qry.NOMENCLATURE, SearchAll_qry.FIG, SearchAll_qry.ITEM, SearchAll_qry.QTY, SearchAll_qry.FY14AMDFPRICE, SearchAll_qry.Unit,
SearchAll_qry.EXTCost, SearchAll_qry.[QTY TO ORDER (12 Vehicles)], SearchAll_qry.[TOTAL COST (12 Vehicles)], SearchAll_qry.[Source of Fig/Item Numbers], SearchAll_qry.
[Source of Part], SearchAll_qry.Tab
FROM SearchAll_qry
WHERE  (((SearchAll_qry.[Source of Fig/Item Numbers])=[Forms]![SearchMulti_frm]![CboSource]))
OR  (((SearchAll_qry.[Alpha Designator]) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) OR (((SearchAll_qry.ICP) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*"))
OR (((SearchAll_qry.[Cage Code]) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) OR (((SearchAll_qry.[PART NUMBER]) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*"))
OR (((SearchAll_qry.NSN) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) OR (((SearchAll_qry.NIIN) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*"))
OR (((SearchAll_qry.NOMENCLATURE) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) OR (((SearchAll_qry.FIG) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*"))
OR (((SearchAll_qry.ITEM) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) OR (((SearchAll_qry.QTY) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*"))
OR (((SearchAll_qry.FY14AMDFPRICE) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) OR (((SearchAll_qry.Unit) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*"))
OR (((SearchAll_qry.EXTCost) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) OR (((SearchAll_qry.[QTY TO ORDER (12 Vehicles)]) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*"))
OR (((SearchAll_qry.[TOTAL COST (12 Vehicles)]) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*"))
OR (((SearchAll_qry.[Source of Fig/Item Numbers]) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) OR (((SearchAll_qry.[Source of Part]) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*"))
OR (((SearchAll_qry.Tab) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*"));

 

[CJ_London]

The query works when it is filtered by the text box but it does not work when I use the combo box.

just to provide some focus - what does 'does not work' mean? the query errors? returns nothing? returns everything? what sort of values are in the combobox? does the combo have multiple columns?

Also are you leaving the srchtext control blank in your form when you say 'when I use the combo box'? If so then I would expect all records to be returned since 'Like **' will return everything so it does not matter what is in your combo.

 

[hllary]

Gasman- I tried the code and it did not work. Meaning when I have selected an item in the combo box and rerun the query the selection is not filtered. When the combo box value is null and I type something in the text box the query reflects the search.

Possibly try

SELECT SearchAll_qry.ID, SearchAll_qry.[Alpha Designator], SearchAll_qry.ICP, SearchAll_qry.[Cage Code], SearchAll_qry.[PART NUMBER], SearchAll_qry.NSN,
SearchAll_qry.NIIN, SearchAll_qry.NOMENCLATURE, SearchAll_qry.FIG, SearchAll_qry.ITEM, SearchAll_qry.QTY, SearchAll_qry.FY14AMDFPRICE, SearchAll_qry.Unit,
SearchAll_qry.EXTCost, SearchAll_qry.[QTY TO ORDER (12 Vehicles)], SearchAll_qry.[TOTAL COST (12 Vehicles)], SearchAll_qry.[Source of Fig/Item Numbers], SearchAll_qry.
[Source of Part], SearchAll_qry.Tab
FROM SearchAll_qry
WHERE  (((SearchAll_qry.[Source of Fig/Item Numbers])=[Forms]![SearchMulti_frm]![CboSource]))
OR  (((SearchAll_qry.[Alpha Designator]) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) OR (((SearchAll_qry.ICP) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*"))
OR (((SearchAll_qry.[Cage Code]) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) OR (((SearchAll_qry.[PART NUMBER]) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*"))
OR (((SearchAll_qry.NSN) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) OR (((SearchAll_qry.NIIN) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*"))
OR (((SearchAll_qry.NOMENCLATURE) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) OR (((SearchAll_qry.FIG) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*"))
OR (((SearchAll_qry.ITEM) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) OR (((SearchAll_qry.QTY) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*"))
OR (((SearchAll_qry.FY14AMDFPRICE) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) OR (((SearchAll_qry.Unit) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*"))
OR (((SearchAll_qry.EXTCost) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) OR (((SearchAll_qry.[QTY TO ORDER (12 Vehicles)]) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*"))
OR (((SearchAll_qry.[TOTAL COST (12 Vehicles)]) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*"))
OR (((SearchAll_qry.[Source of Fig/Item Numbers]) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) OR (((SearchAll_qry.[Source of Part]) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*"))
OR (((SearchAll_qry.Tab) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*"));

 

[CJ_London]

the selection is not filtered

see post #8

you need clarify your actual requirement for searching

e.g. find searchtext in one or more fields OR use combo (which is what you seem to be describing). Or perhaps find searchtext in one or more fields AND use combo

 

[hllary]

It feels like I'm trying to do too much with the search and I think the solution is I have to have a the search with the text box on one form and the combo box on another form.

Thank you for your help.

 

[CJ_London]

you only need the one query - you just need to be clear about what you want returned

If it is either/or (either use the text box OR the combo) then you would use this

FROM SearchAll_qry
WHERE (nz([Forms]![SearchMulti_frm]![SrchText],"")<>"" AND (((SearchAll_qry.[Alpha Designator]) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) OR (((SearchAll_qry.ICP) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*"))
OR (((SearchAll_qry.[Cage Code]) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) OR (((SearchAll_qry.[PART NUMBER]) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*"))
OR (((SearchAll_qry.NSN) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) OR (((SearchAll_qry.NIIN) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*"))
OR (((SearchAll_qry.NOMENCLATURE) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) OR (((SearchAll_qry.FIG) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*"))
OR (((SearchAll_qry.ITEM) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) OR (((SearchAll_qry.QTY) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*"))
OR (((SearchAll_qry.FY14AMDFPRICE) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) OR (((SearchAll_qry.Unit) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*"))
OR (((SearchAll_qry.EXTCost) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) OR (((SearchAll_qry.[QTY TO ORDER (12 Vehicles)]) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*"))
OR (((SearchAll_qry.[TOTAL COST (12 Vehicles)]) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*"))
OR (((SearchAll_qry.[Source of Fig/Item Numbers]) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")) OR (((SearchAll_qry.[Source of Part]) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*"))
OR (((SearchAll_qry.Tab) Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*")))

OR (((SearchAll_qry.[Source of Fig/Item Numbers])=[Forms]![SearchMulti_frm]![CboSource]));

 

[The_Doc_Man]

One thing you can do to save your sanity and eyesight is to recognize that you have only one record source in the FROM clause. Therefore, ALL of your SearchAll_qry. prefixes can be omitted. SQL rules say that if there is only one source, everything you name must either come from that source, be a constant, or be a fully qualified reference. So your fully qualified [Forms]!... references will work OK. You don't have many constants anyway, so that is not an issue. But you can save a ton of typing by dropping the prefixes that come from your FROM clause source. And it will be easier to read and to debug.

 

[CJ_London]

and parentheses

In this instance, only four required (plus two for the nz function) rather than 120+

 

[The_Doc_Man]

Having looked at that mess long enough, I took the liberty of cleaning it up. This is your cleaned SQL code.

SELECT
    ID,
    [Alpha Designator],
    ICP,
    [Cage Code],
    [PART NUMBER],
    NSN,
    NIIN,
    NOMENCLATURE,
    FIG,
    ITEM,
    QTY,
    FY14AMDFPRICE,
    Unit,
    EXTCost,
    [QTY TO ORDER (12 Vehicles)],
    [TOTAL COST (12 Vehicles)],
    [Source of Fig/Item Numbers],
    [Source of Part],
    Tab
FROM
    SearchAll_qry
WHERE
    ( [Alpha Designator] Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*" ) OR
    ( ICP Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*" ) OR
    ( [Cage Code] Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*" ) OR
    ( [PART NUMBER] Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*" ) OR
    ( NSN Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*" ) OR
    ( NIIN Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*" ) OR
    ( NOMENCLATURE Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*" ) OR
    ( FIG Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*" ) OR
    ( ITEM Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*" ) OR
    ( QTY Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*" ) OR
    ( FY14AMDFPRICE Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*" ) OR
    ( Unit Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*" ) OR
    ( EXTCost Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*" ) OR
    ( [QTY TO ORDER (12 Vehicles)] Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*" ) OR
    ( [TOTAL COST (12 Vehicles)] Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*" ) OR
    ( [Source of Fig/Item Numbers] Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*" ) OR
    ( [Source of Part] Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*" ) OR
    ( Tab Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*" ) OR
    ( [Source of Fig/Item Numbers] = [Forms]![SearchMulti_frm]![CboSource] ) ;

So now comes the question: What is the bound column of CboSource? Remember this: The default property of a text box is .VALUE, which is why you don't need to specify it. But the default value of a combo box involves both a selected row AND a column number. Further, combo box columns number starting from 0. The value of the combo box is selected by the .BoundColumn property and the row number. For the case of a single-select combo box, that would be the row selected by the .ListIndex property. Therefore, your combo reference might need to look more like this:

[Forms]![SearchMulti_frm]![CboSource].Column( [Forms]![SearchMulti_frm]![CboSource].ListIndex, n ) - where n is the correct column number for what you wanted to see. If and ONLY IF the bound column actually contains what you wanted to see, your reference is correct. Otherwise it is not. Here are some links to explain the details of the properties I've referenced.

ComboBox.ListIndex property (Access)

Office VBA reference topic

docs.microsoft.com

ComboBox.BoundColumn property (Access)

Office VBA reference topic

docs.microsoft.com

ComboBox.Column property (Access)

Office VBA reference topic

docs.microsoft.com

You mentioned EXCEL being involved, so I figure that is why you have some really ugly column names. If you are only using the query for spreadsheet analysis, that is OK, but if you are going to propagate those names throughout the rest of your DB, you might eventually get tired of typing so much. There are ways to only use the "ugly" names when exporting to Excel, and to leave shorter names for internal use if that interests you. But that is clearly a problem for a later time.

 

[MajP]

SELECT id,
       [alpha designator],
       icp,
       [cage code],
       [part number],
       nsn,
       niin,
       nomenclature,
       fig,
       item,
       qty,
       fy14amdfprice,
       unit,
       extcost,
       [qty to order (12 vehicles)],
       [total cost (12 vehicles)],
       [source of fig/item numbers],
        [source of part],
       tab
FROM   searchall_qry
WHERE  (( ( [alpha designator] ) LIKE
                  "*" & [forms] ! [searchmulti_frm] ! [srchtext] & "*" ))
        OR (( ( icp ) LIKE "*" & [forms] ! [searchmulti_frm] !
                                         [srchtext]
                                         &
                                             "*" ))
        OR (( ( [cage code] ) LIKE
              "*" & [forms] ! [searchmulti_frm] !
                   [srchtext] & "*" ))
        OR (( ( [part number] ) LIKE
                   "*" & [forms] ! [searchmulti_frm] ! [srchtext] & "*" ))
        OR (( ( nsn ) LIKE "*" & [forms] ! [searchmulti_frm] !
                                         [srchtext]
                                         &
                                             "*" ))
        OR (( ( niin ) LIKE "*" & [forms] ! [searchmulti_frm] !
                                          [srchtext] &
                                              "*" ))
        OR (( ( nomenclature ) LIKE
              "*" & [forms] ! [searchmulti_frm] !
                   [srchtext] & "*" ))
        OR (( ( fig ) LIKE "*" & [forms] ! [searchmulti_frm] !
                                         [srchtext]
                                         &
                                             "*" ))
        OR (( ( item ) LIKE "*" & [forms] ! [searchmulti_frm] !
                                          [srchtext] &
                                              "*" ))
        OR (( ( qty ) LIKE "*" & [forms] ! [searchmulti_frm] !
                                         [srchtext]
                                         &
                                             "*" ))
        OR (( ( fy14amdfprice ) LIKE
                   "*" & [forms] ! [searchmulti_frm] ! [srchtext] & "*" ))
        OR (( ( unit ) LIKE "*" & [forms] ! [searchmulti_frm] !
                                          [srchtext] &
                                              "*" ))
        OR (( ( extcost ) LIKE "*" & [forms] ! [searchmulti_frm] !
                                             [srchtext]
                                                 &
                                                  "*" ))
        OR (( ( [qty to order (12 vehicles)] ) LIKE
                   "*" & [forms] ! [searchmulti_frm] ! [srchtext] & "*" ))
        OR (( ( [total cost (12 vehicles)] ) LIKE
                   "*" & [forms] ! [searchmulti_frm] ! [srchtext] & "*" ))
        OR (( ( [source of fig/item numbers] ) LIKE
                   "*" & [forms] ! [searchmulti_frm] ! [srchtext] & "*" ))
        OR (( ( [source of part] ) LIKE
                   "*" & [forms] ! [searchmulti_frm] ! [srchtext] & "*" ))
        OR (( ( tab ) LIKE "*" & [forms] ! [searchmulti_frm] !
                                         [srchtext]
                                         &
                                             "*" ))
        OR (( ( [source of fig/item numbers] ) =
                   [forms] ! [searchmulti_frm] ! [cbosource] ));

I am not sure how this could work with a textbox. Not a single like is properly formatted like Like '*SomeText*'

Instant SQL Formatter & Beautifier Online

Free online sql formatting tool, beautify sql code instantly for SQL Server, Oracle, DB2, MySQL, Sybase, Access and MDX

www.dpriver.com

 

[MajP]

However if you want a far simpler way to do it. It requires one line of code to sort any number of fields. I think there are several thousands records and you can find a solution in seconds.

 

[The_Doc_Man]

Good catch, MajP. Took me a while to reformat that mess.

@Hilary, what you wrote as (for example)

( Tab Like "*" & [Forms]![SearchMulti_frm]![SrchText] & "*" ) OR

needs to look like

( Tab Like "'*" & [Forms]![SearchMulti_frm]![SrchText] & "*'" ) OR

(Look for the red apostrophes.) You have 14 lines where this might require insertion of the apostrophes.

 

[CJ_London]

Sorry guys - you don't need the singles quotes if this is written in the sql window, only if written in VBA

 

[MajP]

Sorry guys - you don't need the singles quotes if this is written in the sql window, only if written in VBA

Oh yeah. I guess I would have killed my self long ago if I had to write that in sql. I may be an outlier, but I do not think I have ever written a query with a control reference in it, ever. To me It has to be the hardest possible way to check and validate and provides limited flexibility.