Query in a multi fields

[Leos]

Hi,
Is there any simple way to make a query simultaneously in all the fields in one table?
I tried to use 'CONTAINS' but it returns error.

Example:

database: TESTE
field1
field2
field3


*SELECT *
FROM TESTE
WHERE CONTAINS((field1,field2,field3,) ' "Neobook" ');

Thanks

[Gaev]

Leos:

If you want to select records where 'NeoBook' is in ANY ONE of the fields ...

Code: Select all

SELECT FROM TESTE
WHERE field1 LIKE '%NeoBook%' OR field2 LIKE '%NeoBook%' OR field3 LIKE '%NeoBook%';

If you want to select records where 'NeoBook' is in ALL the fields ...

Code: Select all

SELECT FROM TESTE
WHERE field1 LIKE '%NeoBook%' AND field2 LIKE '%NeoBook%' AND field3 LIKE '%NeoBook%';

[Leos]

Hi Gaev,
One more time thank you for your help, but in this case, is not exactly what I need.
Using this method I get this, and is not yet complete!

Code: Select all

SELECT * from EuroMilhoes35 where ((((N1 LIKE '3' OR N2 LIKE '3' OR N3 LIKE '3' OR N4 LIKE '3' OR N5 LIKE '3') AND (N2 LIKE '7' OR N3 LIKE '7' OR N4 LIKE '7' OR N5 LIKE '7') OR ((N1 LIKE '3' OR N2 LIKE '3' OR N3 LIKE '3' OR N4 LIKE '3' OR N5 LIKE '3') AND (N2 LIKE '10' OR N3 LIKE '10' OR N4 LIKE '10' OR N5 LIKE '10')) OR ((N1 LIKE '3' OR N2 LIKE '3' OR N3 LIKE '3' OR N4 LIKE '3' OR N5 LIKE '3') AND (N2 LIKE '13' OR N3 LIKE '13' OR N4 LIKE '13' OR N5 LIKE '13')) OR ((N1 LIKE '3' OR N2 LIKE '3' OR N3 LIKE '3' OR N4 LIKE '3' OR N5 LIKE '3') AND (N2 LIKE '21' OR N3 LIKE '21' OR N4 LIKE '21' OR N5 LIKE '21'))) OR ((N1 LIKE '7' OR N2 LIKE '7' OR N3 LIKE '7' OR N4 LIKE '7' OR N5 LIKE '7') AND (N2 LIKE '10' OR N3 LIKE '10' OR N4 LIKE '10' OR N5 LIKE '10'))) OR ((N1 LIKE '10' OR N2 LIKE '10' OR N3 LIKE '10' OR N4 LIKE '10' OR N5 LIKE '10') AND (N2 LIKE '13' OR N3 LIKE '13' OR N4 LIKE '13' OR N5 LIKE '13'))) OR ((N1 LIKE '13' OR N2 LIKE '13' OR N3 LIKE '13' OR N4 LIKE '13' OR N5 LIKE '13') AND (N2 LIKE '21' OR N3 LIKE '21' OR N4 LIKE '21' OR N5 LIKE '21'));

SQL it haves WHERE / CONTAINS that solve my problem, but it doesn't work in SQL access database
It was great if NeobookDBPro did it...
Maybe in a next update?

Best regards

[David de Argentina]

Hi,

Try something like this:

Code: Select all

SELECT *
FROM TESTE
WHERE
field1 IN("Neobook", "NeoPaint", "NeoDBPro") OR
field2 IN("Neobook", "NeoPaint", "NeoDBPro") OR
field3 IN("Neobook", "NeoPaint", "NeoDBPro");

Source:
http://office.microsoft.com/en-us/acces ... 66611.aspx

My devaluated (12x) cent,
David de Argentina

[Leos]

Hi, David

This works (without the brackets), but is not yet what i need...
(if you have for example 20 fields, it's a problem...)

I need something like that:

Code: Select all

SELECT *
FROM TESTE
WHERE
* IN("Neobook", "NeoPaint", "NeoDBPro")

The second " * " it represents "ALL FIELDS"

Best regards to you

[Gaev]

Leos:

The second " * " it represents "ALL FIELDS"

It was great if NeobookDBPro did it...
Maybe in a next update?

I do not think SQL supports the use of the * (wildcard) for the field names ... so it is unlikely that NeoBookDBPro can provide a similar command.

Note that it is highly unlikely that ALL fields in a Table are of the same type (text, number, boolean etc.) ... so such a requirement is not so common.

(if you have for example 20 fields, it's a problem...)

If it is a problem for you (the developer) to construct manually, there is a way around it i.e. you can pass some variables to a generic subroutine e.g. ...

Code: Select all

SetVar "[FieldList]" "field1,field2,field3"
SetVar "[InList]" "NeoBook,NeoPaint,NeoDBPro"
SetVar "[QueryConnector]" "OR"
GoSub "ComposeQuery"

... then issue the SQL command like ...

Code: Select all

dbpExecSQL "database id" "SELECT *
FROM TESTE WHERE [ComposedQuery]" "results table"

... where [ComposedQuery] would be constructed by the subroutine.

If you do not want to manually specify ALL fields in the Table for [FieldList], you can ...

Code: Select all

dbpGetTableNames "TableNameHere" "," "[FieldList]"

If you need help with the subroutine, ask here.

[Leos]

Hi Gaev,
I really appreciate your help, but in this case is not exactly what i have in my mind...

What I need / suggest, is a command in NeobookDBPro, so simple, something like that:

Code: Select all

dbpFind "DataBase" "Table" "field1;field2;field3;field4;field5" "text to find" "ExactMatch=No;CaseSensitive=No"


But doing a query, not a search in the database...
something like that:

Code: Select all

what i want:

"dbpQuery "DataBase" "Table" "field1;field2;field3;field4;field5" "text to query" "ExactMatch=No/yes;CaseSensitive=No/yes"

OR

"dbpQuery "DataBase" "Table" "field1;field2;field3;field4;field5" " 'text' 'to' 'query' " "ExactMatch=No/yes;CaseSensitive=No/yes"

I know that we have the problem with different fields datatypes, etc... but the idea is exactly not care about it...

Somethink like that:

Code: Select all

query my table with this 'text to find' (string data, numeric, memo ... don´t care...)

Best regards to you

[Neosoft Support]

I'm not sure if this is something that could be done through DBPro or not. Almost everything that DBPro does behind the scenes involves SQL. Apparently, MS Access does not support the SQL Contains function, so DBPro can't change that.