General question on writing a complex query

[Damon Heron]

I have this query that is one of many in an option box that, if selected,
loads a listbox with the results. I have the AND and OR used several times
and that is where my problem is, I think. It seems to be ignoring the
"GroupID=0" part of the second half of the where conditions. Aside from
that problem, is there a logical way to group conditions when using AND and
OR statements, or can someone point me to a way to design a truth table
around all of this so I can quit wasting all this time experimenting?
Here is the sql stmt....


"SELECT tblContainer.ContainerID, tblContainer.ContainerName,
tblContainer.Capacity, tblContainer.ContainerTypeID,
tblContainer.LocationID, tblContainer.Gallons, tblContainer.Committed" & _
" FROM tblContainer WHERE ((tblContainer.Committed) = False) And
((tblContainer.BldID) = 0) And ((tblContainer.ContainerTypeID) <>2) And
((tblContainer.ContainerTypeID) <>6)" & _

" Or ((tblContainer.Capacity) <> ([tblContainer].[Gallons])) And
((tblContainer.BldID) = " & intBldID & ") And ((tblContainer.GroupID)=0)
ORDER BY tblContainer.Capacity, tblContainer.ContainerName"

Thanks for any and all suggestions...

 

[Guest]

You probably need to add a few ( & ) in your SQL. Remember back to your
first year algebra and combine AND and OR statements.

Try this --
SELECT tblContainer.ContainerID, tblContainer.ContainerName,
tblContainer.Capacity, tblContainer.ContainerTypeID,
tblContainer.LocationID, tblContainer.Gallons, tblContainer.Committed
FROM tblContainer
WHERE (tblContainer.Committed = False And tblContainer.BldID = 0 And
tblContainer.ContainerTypeID <>2 And
tblContainer.ContainerTypeID <>6) Or (tblContainer.Capacity <>
tblContainer.Gallons And tblContainer.BldID = intBldID And
tblContainer.GroupID =0)
ORDER BY tblContainer.Capacity, tblContainer.ContainerName

 

[Damon Heron]

Thanks I will try that without parens.

--
Damon Heron

You probably need to add a few ( & ) in your SQL. Remember back to your
first year algebra and combine AND and OR statements.

Try this --
SELECT tblContainer.ContainerID, tblContainer.ContainerName,
tblContainer.Capacity, tblContainer.ContainerTypeID,
tblContainer.LocationID, tblContainer.Gallons, tblContainer.Committed
FROM tblContainer
WHERE (tblContainer.Committed = False And tblContainer.BldID = 0 And
tblContainer.ContainerTypeID <>2 And
tblContainer.ContainerTypeID <>6) Or (tblContainer.Capacity <>
tblContainer.Gallons And tblContainer.BldID = intBldID And
tblContainer.GroupID =0)
ORDER BY tblContainer.Capacity, tblContainer.ContainerName

I have this query that is one of many in an option box that, if selected,
loads a listbox with the results. I have the AND and OR used several
times
and that is where my problem is, I think. It seems to be ignoring the
"GroupID=0" part of the second half of the where conditions. Aside from
that problem, is there a logical way to group conditions when using AND
and
OR statements, or can someone point me to a way to design a truth table
around all of this so I can quit wasting all this time experimenting?
Here is the sql stmt....


"SELECT tblContainer.ContainerID, tblContainer.ContainerName,
tblContainer.Capacity, tblContainer.ContainerTypeID,
tblContainer.LocationID, tblContainer.Gallons, tblContainer.Committed" &
_
" FROM tblContainer WHERE ((tblContainer.Committed) = False) And
((tblContainer.BldID) = 0) And ((tblContainer.ContainerTypeID) <>2) And
((tblContainer.ContainerTypeID) <>6)" & _

" Or ((tblContainer.Capacity) <> ([tblContainer].[Gallons])) And
((tblContainer.BldID) = " & intBldID & ") And ((tblContainer.GroupID)=0)
ORDER BY tblContainer.Capacity, tblContainer.ContainerName"

Thanks for any and all suggestions...