Attaching a check box to a max date function

[Chris W via AccessMonster.com]

Hi all

I have hit a snag on a feature of a query. My database developed to search
valuations of plant and machinery; each ‘asset’ can have many valuations as
they can be updated. So in the vast majority of times the user only wants to
view the most recent valuation. However, there is not the option for the user
to view all previous valuations should they wish to. What I want is to
include a checkbox in the form attached to this query and when checked the
database returns all valuations.

Here is the current SQL for the max date function:

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]))

If anyone can offer some help I would really appreciate it, thanks

 

[Ken Snell [MVP]]

Your statement is not a full SQL statement, but perhaps this modification
will get you close:

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)

 

[Chris W via AccessMonster.com]

Thanks Ken for your help I really appreciate it

I have implemented your changes but it now is not operating properly with
another function in the WERE statement of the SQL. I have another function
that requires when a check box (lets call it basket) is checked it will only
return the records that contain a TRUE ‘basket’ but when the ‘basket’ is
false i.e. blank the query is to return all records whether the records
whether ‘basket’ is TRUE or FALSE.

When I implemented the changes you suggested it only works for records were
basket = true despite the fact that they have more than one value/costing it
is not being returned.

What I need is for the max date function to be disabled when the desired
checkbox (not basket) is selected.

I have included the entire SQL statement for the query in question, including
the area were the changes where made.

Thanks for any help I would really appreciate it!!

SELECT Cost.[Date of Research], [Generic asset].[Generic Asset Name], TYPE.
[TYPE NAME], Transaction.Description, Client.[Client Name], Manfacturer.
[Manufacturer Name], Transaction.Model, Transaction.[Serial Number],
Transaction.[Cap/size], Transaction.[year of manufacture], Cost.Condition,
Transaction.[Country of Origion], Transaction.[Useful life], Cost.Valuer,
Cost.[Replacement Cost], Cost.[Insalation %], Cost.[Used Price], Transaction.
Basket, [Industry Classification].[Industry classification Name], [Generic
asset].[Generic Asset Name], Transaction.ANZSIC


FROM TYPE RIGHT JOIN ([Industry Classification] RIGHT JOIN (Cost RIGHT JOIN
(Client RIGHT JOIN (Manfacturer RIGHT JOIN ([Generic asset] RIGHT JOIN
[Transaction] ON [Generic asset].[Generic Asset ID]=Transaction.[Generic
asset ID]) ON Manfacturer.[Manufacturer Id]=Transaction.[Manufacturer ID]) ON
Client.[Client ID]=Transaction.[Client ID]) ON Cost.[Plant and Machinery ID]
=Transaction.[Plant and Machinery ID]) ON [Industry Classification].
ANZSIC=Transaction.ANZSIC) ON TYPE.[TYPE ID]=Transaction.[TYPE ID]


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])) OR [Forms]![multi query]![check66]
=True)) AND ((Transaction.Basket)=True)


GROUP BY Cost.[Date of Research], [Generic asset].[Generic Asset Name], TYPE.
[TYPE NAME], Transaction.Description, Client.[Client Name], Manfacturer.
[Manufacturer Name], Transaction.Model, Transaction.[Serial Number],
Transaction.[Cap/size], Transaction.[year of manufacture], Cost.Condition,
Transaction.[Country of Origion], Transaction.[Useful life], Cost.Valuer,
Cost.[Replacement Cost], Cost.[Insalation %], Cost.[Used Price], Transaction.
Basket, [Industry Classification].[Industry classification Name], [Generic
asset].[Generic Asset Name], Transaction.ANZSIC, Transaction.[Plant and
Machinery ID], Transaction.[Generic asset ID], TYPE.[TYPE ID], Transaction.
[Client ID], Transaction.[Manufacturer ID], (Cost.Valuer) Like "*" & Forms!
[multi query]!text3 & "*", (Cost.[Date of research]) Like "*" & Forms![multi
query]![Date of research] & "*", (Transaction.Model) Like "*" & Forms![multi
query]!text2 & "*", (Transaction.[Cap/size]) Like "*" & Forms![multi query]!
text1 & "*", ([Industry Classification].ANZSIC) Like "*" & Forms![multi query]
!Combo41 & "*", (Client.[Client ID]) Like "*" & Forms![multi query]!Combo38 &
"*", ([Generic asset].[Generic Asset ID]) Like "*[Forms]![multi query]!
[Combo36]*", (Manfacturer.[Manufacturer Id]) Like "*[Forms]![multi query]!
[Combo34]*"


