dbpQuery - Problema con busqueda
Quote from neobook on October 7, 2019, 1:19 amEl problema es el siguiente:
Cuando busco bolsa cemento encuentra la celda, pero si busco cemento bolsa no encuentra nada.
la idea es que busque sin el orden de las palabras.
el codigo que use es el siguiente:
dbpQuery "datos" "Tabla1" "PRODUCTO LIKE [#34]%[value]%[#34]"
El problema es el siguiente:
Cuando busco bolsa cemento encuentra la celda, pero si busco cemento bolsa no encuentra nada.
la idea es que busque sin el orden de las palabras.
el codigo que use es el siguiente:
dbpQuery "datos" "Tabla1" "PRODUCTO LIKE [#34]%[value]%[#34]"
Uploaded files:- You need to login to have access to uploads.
Quote from Gaev on October 7, 2019, 5:13 pm@neobook
dbpQuery "datos" "Tabla1" "PRODUCTO LIKE [#34]%[value]%[#34]"What you are asking to search is for records where 'the phrase in the variable [value] is present somewhere in the beginning, middle or end of the field' ... it does NOT mean 'search for records with the words in the variable [value] being present in any order'.
Try and combine multiple queries (you can replace bolsa and cemento with [value1] and [value2]) ...
1) bolsa OR cemento (in any order; but both must be present) constitutes a match
dbpQuery "datos" "Tabla1" "PRODUCTO LIKE [#34]%bolsa%[#34] AND PRODUCTO LIKE [#34]%cemento%[#34]"2) bolsa OR cemento (any one word) constitutes a match
dbpQuery "datos" "Tabla1" "PRODUCTO LIKE [#34]%bolsa%[#34] OR PRODUCTO LIKE [#34]%cemento%[#34]"3) if you have a phrase with many words (where any one matching word constitutes a match), take a look at ...
dbpQuery "datos" "Tabla1" "PRODUCTO IN ([#34]bolsa[#34],[#34]cemento[#34],[#34]gracias[#34],[#34]amigo[#34])"Warning - I have not verified all suggestions on my computer :-(
dbpQuery "datos" "Tabla1" "PRODUCTO LIKE [#34]%[value]%[#34]"
What you are asking to search is for records where 'the phrase in the variable [value] is present somewhere in the beginning, middle or end of the field' ... it does NOT mean 'search for records with the words in the variable [value] being present in any order'.
Try and combine multiple queries (you can replace bolsa and cemento with [value1] and [value2]) ...
1) bolsa OR cemento (in any order; but both must be present) constitutes a match
dbpQuery "datos" "Tabla1" "PRODUCTO LIKE [#34]%bolsa%[#34] AND PRODUCTO LIKE [#34]%cemento%[#34]"
2) bolsa OR cemento (any one word) constitutes a match
dbpQuery "datos" "Tabla1" "PRODUCTO LIKE [#34]%bolsa%[#34] OR PRODUCTO LIKE [#34]%cemento%[#34]"
3) if you have a phrase with many words (where any one matching word constitutes a match), take a look at ...
dbpQuery "datos" "Tabla1" "PRODUCTO IN ([#34]bolsa[#34],[#34]cemento[#34],[#34]gracias[#34],[#34]amigo[#34])"
Warning - I have not verified all suggestions on my computer :-(
Quote from neobook on October 8, 2019, 12:05 amTengo 500 productos en la tabla, yo di un ejemplo.
Se suele buscar como bolsa de cemento, o cemento en bolsa, asi con otros productos.
No es que solo quiero que busque ese solo.
LA pregunta es :
Si en la variable tengo 3 palabras, si dbpQuery puede buscar sin el orden de las palabras.
Ej:
Pegamento ceramico
Ceramico pegamento.
Por otro lado.
me tiro este error con tu codigo.
Tengo 500 productos en la tabla, yo di un ejemplo.
Se suele buscar como bolsa de cemento, o cemento en bolsa, asi con otros productos.
No es que solo quiero que busque ese solo.
LA pregunta es :
Si en la variable tengo 3 palabras, si dbpQuery puede buscar sin el orden de las palabras.
Ej:
Pegamento ceramico
Ceramico pegamento.
Por otro lado.
me tiro este error con tu codigo.
Uploaded files:- You need to login to have access to uploads.
Quote from Gaev on October 8, 2019, 3:17 am@neobook
me tiro este error con tu codigo.
I throw this error with your code.Looking at the image of your code ...
a) I can not see the full dbpQuery command to see what you have coded ... if you provide the full command you typed in your project, then I can examine what it is about the syntax that is producing the error.
b) Why do you have 2 dbpQuery commands one after the other ? ... the second one will negate the records returned by the first one.
I have edited my previous code to fix syntax errors introduced when forum software does automatic translations.
Si en la variable tengo 3 palabras, si dbpQuery puede buscar sin el orden de las palabras.
If I have 3 words in the variable, if dbpQuery can search without the word order.I addressed that with suggestion (3) in my previous post.
me tiro este error con tu codigo.
I throw this error with your code.
Looking at the image of your code ...
a) I can not see the full dbpQuery command to see what you have coded ... if you provide the full command you typed in your project, then I can examine what it is about the syntax that is producing the error.
b) Why do you have 2 dbpQuery commands one after the other ? ... the second one will negate the records returned by the first one.
I have edited my previous code to fix syntax errors introduced when forum software does automatic translations.
Si en la variable tengo 3 palabras, si dbpQuery puede buscar sin el orden de las palabras.
If I have 3 words in the variable, if dbpQuery can search without the word order.
I addressed that with suggestion (3) in my previous post.
Quote from neobook on October 8, 2019, 5:44 am
If "[value]" "=" ""
AlertBox "BUSCAR PRODUCTO" "INGRESE EL PRODUCTO QUE DESEA BUSCAR"
ELSE
If "[datos.Tabla1.$RecCount]" ">=" "1"
dbpQuery "datos" "Tabla1" "PRODUCTO LIKE [#34]%bolsa%[#34]" AND LIKE [#34]%cemento%[#34]"
EndIfLastError=This expression is missing a quotation mark.
If "[value]" "=" ""
AlertBox "BUSCAR PRODUCTO" "INGRESE EL PRODUCTO QUE DESEA BUSCAR"
ELSE
If "[datos.Tabla1.$RecCount]" ">=" "1"
dbpQuery "datos" "Tabla1" "PRODUCTO LIKE [#34]%bolsa%[#34]" AND LIKE [#34]%cemento%[#34]"
EndIf
LastError=This expression is missing a quotation mark.
Quote from Gaev on October 8, 2019, 3:48 pm@neobook
dbpQuery "datos" "Tabla1" "PRODUCTO LIKE [#34]%bolsa%[#34]" AND LIKE [#34]%cemento%[#34]"
Did you read my last post where I mentioned that I had edited my previous post (some errors introduced with automatic translations) ?
As you can see, there is an extraneous " after %bolsa%[#34] ... and so VisualNEOWin is complaining about the stuff after it (which only has one ending " (double-quotation mark).
Repeating what I corrected in previous post ...
dbpQuery "datos" "Tabla1" "PRODUCTO LIKE [#34]%bolsa%[#34] AND PRODUCTO LIKE [#34]%cemento%[#34]"
... what this is asking for is records where ...
- the PRODUCTO field has word bolsa somewhere (beginning, middle, end) in the content ... PRODUCTO LIKE [#34]%bolsa%[#34]
- and also ... AND
- the PRODUCTO field has word cemento somewhere (beginning, middle, end) in the content ... PRODUCTO LIKE [#34]%cemento%[#34]
... 'both conditions must be met', but 'the relative position of the two words is not important'.
dbpQuery "datos" "Tabla1" "PRODUCTO LIKE [#34]%bolsa%[#34]" AND LIKE [#34]%cemento%[#34]"
Did you read my last post where I mentioned that I had edited my previous post (some errors introduced with automatic translations) ?
As you can see, there is an extraneous " after %bolsa%[#34] ... and so VisualNEOWin is complaining about the stuff after it (which only has one ending " (double-quotation mark).
Repeating what I corrected in previous post ...
dbpQuery "datos" "Tabla1" "PRODUCTO LIKE [#34]%bolsa%[#34] AND PRODUCTO LIKE [#34]%cemento%[#34]"
... what this is asking for is records where ...
- the PRODUCTO field has word bolsa somewhere (beginning, middle, end) in the content ... PRODUCTO LIKE [#34]%bolsa%[#34]
- and also ... AND
- the PRODUCTO field has word cemento somewhere (beginning, middle, end) in the content ... PRODUCTO LIKE [#34]%cemento%[#34]
... 'both conditions must be met', but 'the relative position of the two words is not important'.
Quote from neobook on October 8, 2019, 5:55 pmEstimado amigo, su codigo es muy particular.
solo busca bolsa o cemento.
La pregunta si se fija, es que si se puede buscar por palabras sin importar el orden.
lo del cemento bolsa es solo un ejemplo.
como puede ser: coche casa o casa coche
arbol hoja o hoja arbol
la idea es que busque en la tabla pero sin importar el orden. su busqueda es muy especifica.
vea las imagen cuando busco are el resultado con su codigo esta mal.
en cambio con mi codigo realiza la busqueda con palabras que contengan are.
Estimado amigo, su codigo es muy particular.
solo busca bolsa o cemento.
La pregunta si se fija, es que si se puede buscar por palabras sin importar el orden.
lo del cemento bolsa es solo un ejemplo.
como puede ser: coche casa o casa coche
arbol hoja o hoja arbol
la idea es que busque en la tabla pero sin importar el orden. su busqueda es muy especifica.
vea las imagen cuando busco are el resultado con su codigo esta mal.
en cambio con mi codigo realiza la busqueda con palabras que contengan are.
Uploaded files:- You need to login to have access to uploads.
Quote from neobook on October 8, 2019, 5:58 pmaclaracion:
su codigo bolsa cemento o cemento bolsa lo realiza bien.
pero es muy especifico.
cuando realizo otra busqueda. realiza mal el resultado como se ve en la imagen.
aclaracion:
su codigo bolsa cemento o cemento bolsa lo realiza bien.
pero es muy especifico.
cuando realizo otra busqueda. realiza mal el resultado como se ve en la imagen.
Quote from Gaev on October 8, 2019, 6:47 pm@neobook
Your cement bag code or cement bag performs well.
That is progress
But it is very specific.
When I do another search. misperforms the result as seen in the image.1) it was deliberately specific ... so it was easily understandable
2) as mentioned before, you can make it generic ...
Try and combine multiple queries (you can replace bag and cement with [value1] and [value2]
a) if you are looking for a 'match with 2 words', you can replace bolsa and cemento with [value1] and [value2]
dbpQuery "data" "Table1" "LIKE PRODUCT [#34]%[value1]%[#34] AND LIKE PRODUCT [#34]%[value2]%[#34]"... of course you have to StrParse user input to get the two variables from user's input (or ask user to enter each word in a separate TextEntry Box).
b) if you are looking for a 'match with 3 words', you can add another AND query-condition (using [value3])
c) if you are looking for a 'match with a lot of words', you should look at my 3rd suggestion in an earlier post (instead of LIKE, use IN) ... again, you first have to first setup each word as a seaparate variable value.
Just post here how generic a search facility you want to offer your users ...
- maximum of 2 words ?
- maximum of 3 words ?
- maximum of ???? words ?... the more generic the facility is, the more complex the logic to parse the words and build the query that Access can understand.
Your cement bag code or cement bag performs well.
That is progress
But it is very specific.
When I do another search. misperforms the result as seen in the image.
1) it was deliberately specific ... so it was easily understandable
2) as mentioned before, you can make it generic ...
Try and combine multiple queries (you can replace bag and cement with [value1] and [value2]
a) if you are looking for a 'match with 2 words', you can replace bolsa and cemento with [value1] and [value2]
dbpQuery "data" "Table1" "LIKE PRODUCT [#34]%[value1]%[#34] AND LIKE PRODUCT [#34]%[value2]%[#34]"
... of course you have to StrParse user input to get the two variables from user's input (or ask user to enter each word in a separate TextEntry Box).
b) if you are looking for a 'match with 3 words', you can add another AND query-condition (using [value3])
c) if you are looking for a 'match with a lot of words', you should look at my 3rd suggestion in an earlier post (instead of LIKE, use IN) ... again, you first have to first setup each word as a seaparate variable value.
Just post here how generic a search facility you want to offer your users ...
- maximum of 2 words ?
- maximum of 3 words ?
- maximum of ???? words ?
... the more generic the facility is, the more complex the logic to parse the words and build the query that Access can understand.
Quote from Gaev on October 10, 2019, 3:40 am@neobook
maximum of 3 wods.
any ideas.WARNING - if you are viewing this post in Chrome Browser, and wish to copy/paste the code, make sure you are viewing original content (not Google Translation) ... because Google replaces multiple spaces to single space ... and inserts spaces in other places !!!
Let us say that the variable associated with user input is [matchPhrase].
1) Make sure that multiple (7 or 6 or 5 or 4 or 3 or 2) spaces between words are reduced to just one space ...
StrReplace "[matchPhrase]" " " " " "[matchPhrase]" "" StrReplace "[matchPhrase]" " " " " "[matchPhrase]" "" StrReplace "[matchPhrase]" " " " " "[matchPhrase]" "" StrReplace "[matchPhrase]" " " " " "[matchPhrase]" "" StrReplace "[matchPhrase]" " " " " "[matchPhrase]" "" StrReplace "[matchPhrase]" " " " " "[matchPhrase]" ""2) Split the input to obtain 1, 2 or 3 words ...
StrParse "[matchParse]" " " "[matchWord]" "[matchWordCount]"3) Do the query depending on number of words entered ...
If "[matchWordCount]" "=" "1" dbpQuery "data" "Table1" "LIKE PRODUCTO [#34]%[matchWord1]%[#34]" EndIf If "[matchWordCount]" "=" "2" dbpQuery "data" "Table1" "LIKE PRODUCTO [#34]%[matchWord1]%[#34] AND LIKE PRODUCTO [#34]%[matchWord2]%[#34]" EndIf If "[matchWordCount]" "=" "3" dbpQuery "data" "Table1" "LIKE PRODUCTO [#34]%[matchWord1]%[#34] AND LIKE PRODUCTO [#34]%[matchWord2]%[#34] AND LIKE PRODUCTO [#34]%[matchWord3]%[#34]" EndIf If "[matchWordCount]" "=" "0" AlertBox "Error" "Match Phrase not entered" EndIf If "[matchWordCount]" ">" "3" AlertBox "Error" "Match Phrase contains more than 3 words" EndIf
maximum of 3 wods.
any ideas.
WARNING - if you are viewing this post in Chrome Browser, and wish to copy/paste the code, make sure you are viewing original content (not Google Translation) ... because Google replaces multiple spaces to single space ... and inserts spaces in other places !!!
Let us say that the variable associated with user input is [matchPhrase].
1) Make sure that multiple (7 or 6 or 5 or 4 or 3 or 2) spaces between words are reduced to just one space ...
StrReplace "[matchPhrase]" " " " " "[matchPhrase]" "" StrReplace "[matchPhrase]" " " " " "[matchPhrase]" "" StrReplace "[matchPhrase]" " " " " "[matchPhrase]" "" StrReplace "[matchPhrase]" " " " " "[matchPhrase]" "" StrReplace "[matchPhrase]" " " " " "[matchPhrase]" "" StrReplace "[matchPhrase]" " " " " "[matchPhrase]" ""
2) Split the input to obtain 1, 2 or 3 words ...
StrParse "[matchParse]" " " "[matchWord]" "[matchWordCount]"
3) Do the query depending on number of words entered ...
If "[matchWordCount]" "=" "1" dbpQuery "data" "Table1" "LIKE PRODUCTO [#34]%[matchWord1]%[#34]" EndIf If "[matchWordCount]" "=" "2" dbpQuery "data" "Table1" "LIKE PRODUCTO [#34]%[matchWord1]%[#34] AND LIKE PRODUCTO [#34]%[matchWord2]%[#34]" EndIf If "[matchWordCount]" "=" "3" dbpQuery "data" "Table1" "LIKE PRODUCTO [#34]%[matchWord1]%[#34] AND LIKE PRODUCTO [#34]%[matchWord2]%[#34] AND LIKE PRODUCTO [#34]%[matchWord3]%[#34]" EndIf If "[matchWordCount]" "=" "0" AlertBox "Error" "Match Phrase not entered" EndIf If "[matchWordCount]" ">" "3" AlertBox "Error" "Match Phrase contains more than 3 words" EndIf
Quote from neobook on October 10, 2019, 6:15 pmHola amigo, gracias por contestar y tomarse su tiempo.
El boton Gaev contiene el siguiente codigo
StrReplace "[matchPhrase]" " " " " "[matchPhrase]" ""
StrReplace "[matchPhrase]" " " " " "[matchPhrase]" ""
StrReplace "[matchPhrase]" " " " " "[matchPhrase]" ""
StrReplace "[matchPhrase]" " " " " "[matchPhrase]" ""
StrReplace "[matchPhrase]" " " " " "[matchPhrase]" ""
StrReplace "[matchPhrase]" " " " " "[matchPhrase]" ""
StrParse "[matchParse]" " " "[matchWord]" "[matchWordCount]"
If "[matchWordCount]" "=" "1"
dbpQuery "datos" "Table1" "LIKE PRODUCTO [#34]%[matchWord1]%[#34]"
EndIf
If "[matchWordCount]" "=" "2"
dbpQuery "datos" "Table1" "LIKE PRODUCTO [#34]%[matchWord1]%[#34] AND LIKE PRODUCTO [#34]%[matchWord2]%[#34]"
EndIf
If "[matchWordCount]" "=" "3"
dbpQuery "datos" "Table1" "LIKE PRODUCTO [#34]%[matchWord1]%[#34] AND LIKE PRODUCTO [#34]%[matchWord2]%[#34] AND LIKE PRODUCTO [#34]%[matchWord3]%[#34]"
EndIf
If "[matchWordCount]" "=" "0"
AlertBox "Error" "Match Phrase not entered"
EndIfPero me tira ese error. (ver imagen)
La base datos como vera esta abierta.
Hola amigo, gracias por contestar y tomarse su tiempo.
El boton Gaev contiene el siguiente codigo
StrReplace "[matchPhrase]" " " " " "[matchPhrase]" ""
StrReplace "[matchPhrase]" " " " " "[matchPhrase]" ""
StrReplace "[matchPhrase]" " " " " "[matchPhrase]" ""
StrReplace "[matchPhrase]" " " " " "[matchPhrase]" ""
StrReplace "[matchPhrase]" " " " " "[matchPhrase]" ""
StrReplace "[matchPhrase]" " " " " "[matchPhrase]" ""
StrParse "[matchParse]" " " "[matchWord]" "[matchWordCount]"
If "[matchWordCount]" "=" "1"
dbpQuery "datos" "Table1" "LIKE PRODUCTO [#34]%[matchWord1]%[#34]"
EndIf
If "[matchWordCount]" "=" "2"
dbpQuery "datos" "Table1" "LIKE PRODUCTO [#34]%[matchWord1]%[#34] AND LIKE PRODUCTO [#34]%[matchWord2]%[#34]"
EndIf
If "[matchWordCount]" "=" "3"
dbpQuery "datos" "Table1" "LIKE PRODUCTO [#34]%[matchWord1]%[#34] AND LIKE PRODUCTO [#34]%[matchWord2]%[#34] AND LIKE PRODUCTO [#34]%[matchWord3]%[#34]"
EndIf
If "[matchWordCount]" "=" "0"
AlertBox "Error" "Match Phrase not entered"
EndIf
Pero me tira ese error. (ver imagen)
La base datos como vera esta abierta.
Uploaded files:- You need to login to have access to uploads.
Quote from neobook on October 10, 2019, 6:24 pmaclaracion,
el strreplace esta como uds digo lo que pasa que aca lo pega asi,
por el otro lado cambia la variable del text entry
aclaracion,
el strreplace esta como uds digo lo que pasa que aca lo pega asi,
por el otro lado cambia la variable del text entry
Uploaded files:- You need to login to have access to uploads.
Quote from Gaev on October 10, 2019, 7:14 pm@neobook
The database as you will see is open.
Isn't your database table called Tabla1 ? ... if so, all references to Table1 should be changed to Tabla1
As mentioned before, when the automatic translation (Spanish -> English or English-> Spanish) takes place, a lot of unintended translations also happen.
the strreplace is as you say what happens here hits it like this,
Sorry, I had a typo mistake ... it should be ...
StrParse "[matchPhrase]" " " "[matchWord]" "[matchWordCount]"
... also, the variable (to store TextEntry contents) ... as shown in your foto1.jpg image ... should be [matchPhrase]
Please do not just copy/paste/run the code posted here ... you should study the code (for how things are accomplished) and then run it ... if you had studied the code, it would have been easy to catch the auto-translate and typo errors ... otherwise, you will never learn VisualNEOWin.
The database as you will see is open.
Isn't your database table called Tabla1 ? ... if so, all references to Table1 should be changed to Tabla1
As mentioned before, when the automatic translation (Spanish -> English or English-> Spanish) takes place, a lot of unintended translations also happen.
the strreplace is as you say what happens here hits it like this,
Sorry, I had a typo mistake ... it should be ...
StrParse "[matchPhrase]" " " "[matchWord]" "[matchWordCount]"
... also, the variable (to store TextEntry contents) ... as shown in your foto1.jpg image ... should be [matchPhrase]
Please do not just copy/paste/run the code posted here ... you should study the code (for how things are accomplished) and then run it ... if you had studied the code, it would have been easy to catch the auto-translate and typo errors ... otherwise, you will never learn VisualNEOWin.