Re: Integrating the code for a max.date function with a check box controlling it and
- From: "Tom Ellison" <tellison@xxxxxxxxxxx>
- Date: Tue, 11 Oct 2005 20:13:26 -0500
Dear Chris:
I've reformatted your second WHERE clause here, removing some of the
unnecessary parentheses (probably those added by Access when it mangles your
query:
WHERE
([forms]![multi query]![check60] = 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)
Sometimes, seeing the logic of what you've done can have an effect on your
view of what might be wrong.
Now, you say:
when basket = TRUE (on the form) return only the records containing a TRUE
basket check box but when 'basket' on the form = false the query is to
return all records wether they contain a TRUE value or a FALSE.
I do not see where you have tested "Basket on the form". Perhaps this is
chekc60? When you have a control you want to reference elsewhere, it's good
to name the control according to its function, not only so that others (like
myself) can tell what you intend, but to avoid making some mistake yourself.
Just a suggestion, eh.
Now, I assume next that "records containing a TRUE basket check box" refers
to a boolean column in the table Cost. Is that correct? If so, where in
this query fragment did you test that column?
The logic I would use to test this would look something like this:
WHERE (Cost.Basket = -1 OR [forms]![multi query]![check60] = 0)
AND . . .
This construction is not immediately intuitive, but I believe it is correct
and is fairly minimal. If the Basket column is checked (true), then the
test is true, whether or not the Basket on the form is checked. If the
Basket on the form is unchecked, the result is true whether the column in
the table is true or not. The only time this will exclude a row is when the
column is unchecked and the check box on the form IS checked. Isn't that
just what you want?
By the way, the value of TRUE is -1 for Access Jet databases, with respect
to both columns in tables and checkboxes on forms.
Please let me know if this helped, and if I can be of any other assistance.
Tom Ellison
"Chris W via AccessMonster.com" <u12677@uwe> wrote in message
news:55b37e8318c02@xxxxxx
> Hi all
>
> I have a problem with integrating some code I received via a previous post
> for linking a check box to a max.date function: this is the max date
function
> code: (note: this is only the WHERE statement from the SQL code as the
full
> code is very long and not all together necessary, when ever I put it in a
> post it seems to scare people off)
>
> WHERE (((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]))
> OR [Forms]![FormName]![CheckBoxName] = True)
>
> The problem that I am having now is that it fails to integrate with
another
> feature of the query, which is another check box 'basket'. The function
says
> that when basket = TRUE (on the form) return only the records containing a
> TRUE basket check box but when 'basket' on the form = false the query is
to
> return all records wether they contain a TRUE value or a FALSE.
> My code as it is prior to the addition of the second checkbox is,
>
> WHERE ((([forms]![multi query]![check60])=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))
>
> So I need it to operate together when basket is selected only records
where
> basket=true are returned and when basket and the checkbox to disable max.
> date function is true it returns all date for records where basket = true
and
> so on..for all the combinations of the two check box's
>
> I would dearly love to know how to integrate them to create a statement
that
> works, thanks for any help that you can offer I really appreciate it.
>
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200510/1
.
- References:
- Integrating the code for a max.date function with a check box controlling it and
- From: Chris W via AccessMonster.com
- Integrating the code for a max.date function with a check box controlling it and
- Prev by Date: Re: Sum and Avg with DateDiff
- Next by Date: Re: Sum the row values
- Previous by thread: Integrating the code for a max.date function with a check box controlling it and
- Next by thread: Re: Integrating the code for a max.date function with a check box controlling it and
- Index(es):
Relevant Pages
|