HAVING (((Cost.[Date of Research]) Like "*" & Forms![multi query]![Date of
research] & "*") And ((Transaction.Description) Like "*" & Forms![multi query]
!text57 & "*") And ((Transaction.Model) Like "*" & Forms![multi query]!text2
& "*") And ((Transaction.[Serial Number]) Like "*" & Forms![multi query]!
text162 & "*") And ((Transaction.[Cap/size]) Like "*" & Forms![multi query]!
text1 & "*") And ((Transaction.ANZSIC) Like Forms![multi query]!Combo41 & "*")
And ((Transaction.[Generic asset ID]) Like Forms![multi query]!Combo36 & "*")
And ((TYPE.[TYPE ID]) Like Forms![multi query]!Combo64 & "*") And (
(Transaction.[Client ID]) Like Forms![multi query]!Combo38 & "*") And (
(Transaction.[Manufacturer ID]) Like Forms![multi query]!Combo34 & "*")) Or (
((Transaction.Description) Is Null) And ((Transaction.[Serial Number]) Is
Null) And ((Transaction.[Generic asset ID]) Is Null) And (((Cost.Valuer) Like
"*" & Forms![multi query]!text3 & "*") Is Null) And (((Cost.[Date of research]
) Like "*" & Forms![multi query]![Date of research] & "*") Is Null) And ((
(Transaction.Model) Like "*" & Forms![multi query]!text2 & "*") Is Null) And
(((Transaction.[Cap/size]) Like "*" & Forms![multi query]!text1 & "*") Is
Null) And ((([Industry Classification].ANZSIC) Like "*" & Forms![multi query]!
Combo41 & "*") Is Null) And (((Client.[Client ID]) Like "*" & Forms![multi
query]!Combo38 & "*") Is Null) And (((Manfacturer.[Manufacturer Id]) Like "*
[Forms]![multi query]![Combo34]*") Is Null))


ORDER BY Transaction.[Plant and Machinery ID], Transaction.[Generic asset ID]
;

Your statement is not a full SQL statement, but perhaps this modification
will get you close:

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)

Hi all

[quoted text clipped - 18 lines]

If anyone can offer some help I would really appreciate it, thanks

 

[Ken Snell [MVP]]

A somewhat complex SQL statement indeed... I'm not sure if I'm understanding
all of its intent, but let's focus then on the WHERE clause that you posted:

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])) OR [Forms]![multi query]![check66]
=True)) AND ((Transaction.Basket)=True)


You want to return all records if "check66" is True on the form. And you
only want to return records where Basket is True if the "basket checkbox" (I
don't see that in the WHERE clause that you posted?) is True on the form,
else return all records regardless of the value of Basket if the "basket
checkbox" is False on the form? Is this correct?

In your WHERE clause, identify what "check60" is used for on the form. Then
we'll see if we can identify a WHERE clause that will work.

--

Ken Snell
<MS ACCESS MVP>

Thanks Ken for your help I really appreciate it

I have implemented your changes but it now is not operating properly with
another function in the WERE statement of the SQL. I have another function
that requires when a check box (lets call it basket) is checked it will
only
return the records that contain a TRUE 'basket' but when the 'basket' is
false i.e. blank the query is to return all records whether the records
whether 'basket' is TRUE or FALSE.

When I implemented the changes you suggested it only works for records
were
basket = true despite the fact that they have more than one value/costing
it
is not being returned.

What I need is for the max date function to be disabled when the desired
checkbox (not basket) is selected.

I have included the entire SQL statement for the query in question,
including
the area were the changes where made.

Thanks for any help I would really appreciate it!!

SELECT Cost.[Date of Research], [Generic asset].[Generic Asset Name],
TYPE.
[TYPE NAME], Transaction.Description, Client.[Client Name], Manfacturer.
[Manufacturer Name], Transaction.Model, Transaction.[Serial Number],
Transaction.[Cap/size], Transaction.[year of manufacture], Cost.Condition,
Transaction.[Country of Origion], Transaction.[Useful life], Cost.Valuer,
Cost.[Replacement Cost], Cost.[Insalation %], Cost.[Used Price],
Transaction.
Basket, [Industry Classification].[Industry classification Name], [Generic
asset].[Generic Asset Name], Transaction.ANZSIC


FROM TYPE RIGHT JOIN ([Industry Classification] RIGHT JOIN (Cost RIGHT
JOIN
(Client RIGHT JOIN (Manfacturer RIGHT JOIN ([Generic asset] RIGHT JOIN
[Transaction] ON [Generic asset].[Generic Asset ID]=Transaction.[Generic
asset ID]) ON Manfacturer.[Manufacturer Id]=Transaction.[Manufacturer ID])
ON
Client.[Client ID]=Transaction.[Client ID]) ON Cost.[Plant and Machinery
ID]
=Transaction.[Plant and Machinery ID]) ON [Industry Classification].
ANZSIC=Transaction.ANZSIC) ON TYPE.[TYPE ID]=Transaction.[TYPE ID]


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])) OR [Forms]![multi query]![check66]
=True)) AND ((Transaction.Basket)=True)


