Query Critera -> Text Box

[ASherbuck]

I was playing around today and found something that is odd to me.

If I set my query criteria to "Like [Forms]![FrmName]![Text0]"

it will work as long as it is simple. For example, I'm running the query against a table and my criteria is for the TableID column (1, 2, 3, etc). As long as I use "1" or "2" I'm fine. But for some reason when the text box has "1 Or 2" in it, or "*" (Literally "*") or any of the other logic operations it fails and gives no results. Is there a reason for this? Does it have something to do with the how the query changes in sql ?

 

[rainman89]

you want it

Like "*" & [Forms]![FrmName]![Text0] & "*"

 

[ASherbuck]

no, that's definitely not it.

That would work as I originally said it does, with just "1" or "2" in my criteria. Not when it's "1 Or 2". My question deals specifically with the logic operators in the query criteria. And if I precede and end my criteria with "*" a problem arises when I enter "1" as an option as that will also return "11" and "21" and all the teens . . . anything with a one in it.

I'm sorry I stated my question wrong. I am curious why I cannot include the logic operators "Or" "Not" "Like" "And" in my text boxes and have them used by the query in the criteria. I figure this must have something to do with the SQL Statement structure behind the query.

 

[ajetrumpet]

I am curious why I cannot include the logic operators "Or" "Not" "Like" "And" in my text boxes and have them used by the query in the criteria.

The answer to this is very simple.

operators are used in the query as means to getting to an END, that being a value of some sort (a target source). Operators are not objects, and as such, they are not meant to house anything, like objects do.

You can also think of this as SQL, and built-in objects. You cannot mix the two. I think of SQL as being a facilitator for the engine, while objects really have nothing to do with engine itself. They are just part of the structure of the program that are there for your ease of use and navigation.

I think you're trying to use the different features of Access that are built in, but in the wrong way. Use them for their purpose. It's meant to make things easier for you.

 

[ASherbuck]

Thanks Adam, that's great.

So if I wanted to make a search engine for my database and I wanted to be able to include the ability to use "-" as if it were NOT, I would basically have to parse the text box control accordingly and then build a sql statement off of the parsed line.

Something like

If Instr(TxtSearch, "-") Then
    TxtNotSearch = Split(TxtSearch, "-")(1)
    TxtSearch = Split(TxtSearch, "-")(0)
       If Instr(TxtNotSearch, " ") Then
          TxtSearch = TxtSearch & " " & Split(TxtNotSearch, " ")(1)
          TxtNotSearch = Split(TxtNotSearch, " ")(0)
          DoCmd.RunSQL "SELECT tblBarcodeMain.sBarcode
          FROM tblBarcodeMain
          WHERE (((tblBarcodeMain.sBarcode) Like [Forms]![FrmSearch]![TxtSearch] And Not           (tblBarcodeMain.sBarcode)=[Forms]![FrmSearch]![TxtNOTSearch]));"
    Else
DoCmd.RunSQL "SELECT tblBarcodeMain.sBarcode
FROM tblBarcodeMain
WHERE (((tblBarcodeMain.sBarcode) Like [Forms]![FrmSearch]![TxtSearch]));"

That's really messy and sloppy but basically I build different queries pending the conditions in my search box control. I'm just trying to give users a way of excluding certain words from their searches. Is this something a filter would be better suited for? I never got too far into them.