SQL and AND [RESOLVED]

hi:

The last post we had resolve a problem with nulls and empty strings!
Now I have annother problem with Logic Operator "AND":

this is the problem:

Example:
This is the fields I have in the Database Access:

Code:

---


Raca          dt_morte1      dt_morte2      dt_morte3
especie          Goro                          Goro 
especie          car              Goro

---

Now I want extract the fields don't have nulls or empty strings where the "Raca" be "especie" and the dt_morte1,dt_morte2,dt_morte3="Goro"

the code:

Code:

---


SQL = "select Sum(IIF(dt_morte1 Is Null or dt_morte1='', 0, 1) ) as total1,Sum(IIF(dt_morte2 Is Null or dt_morte2='', 0, 1) ) as total2,Sum(IIF(dt_morte3 Is Null or dt_morte3='', 0, 1) ) as total3, from " & ano
SQL = SQL & " WHERE Raca = '" & especie & "'" AND dt_morte1 = '" & Goro  & "' AND dt_morte2 = '" & Goro  & "'  AND dt_morte3 = '" & Goro  & "'

---

the problem is:

With the "AND dt_morte2 = '" & Goro & "' AND dt_morte3 = '" & Goro & "' " the code return a wrong value.He return's "0". :mad:


Only with the first "AND dt_morte1 = '" & Goro &'" the code return the right value "1" :cry:

Something wrong with the expression?Why the code just return the right value with one "AND"? :confused: :confused:

Thanks

Looks like you want OR, and not AND in that expression.

Code:

---