GROUP BY Cost.[Date of Research], [Generic asset].[Generic Asset Name],
TYPE.
[TYPE NAME], Transaction.Description, Client.[Client Name], Manfacturer.
[Manufacturer Name], Transaction.Model, Transaction.[Serial Number],
Transaction.[Cap/size], Transaction.[year of manufacture], Cost.Condition,
Transaction.[Country of Origion], Transaction.[Useful life], Cost.Valuer,
Cost.[Replacement Cost], Cost.[Insalation %], Cost.[Used Price],
Transaction.
Basket, [Industry Classification].[Industry classification Name], [Generic
asset].[Generic Asset Name], Transaction.ANZSIC, Transaction.[Plant and
Machinery ID], Transaction.[Generic asset ID], TYPE.[TYPE ID],
Transaction.
[Client ID], Transaction.[Manufacturer ID], (Cost.Valuer) Like "*" &
Forms!
[multi query]!text3 & "*", (Cost.[Date of research]) Like "*" &
Forms![multi
query]![Date of research] & "*", (Transaction.Model) Like "*" &
Forms![multi
query]!text2 & "*", (Transaction.[Cap/size]) Like "*" & Forms![multi
query]!
text1 & "*", ([Industry Classification].ANZSIC) Like "*" & Forms![multi
query]
!Combo41 & "*", (Client.[Client ID]) Like "*" & Forms![multi
query]!Combo38 &
"*", ([Generic asset].[Generic Asset ID]) Like "*[Forms]![multi query]!
[Combo36]*", (Manfacturer.[Manufacturer Id]) Like "*[Forms]![multi query]!
[Combo34]*"


HAVING (((Cost.[Date of Research]) Like "*" & Forms![multi query]![Date of
research] & "*") And ((Transaction.Description) Like "*" & Forms![multi
query]
!text57 & "*") And ((Transaction.Model) Like "*" & Forms![multi
query]!text2
& "*") And ((Transaction.[Serial Number]) Like "*" & Forms![multi query]!
text162 & "*") And ((Transaction.[Cap/size]) Like "*" & Forms![multi
query]!
text1 & "*") And ((Transaction.ANZSIC) Like Forms![multi query]!Combo41 &
"*")
And ((Transaction.[Generic asset ID]) Like Forms![multi query]!Combo36 &
"*")
And ((TYPE.[TYPE ID]) Like Forms![multi query]!Combo64 & "*") And (
(Transaction.[Client ID]) Like Forms![multi query]!Combo38 & "*") And (
(Transaction.[Manufacturer ID]) Like Forms![multi query]!Combo34 & "*"))
Or (
((Transaction.Description) Is Null) And ((Transaction.[Serial Number]) Is
Null) And ((Transaction.[Generic asset ID]) Is Null) And (((Cost.Valuer)
Like
"*" & Forms![multi query]!text3 & "*") Is Null) And (((Cost.[Date of
research]
) Like "*" & Forms![multi query]![Date of research] & "*") Is Null) And ((
(Transaction.Model) Like "*" & Forms![multi query]!text2 & "*") Is Null)
And
(((Transaction.[Cap/size]) Like "*" & Forms![multi query]!text1 & "*") Is
Null) And ((([Industry Classification].ANZSIC) Like "*" & Forms![multi
query]!
Combo41 & "*") Is Null) And (((Client.[Client ID]) Like "*" & Forms![multi
query]!Combo38 & "*") Is Null) And (((Manfacturer.[Manufacturer Id]) Like
"*
[Forms]![multi query]![Combo34]*") Is Null))


ORDER BY Transaction.[Plant and Machinery ID], Transaction.[Generic asset
ID]
;

< snipped >

 

[Chris W via AccessMonster.com]

Sorry Ken I hadn’t seen your reply

So many times people offer a solution and don’t follow it up; I really
appreciate your assistance.

I have changed the name of check 60 to basket to make it clearer.

You want to return all records if "check66" is True on the form

Yeah, when an asset has more than one valuation, I want to be able to
display all these when check 66 = TRUE, when check 66 = FLASE I want the max
date function active so that only the most recent valuation for that asset is
shown.

This is altered only when an asset is included in the basket bracketing
feature ‘basket’ previously ‘check 60’ when basket is selected on the form I
only want records that have basket in the table/on the record = TRUE
(transaction.basket = true).

When both basket and check 66 are selected on the form, therefore both = TRUE,
the query should return the all the valuations (in other words not just the
most recent one) for records where basket in the table/record = true

(I don't see that in the WHERE clause that you posted?)

Yeah I didn’t include it in the original post because I didn’t think it would
be a problem to integrate, I should have realised that things are never
simple in the world of database building, to which I am very new.

Thanks mate, again I apologise for making another post but like I said often
people don’t follow up responses after an initial post.

A somewhat complex SQL statement indeed... I'm not sure if I'm understanding
all of its intent, but let's focus then on the WHERE clause that you posted:

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])) OR [Forms]![multi query]![check66]
=True)) AND ((Transaction.Basket)=True)

You want to return all records if "check66" is True on the form. And you
only want to return records where Basket is True if the "basket checkbox" (I
don't see that in the WHERE clause that you posted?) is True on the form,
else return all records regardless of the value of Basket if the "basket
checkbox" is False on the form? Is this correct?

In your WHERE clause, identify what "check60" is used for on the form. Then
we'll see if we can identify a WHERE clause that will work.

Thanks Ken for your help I really appreciate it

[quoted text clipped - 119 lines]

< snipped >

 

[Ken Snell [MVP]]

I see that you have started another thread in this newsgroup, and Tom
Ellison, former MVP in ACCESS, has replied. Rather than have both us work
simultaneously on the same question, I'm going to wait and see if his answer
is what you're seeking.

--

Ken Snell
<MS ACCESS MVP>

Sorry Ken I hadn't seen your reply

So many times people offer a solution and don't follow it up; I really
appreciate your assistance.

I have changed the name of check 60 to basket to make it clearer.

You want to return all records if "check66" is True on the form

Yeah, when an asset has more than one valuation, I want to be able to
display all these when check 66 = TRUE, when check 66 = FLASE I want the
max
date function active so that only the most recent valuation for that asset
is
shown.

This is altered only when an asset is included in the basket bracketing
feature 'basket' previously 'check 60' when basket is selected on the form
I
only want records that have basket in the table/on the record = TRUE
(transaction.basket = true).

When both basket and check 66 are selected on the form, therefore both =
TRUE,
the query should return the all the valuations (in other words not just
the
most recent one) for records where basket in the table/record = true

(I don't see that in the WHERE clause that you posted?)

Yeah I didn't include it in the original post because I didn't think it
would
be a problem to integrate, I should have realised that things are never
simple in the world of database building, to which I am very new.

Thanks mate, again I apologise for making another post but like I said
often
people don't follow up responses after an initial post.

A somewhat complex SQL statement indeed... I'm not sure if I'm
understanding
all of its intent, but let's focus then on the WHERE clause that you
posted:

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])) OR [Forms]![multi query]![check66]
=True)) AND ((Transaction.Basket)=True)

You want to return all records if "check66" is True on the form. And you
only want to return records where Basket is True if the "basket checkbox"
(I
don't see that in the WHERE clause that you posted?) is True on the form,
else return all records regardless of the value of Basket if the "basket
checkbox" is False on the form? Is this correct?

In your WHERE clause, identify what "check60" is used for on the form.
Then
we'll see if we can identify a WHERE clause that will work.

Thanks Ken for your help I really appreciate it

[quoted text clipped - 119 lines]

< snipped >