SQL query with same criteria on several fields 1

[yobensh]

Hi,

I'm trying to understand why this is not working for me..
I'm using Access2000 database and ADO Object to show data over datagrid.
What I'm acually trying to do is to filter data according to the SAME criteria but on 5 different fields (some of the fields might contain a null value) named CRem1, CRem2 etc. - something like this:

"SELECT * FROM Data WHERE [CRem1] LIKE '1%' OR [CRem2] LIKE '1%' OR [CRem3] LIKE '1%' OR [CRem4] LIKE '1%' OR [CRem5] LIKE '1%' AND Date >= #28/08/2006 00:00:01# AND Date <= #29/08/2006 23:59:59#"
Adodc1.Refresh
Adodc1.Recordset.Filter = sSql

BTW, I'm not in the US so there is no problem with the date format.. ;-)
Would appreciate any help solving this annoying problem!!
Thanks,
Yoav

 

[yobensh]

oops...noticed i've missed something here..sql statment should be:
sSql="SELECT * FROM Data WHERE [CRem1] LIKE '1%' OR [CRem2] LIKE '1%' OR [CRem3] LIKE '1%' OR [CRem4] LIKE '1%' OR [CRem5] LIKE '1%' AND Date >= #28/08/2006 00:00:01# AND Date <= #29/08/2006 23:59:59#"

 

[PGoRule]

I think Like '1%' works with SQL, Oracle. Try passing the value as Like '1*'.

Sharing the best from my side...

--Prashant--

 

[abaldwin]

My guess looking at your sql is that the query will only work between 8/28/2006 and 8/29/2006. It will not return records between those dates but only return records when the current date is between those dates. Date is a reserved word representing the current date. If this is a field name in your database then you should do one of the following.

1. Put [] brackets around the words date in your sql i.e. [Date]

or better

2. Change the field name from Date to DataDate or something else more meaningful to your data.

Thanks


Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[Golom]

Your filters say ... in words ...

Select all rows where at least one of the text fields [CRem1], [CRem2], [CRem3], etc. starts with the digit "1" AND the field [Date] is within the specified range.

Are those fields really text fields?

Also "Date" is a reserved word. If you have a database field with that name then you need to enclose it in square brackets to distinguish it from the Date() function that returns the current date.

You should also indicate the order that you want logical operations performed in.

sSql="SELECT * FROM Data " & _
     "WHERE [red]([/red][CRem1] LIKE '1%' " & _
     "   OR  [CRem2] LIKE '1%' " & _
     "   OR  [CRem3] LIKE '1%' " & _
     "   OR  [CRem4] LIKE '1%' " & _
     "   OR  [CRem5] LIKE '1%' [red])[/red] " & _
     "  AND [red][[/red]Date[red]][/red] BETWEEN " & _
     "      #28/08/2006 00:00:01# AND #29/08/2006 23:59:59#"

"%" is a valid wild-card character in ADO. DAO uses "*".

 

[yobensh]

Hi Golom,

Thanks for the information, but...yes, all fields are text fields.
Still, after adding the [] brakets to fields names, there is no change with the sql result. My suspicious is that there is some kind of problem with the "OR" statments (when testing this on 1 field only, it works fine!), otherwise, I'm getting an error: "Arguments are of the wrong type,are out of acceptable range, or are in conflict with one another!"
Any idea why it's not working?

 

[Golom]

Did you also add the parentheses?

When evaluating AND and OR clauses, AND has precedence so the expression without parens would be evaluated as

sSql="SELECT * FROM Data " & _
     "WHERE  [CRem1] LIKE '1%' " & _
     "   OR  [CRem2] LIKE '1%' " & _
     "   OR  [CRem3] LIKE '1%' " & _
     "   OR  [CRem4] LIKE '1%' " & _
     "   OR  [red][CRem5] LIKE '1%' " & _
     "  AND [Date] BETWEEN " & _
     "      #28/08/2006 00:00:01# AND #29/08/2006 23:59:59#" [/red]

where the part in [red]red[/red] is evaluated as a compound condition.

With parentheses, the evaluation is

sSql="SELECT * FROM Data " & _
     "WHERE [blue]([CRem1] LIKE '1%' " & _
     "   OR  [CRem2] LIKE '1%' " & _
     "   OR  [CRem3] LIKE '1%' " & _
     "   OR  [CRem4] LIKE '1%' " & _
     "   OR  [CRem5] LIKE '1%')[/blue]" & _
     "  AND [red][Date] BETWEEN " & _
     "      #28/08/2006 00:00:01# AND #29/08/2006 23:59:59#" [/red]

where the stuff in [blue]blue[/blue] is evaluated and then "ANDed" with the stuff in [red]red[/red].

 

[JerryKlmns]

Couldn't resist ...

sSql="SELECT * FROM Data " & _
     "WHERE (Left([CRem1])&" & _
            "Left([CRem2])&" & _
            "Left([CRem3])&" & _
            "Left([CRem4])&" & _
            "Left([CRem5]) LIKE '%1%')" & _
     "  AND ([Date] BETWEEN " & _
     "      #2006-08-28 00:00:01# AND #2006-08-29 23:59:59#)"

I wonder: Whats wrong with this sec 28/08/2006 00:00:00

 

[yobensh]

Thanks Golom!!!!
Works like a charm have a star, you deserve it!