Query won't sort based on form combo box (1 Viewer)

H

hllary

Registered User.
Local time
Today, 07:37
Joined
Sep 23, 2019
Messages
80
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.

Code: 
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

Gasman

Enthusiastic Amateur
Local time
Today, 15:37
Joined
Sep 21, 2011
Messages
14,321
What happens if you hardocde a value for the combo?
What is actually in the combo.?
 
H

hllary

Registered User.
Local time
Today, 07:37
Joined
Sep 23, 2019
Messages
80
Gasman said:
What happens if you hardocde a value for the combo?
What is actually in the combo.?
Click to expand...
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

Gasman

Enthusiastic Amateur
Local time
Today, 15:37
Joined
Sep 21, 2011
Messages
14,321
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.?
 
H

hllary

Registered User.
Local time
Today, 07:37
Joined
Sep 23, 2019
Messages
80
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

Gasman

Enthusiastic Amateur
Local time
Today, 15:37
Joined
Sep 21, 2011
Messages
14,321
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

Gasman

Enthusiastic Amateur
Local time
Today, 15:37
Joined
Sep 21, 2011
Messages
14,321
Possibly try

Code: 
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] & "*"));
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 15:37
Joined
Feb 19, 2013
Messages
16,619
The query works when it is filtered by the text box but it does not work when I use the combo box.
Click to expand...
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.
 
H

hllary

Registered User.
Local time
Today, 07:37
Joined
Sep 23, 2019
Messages
80
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.


Gasman said:
Possibly try

Code: 
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] & "*"));
Click to expand...
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 15:37
Joined
Feb 19, 2013
Messages
16,619
the selection is not filtered
Click to expand...
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
 
H

hllary

Registered User.
Local time
Today, 07:37
Joined
Sep 23, 2019
Messages
80
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.
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 15:37
Joined
Feb 19, 2013
Messages
16,619
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

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:37
Joined
Feb 28, 2001
Messages
27,195
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.
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 15:37
Joined
Feb 19, 2013
Messages
16,619
and parentheses;)

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

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:37
Joined
Feb 28, 2001
Messages
27,195
Having looked at that mess long enough, I took the liberty of cleaning it up. This is your cleaned SQL code.

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.

docs.microsoft.com

ComboBox.ListIndex property (Access)

Office VBA reference topic
docs.microsoft.com
docs.microsoft.com

ComboBox.BoundColumn property (Access)

Office VBA reference topic
docs.microsoft.com
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

MajP

You've got your good things, and you've got mine.
Local time
Today, 10:37
Joined
May 21, 2018
Messages
8,542
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] ));

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

MajP

You've got your good things, and you've got mine.
Local time
Today, 10:37
Joined
May 21, 2018
Messages
8,542
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.
 

Attachments

  • FuzzyFind5.zip
    605.4 KB · Views: 105
The_Doc_Man

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:37
Joined
Feb 28, 2001
Messages
27,195
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.
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 15:37
Joined
Feb 19, 2013
Messages
16,619
Sorry guys - you don't need the singles quotes if this is written in the sql window, only if written in VBA
 
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 10:37
Joined
May 21, 2018
Messages
8,542
Sorry guys - you don't need the singles quotes if this is written in the sql window, only if written in VBA
Click to expand...
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.
 
You must log in or register to reply here.

Similar threads

M
Sorting a continuous form based on a field
Replies
18
Views
763
Pat Hartman
P
Z
Solved Filtering a query based on a checkbox in a different form
Replies
11
Views
707
ebs17
E
T
VBA to open specific form/report based on combo box selection
Replies
5
Views
639
TB11
T
H
Solved Query based on combo box
Replies
5
Views
908
HealthyB1
H
T
"Cascading" form/combo boxes
Replies
4
Views
888
tmyers
T

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom