is there any way to filter multiple fields in query instead of the every field code

[ZKHADI]

hy buddies!

check below image i put the code in every column of query for multiple search.
this look not so good but it working. is there any short code to filter every field in query.

 

[The_Doc_Man]

Short code? No, not in Access. That is definitely a "long code" event. What you are doing in that query grid is filling in a virtual "table" that Access will use to build SQL to test every field. You have to specify each field for which you want SQL to apply this filter, whatever it is.

You CAN write a loop to build the SQL based on the structure of a table or query.

Dim xFld as Access.Field
Dim strSQLSel as String, Dim strSQLWhere as String, Dim strField as String

strSQLSel = "SELECT "
strSQLWhere = "WHERE "
For Each xFld In StudentDetailsTable.Fields
    strField = xFld.Name
    strSQLSel = strSQLSel & "[" & xFld & "], "
    strSQLWhere = strSQLWhere & "[" & xFld & "] LIKE '*" & Forms!StudentRecord!txtSearch & "*' AND "
Next xctl
strSQLSel = Left( strSQLSel, Len(strSQLSel) - 2 )
strSQLWhere = Left( strSQLWhere, Len( strSQLWhere ) - 4 )
strSQLSel = strSQLSel & " FROM StudentDetailsTable " & strSQLWhere & " ;"
[

The two "Left" functions remove an extra comma and space or "AND" and space from the end of the strings you are building. This will create the SQL for you, after which you have to do something with it.

NOTE that if ANY of those fields is not text, you will have to do something special to avoid a run-time error.

 

[theDBguy]

hy buddies!

check below image i put the code in every column of query for multiple search.
this look not so good but it working. is there any short code to filter every field in query.

Hmm, maybe. You could try adding a calculated column where you concatenate all the columns with an unusual (read: non-typical) delimiter and then just apply your criteria, with the same delimiters, just to that calculated column.

 

[ZKHADI]

Short code? No, not in Access. That is definitely a "long code" event. What you are doing in that query grid is filling in a virtual "table" that Access will use to build SQL to test every field. You have to specify each field for which you want SQL to apply this filter, whatever it is.

You CAN write a loop to build the SQL based on the structure of a table or query.

Dim xFld as Access.Field
Dim strSQLSel as String, Dim strSQLWhere as String, Dim strField as String

strSQLSel = "SELECT "
strSQLWhere = "WHERE "
For Each xFld In StudentDetailsTable.Fields
    strField = xFld.Name
    strSQLSel = strSQLSel & "[" & xFld & "], "
    strSQLWhere = strSQLWhere & "[" & xFld & "] LIKE '*" & Forms!StudentRecord!txtSearch & "*' AND "
Next xctl
strSQLSel = Left( strSQLSel, Len(strSQLSel) - 2 )
strSQLWhere = Left( strSQLWhere, Len( strSQLWhere ) - 4 )
strSQLSel = strSQLSel & " FROM StudentDetailsTable " & strSQLWhere & " ;"
[

The two "Left" functions remove an extra comma and space or "AND" and space from the end of the strings you are building. This will create the SQL for you, after which you have to do something with it.

NOTE that if ANY of those fields is not text, you will have to do something special to avoid a run-time error.

Something error......

 

[Gasman]

No idea as to why it is saying that.
However you would need a space after the SELECT anyway?

Plus if you are going to try and write code like that, at least put a Debug.Print of the final string somehwre to see what you actually have, not what you think you have.

 

[ZKHADI]

No idea as to why it is saying that.
However you would need a space after the SELECT anyway?

Plus if you are going to try and write code like that, at least put a Debug.Print of the final string somehwre to see what you actually have, not what you think you have.

not working

 

[Gasman]

not working

So stop trying to be clever and build it bit by bit. You have not even copied Doc's code correctly?
You are using xfld in the loop, when I would have thought you would be using strField? You need to understand code even when you copy anything offered. SO whilst the xfld is not of your creation, the same logic applies?.

You can always put "SELECT " at the start of the final string.
I doubt that code is going to work anyway, as I would have thought you would need OR ? and remove the last one ?

Also post the code within code tags. That way someone can copy and paste and try it for themselves in someway.

 

[ZKHADI]

So stop trying to be clever and build it bit by bit. You have not even copied Doc's code correctly?
You are using xfld in the loop, when I would have thought you would be using strField? You need to understand code even when you copy anything offered. SO whilst the xfld is not of your creation, the same logic applies?.

You can always put "SELECT " at the start of the final string.
I doubt that code is going to work anyway, as I would have thought you would need OR ? and remove the last one ?

Also post the code within code tags. That way someone can copy and paste and try it for themselves in someway.

if i am clever why i am here. and stop abusing me if you cant help. you all my teachers and I respect all of you. and I copied same code to module just change names of table and form but it stuck on select. even I give space

 

[Gasman]

You did not copy the code Doc supplied though? He has a space after the select and also the where.?
What I am saying is build it bit by bit. That is how I would approach it, putting a debug.print of the sql string within the loop to see what gets built on each pass.
I have no idea why it objects to that string, but you can get past that by just prefixing the final string, but to do that you still have to understand the code? You can spend time trying to find out why a simple string gives an error, or move on and do it another way. I would like to know why it complains, but only after I get the code working and if I had the time to spare.

What you had was perfectly functional, only a problem if you added extra fields to the table, but I would have thought it was very rare to search EVERY field in a table?

 

[KitaYama]

@MajP has a Find as you type class that (if my memory servers me right) can search all fields of a form.
You may want to search for "Find As You Type" and test it.
You only need one line to be able to search all fields.

Edit : I had a copy. You don't need all classes. You can only use FindAsYouTypeForm class.
For testing select "Filter Anywhere in string" option and start typing in textbox.

 

[Gasman]

If you look at the code Doc supplied, the Dim line in mine is all red?
xctl does not exist?

I have to go out now to pick up a passenger, but this is *almost* there.
So the SELECT was not at fault, but the line before it?
I could not get Tablename.Fields to be recognised, but I am rushing, need to go out.

Sub testSearch()
Dim xFld As Field, tdf As TableDef
Dim strSQLSel As String, strSQLWhere As String, strField As String

strSQLSel = "SELECT "
strSQLWhere = "WHERE "
Set tdf = CurrentDb.TableDefs("Transactions")
For Each xFld In tdf.Fields
    strField = xFld.Name
    strSQLSel = strSQLSel & "[" & strField & "], "
    Debug.Print "SQL " & strSQLSel
    strSQLWhere = strSQLWhere & "[" & strField & "] LIKE '*" & Forms!StudentRecord!txtSearch & "*' AND "
    Debug.Print "WHERE " & strSQLWhere
Next xFld
strSQLSel = Left(strSQLSel, Len(strSQLSel) - 2)
strSQLWhere = Left(strSQLWhere, Len(strSQLWhere) - 4)
strSQLSel = strSQLSel & " FROM StudentDetailsTable " & strSQLWhere & " ;"
End Sub

You need to get the tdf working, or perhaps someone else can chip in?

 

[Gasman]

Not the most effcient and does not take into account Dates, numeric etc

Sub testSearch()
Dim xFld As Field, tdf As TableDef
Dim strSQLSel As String, strSQLWhere As String, strField As String, strCriteria As String

strSQLSel = "SELECT "
strSQLWhere = "WHERE "
'strCriteria = Forms!StudentRecord!txtSearch
strCriteria = "Test"
For Each tdf In CurrentDb.TableDefs
    If tdf.Name = "Transactions" Then
        For Each xFld In tdf.Fields
            strField = xFld.Name
            strSQLSel = strSQLSel & "[" & strField & "], "
            Debug.Print "SQL " & strSQLSel
            strSQLWhere = strSQLWhere & "[" & strField & "] LIKE '*" & strCriteria & "*' AND "
            Debug.Print "WHERE " & strSQLWhere
        Next xFld
        strSQLSel = Left(strSQLSel, Len(strSQLSel) - 2)
        strSQLWhere = Left(strSQLWhere, Len(strSQLWhere) - 4)
        strSQLSel = strSQLSel & " FROM StudentDetailsTable " & strSQLWhere & " ;"
        Exit For
    End If
Next
End Sub

And the Debug.Print output

SQL SELECT [ID], 
WHERE WHERE [ID] LIKE '*Test*' AND 
SQL SELECT [ID], [TransactionDate], 
WHERE WHERE [ID] LIKE '*Test*' AND [TransactionDate] LIKE '*Test*' AND 
SQL SELECT [ID], [TransactionDate], [Account], 
WHERE WHERE [ID] LIKE '*Test*' AND [TransactionDate] LIKE '*Test*' AND [Account] LIKE '*Test*' AND 
SQL SELECT [ID], [TransactionDate], [Account], [Num], 
WHERE WHERE [ID] LIKE '*Test*' AND [TransactionDate] LIKE '*Test*' AND [Account] LIKE '*Test*' AND [Num] LIKE '*Test*' AND 
SQL SELECT [ID], [TransactionDate], [Account], [Num], [Description], 
WHERE WHERE [ID] LIKE '*Test*' AND [TransactionDate] LIKE '*Test*' AND [Account] LIKE '*Test*' AND [Num] LIKE '*Test*' AND [Description] LIKE '*Test*' AND 
SQL SELECT [ID], [TransactionDate], [Account], [Num], [Description], [Memo], 
WHERE WHERE [ID] LIKE '*Test*' AND [TransactionDate] LIKE '*Test*' AND [Account] LIKE '*Test*' AND [Num] LIKE '*Test*' AND [Description] LIKE '*Test*' AND [Memo] LIKE '*Test*' AND 
SQL SELECT [ID], [TransactionDate], [Account], [Num], [Description], [Memo], [Category], 
WHERE WHERE [ID] LIKE '*Test*' AND [TransactionDate] LIKE '*Test*' AND [Account] LIKE '*Test*' AND [Num] LIKE '*Test*' AND [Description] LIKE '*Test*' AND [Memo] LIKE '*Test*' AND [Category] LIKE '*Test*' AND 
SQL SELECT [ID], [TransactionDate], [Account], [Num], [Description], [Memo], [Category], [Clr], 
WHERE WHERE [ID] LIKE '*Test*' AND [TransactionDate] LIKE '*Test*' AND [Account] LIKE '*Test*' AND [Num] LIKE '*Test*' AND [Description] LIKE '*Test*' AND [Memo] LIKE '*Test*' AND [Category] LIKE '*Test*' AND [Clr] LIKE '*Test*' AND 
SQL SELECT [ID], [TransactionDate], [Account], [Num], [Description], [Memo], [Category], [Clr], [Amount], 
WHERE WHERE [ID] LIKE '*Test*' AND [TransactionDate] LIKE '*Test*' AND [Account] LIKE '*Test*' AND [Num] LIKE '*Test*' AND [Description] LIKE '*Test*' AND [Memo] LIKE '*Test*' AND [Category] LIKE '*Test*' AND [Clr] LIKE '*Test*' AND [Amount] LIKE '*Test*' AND

 

[KitaYama]

strSQLWhere = strSQLWhere & "[" & strField & "] LIKE '*" & strCriteria & "*' AND "

@Gasman I think OP needs OR not AND.

 

[Gasman]

I have already said that? in post #7
O/P has to do some work himself.

 

[The_Doc_Man]

I got involved in USA's Father's Day activities yesterday so let the keyboard sit idle, which is why I didn't see that this little snippet was becoming a problem.

Simplest first, it probably DOES need OR rather than AND, thus needs to trim only 3 characters from the end of the otherwise finished WHERE line. The SELECT line should be OK. I DID warn that if any of the fields were NOT text fields, you would have a problem and it would REALLY complicate the query. The reason is that LIKE doesn't work on any field that is numeric or that is derived from a numeric datatype (e.g. DATE).

@ZKHADI, that was a rough code snippet that I tossed together on the fly to be placed in a routine to be called at the appropriate time. It CANNOT be stand-alone code because Access doesn't execute stand-alone code. There is no way to get to it unless you place it where some event code will eventually get to it. I cannot advise you on exactly where to put it because I don't know when or how you intend to perform the query and don't know the context in which it would be used. However, in your project, you would insert that code (with some of the cleanups as suggested by the others here) before you need to run that query.

I don't know if this is going to be a one-off query that will be built once & stored as a querydef or whether you need to build it frequently for some reason. That's the problem with isolated advice in this forum. With no context, we cannot advise as well as we might like. And yet to provide full context takes us WAY too long to try to understand your problem.

At least I think you begin to understand why some of us answered your original question with "no simple way."

 

[KitaYama]

The reason is that LIKE doesn't work on any field that is numeric or that is derived from a numeric datatype (e.g. DATE).

@The_Doc_Man I wonder why this works:

Design view of table :

Data sheet of the table:

Design of query:

the sql of the query :

SELECT tblMachines.MachinePK, tblMachines.MachineName,
       tblMachines.ShowOrder, tblMachines.ClassFK
FROM   tblMachines
WHERE (((tblMachines.ShowOrder) Like '*1*'));

And the result is :

 

[The_Doc_Man]

Since the wise men of Microsoft do not release information about the inner workings of their code, I can only assume that the filtration is taking advantage of the implied ambiguity of all numbers. They could be numeric values but also can be expressed as text strings. However, I cannot tell for sure because of that black-box effect.

I also know that SQL has different rules for mixed-mode expressions when compared against VBA. So why does that work? Beats the heck out of me. But I'll add that it might work in Microsoft SQL and fail in ORACLE SQL and work in SYBASE SQL and fail in INGRES SQL. Or some combination of failures. It's called "implementation dependence" - or "it does what the vendor says it does."

 

[jdraw]

I agree Doc. It might not work on the previous or next release of Access or any update between new releases.