Dear Chris:
I'm back to work here now.
There is a challenge here beyond just knowing how to code for a specific
language and application framework. The challenge is that of logic. A bit
of logic training is invaluable at this point. But much more important is
just the ability to think logically.
Let's start with a description of the problem.
Is it the case that you want to EXCLUDE rows from the query whenever the
row's column Basket is not checked if the "basket" check box on the form is
checked? If the "basket" check box on the form is not checked, then all
rows will be included regardless of the Basket column in the table. Is this
correct? If so, let's write the query to do just this one thing and test
that much. Let the following be the entire filter for your query:
WHERE (Cost.Basket = -1 OR [forms]![multi query]![basket] = 0)
Just a note here. Programmers are strongly advised to use a prefix on
controls. Otherwise, there can be a conflict between the name of a control
and the name of a column in the recordsource of a bound form. Calling your
control "basket" when you have a column "Basket" is just such a case. When
you later reference the control, the software will reference the column
instead in ways that are totally mysterious.
Personally, I do not prefer prefixes. When working with an alphabetical
list of controls this creates difficulty for me. I want to look at controls
sorted by their name knowing what name I have made for them, not by some
standard prefix. I therefore choose to suffix this instead of prefixing it.
I've been consistently glad that I do so, but the principle is the same. Do
not use control names that can duplicate column names. I follow that
necessity rigorously, and strongly recommend you do so also.
Can we call the control BasketChk instead. At this point it is important
that you change your thinking as well, to avoid ambiguity there, too. What
I suggest here is that you NEVER refer to a column as a check box. The
datatype used in columns is not check box, but boolean. Using such
terminology in the newsgroup and in your own mind will avoid confusion.
Perhaps you may think I'm nit picking. There are reasons for my strongly
recommending this. As we discuss the logic you are trying to build, it will
be a great advantage to avoid confusion between us AND within your mind (and
mine!) Constructing complex logic is difficult enough without adding even
the smallest bit of confusion.
I propose to build the additional logic you require one piece at a time on
top of this beginning piece. It is important that this be tested well. One
good test I have found is to reverse the logic to be able to see the rows
that will be excluded. Briefly try this variation:
WHERE NOT (Cost.Basket = -1 OR [forms]![multi query]![BasketChk] = 0)
Observe the total number of rows in the table. Observe the number of rows
returned by the query with each of the two filters above. Do they add up to
the total number of rows in the table? If not, you probably have a problem
with NULLs, and we can adjust for that. Important Note: If there is no
problem with NULLs at this time, there may come a time when there IS a NULL
in this column. If so, we must handle that in the query now, or change the
table design to eliminate that possibility.
Please get back to me on how this is working for you, and any questions you
may have. When we are ready to proceed from this point, we can implement
another independent piece of your logic.
Tom Ellison
"Chris W via AccessMonster.com" u12677@uwe wrote in message
news:55b6d9bc59957@uwe...
sorry the last post should have had this above it, sorry not sure what
happend there
Hi Tom thanks for your response
I completely understand that the logic must be optimised and fluent so
that
it can be easily understood. However I think that you may over estimate my
knowledge of SQL data base coding, pretty much none of it seems logical to
me
so I apologise for how thick I must seem to someone who clearly has done a
great deal of this.
I have attempted to follow you original response and construct it in the
way
that you suggest but I now get a syntax error when I try to save if you
could,
though it doesn't vary a lot from the code that you suggest it simply adds
an
element to it for the no_max.date combo box.
Chris W wrote:
WHERE ([forms]![multi query]![basket] = False AND [forms]![multi query]!
[no_max.date] = FALSE AND Cost.[Date of Research] = (select max([Date of
Research]) from cost where cost.[Plant and Machinery ID] =
Transaction.[Plant
and Machinery ID]))
OR
(Cost.[Date of Research] = (select max([Date of Research]) from cost
where
cost.[Plant and Machinery ID] = Transaction.[Plant and Machinery ID]) AND
Transaction.Basket = True)
AND
([forms]![multi query]![no_max.date] = TRUE AND Cost.[Date of Research] =
((
[Date of Research]) from cost where cost.[Plant and Machinery ID] =
Transaction.[Plant and Machinery ID]) AND Transaction.Basket = True)
Please persevere with me I know that I am requiring a lot of guidence
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200510/1