SQL = "select Sum(IIF(dt_morte1 Is Null or dt_morte1='', 0, 1) ) as total1,Sum(IIF(dt_morte2 Is Null or dt_morte2='', 0, 1) ) as total2,Sum(IIF(dt_morte3 Is Null or dt_morte3='', 0, 1) ) as total3, from " & ano
SQL = SQL & " WHERE Raca = '" & especie & "'" AND (dt_morte1 = '" & Goro  & "' OR dt_morte2 = '" & Goro  & "'  OR dt_morte3 = '" & Goro  & ")'

---

with this way returns all fields with the name "Goro" and others names like for example "car"!!

I finc I had try this way too!!

I spent some time formatting it, and think that you combined the AND's instead of using OR.

VB Code:

SQL = _
     "select" & _
       " Sum(IIF(dt_morte1 Is Null or dt_morte1='', 0, 1) )" & _
         " as total1, " & _
       " Sum(IIF(dt_morte2 Is Null or dt_morte2='', 0, 1) )" & _
         " as total2, " & _
       " Sum(IIF(dt_morte3 Is Null or dt_morte3='', 0, 1) )" & _
         " as total3, " & _
     " from " & ano & _
     " WHERE Raca = '" & especie & "'" & _
       " And dt_morte1 = '" & Goro & "'" & _
       " Or dt_morte2 = '" & Goro & "'" & _
       " Or dt_morte3 = '" & Goro & "'"

I think it's easier to read like this.

Quote:

---

I think it's easier to read like this

---

yes you are wright but the sintax have some problem:

error 3141 :blush:

I thought you were using variables, but if you want text, then I think this is closer. There was an extra comma in there, too.

VB Code:

sql = _
     "select" & _
       " Sum(IIF(dt_morte1 Is Null or dt_morte1='', 0, 1) )" & _
         " as total1, " & _
       " Sum(IIF(dt_morte2 Is Null or dt_morte2='', 0, 1) )" & _
         " as total2, " & _
       " Sum(IIF(dt_morte3 Is Null or dt_morte3='', 0, 1) )" & _
         " as total3 " & _
     " from " & ano & _
     " WHERE Raca = 'especie'" & _
       " And dt_morte1 = 'Goro'" & _
       " Or  dt_morte2 = 'Goro'" & _
       " Or  dt_morte3 = 'Goro'"

Here is the code with something alterations,but the syntax with AND and OR don't return the desired value!He returns all fields...he doesn't make the distinction of the fields with the word "Goro"!

Code:

---


SQL = _
    "select" & _
      " Sum(IIF(dt_morte1 Is Null or dt_morte1='', 0, 1) ) as total1, " & _
      " Sum(IIF(dt_morte2 Is Null or dt_morte2='', 0, 1) ) as total2, " & _
      " Sum(IIF(dt_morte3 Is Null or dt_morte3='', 0, 1) ) as total3, " & _
      " Sum(IIF(dt_morte4 Is Null or dt_morte4='', 0, 1) ) as total4, " & _
      " Sum(IIF(dt_morte5 Is Null or dt_morte5='', 0, 1) ) as total5, " & _
      " Sum(IIF(dt_morte6 Is Null or dt_morte6='', 0, 1) ) as total6, " & _
      " Sum(IIF(dt_morte7 Is Null or dt_morte7='', 0, 1) ) as total7, " & _
      " Sum(IIF(dt_morte8 Is Null or dt_morte8='', 0, 1) ) as total8 " & _
    " from " & ano & _
    " WHERE especie = 'especie'" & _
      " And dt_morte1 = 'Goro'" & _
      " Or  dt_morte2 = 'Goro'" & _
      " Or  dt_morte3 = 'Goro'" & _
      " Or  dt_morte4 = 'Goro'" & _
      " Or  dt_morte5 = 'Goro'" & _
      " Or  dt_morte6 = 'Goro'" & _
      " Or  dt_morte7 = 'Goro'" & _
      " Or  dt_morte8 = 'Goro'"

---

I think this is legal in SQL. You could try it without the sums untl you get it working. That's what I do. Break it into parts.

VB Code:

SQL = _
     "select" & _
       " Sum(IIF(dt_morte1 Is Null or dt_morte1='', 0, 1) ) as total1, " & _
       " Sum(IIF(dt_morte2 Is Null or dt_morte2='', 0, 1) ) as total2, " & _
       " Sum(IIF(dt_morte3 Is Null or dt_morte3='', 0, 1) ) as total3, " & _
       " Sum(IIF(dt_morte4 Is Null or dt_morte4='', 0, 1) ) as total4, " & _
       " Sum(IIF(dt_morte5 Is Null or dt_morte5='', 0, 1) ) as total5, " & _
       " Sum(IIF(dt_morte6 Is Null or dt_morte6='', 0, 1) ) as total6, " & _
       " Sum(IIF(dt_morte7 Is Null or dt_morte7='', 0, 1) ) as total7, " & _
       " Sum(IIF(dt_morte8 Is Null or dt_morte8='', 0, 1) ) as total8 " & _
     " from " & ano & _
     " WHERE especie = 'especie'" & _
       " And [COLOR=Red]([/COLOR]dt_morte1 = 'Goro'" & _
       " Or  dt_morte2 = 'Goro'" & _
       " Or  dt_morte3 = 'Goro'" & _
       " Or  dt_morte4 = 'Goro'" & _
       " Or  dt_morte5 = 'Goro'" & _
       " Or  dt_morte6 = 'Goro'" & _
       " Or  dt_morte7 = 'Goro'" & _
       " Or  dt_morte8 = 'Goro'[COLOR=Red])[/COLOR]"

Hi David:

After A lot of tryings a real need some help here because I don't now Why the SQL don't extract that I want!
I had add the project for better understanding!

Please run the project and tell me if sommething is wrong ok?


Thanks

Exactly what are you trying to count? The number of times the word "Goro" appears in each dt_morte field or the number of records which has at least one dt_morte field equal to "Goro"

Using the sample records in the original post what is the total count that you are expecting?

I suspect you want this SQL statement.

VB Code:

SQL = _
     "select" & _
       " Sum(IIF(dt_morte1='Galado', 1, 0) ) as total1, " & _
       " Sum(IIF(dt_morte2='Galado', 1, 0) ) as total2, " & _
       " Sum(IIF(dt_morte3='Galado', 1, 0) ) as total3, " & _
       " Sum(IIF(dt_morte4='Galado', 1, 0) ) as total4, " & _
       " Sum(IIF(dt_morte5='Galado', 1, 0) ) as total5, " & _
       " Sum(IIF(dt_morte6='Galado', 1, 0) ) as total6, " & _
       " Sum(IIF(dt_morte7='Galado', 1, 0) ) as total7, " & _
       " Sum(IIF(dt_morte8='Galado', 1, 0) ) as total8 " & _
     " from " & ano & _
     " WHERE especie = 'Canários de Côr'"

[Edit] I update the SQL statement to reflect your project code.

I try count all the records(dt_morte1...8) (without nulls and empty strings) where the field especie = "Canários de Côr",and the fields(dt_morte1...8) contain the word "Galado"!

Had you try the project?

Thanks

Your code makes sense but the result is "0". :confused: :confused:

Put your code in the project and replace the SQL statment and see the value!

Strange

Yes, I looked at your project and sample database. Using my posted SQL statement a total of 5 is shown in the listview. Is that the number you are expecting (I am still unclear on what you are trying to calculate)?


[Edit] I changed the SQL statement in my previous post to reflect your project. Try it again.

This maybe should be a new thread

But concatenation of long SQL strings is very slow.
If you can dimension the string first, before concatenation like:

Dim strSQL As String
strSQL = Space$(Length of SQL String)

it will significantly speed up handling the SQL String.

Ok BruceVde,now the code works!

Thanks for your help! :)