Re: Attaching a check box to a max date function

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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>

"Chris W via AccessMonster.com" <u12677@uwe> wrote in message
news:55ab7d5665006@xxxxxx
> 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 >


.

Relevant Pages

  • Re: query based on form
    ... Typically it's only the WHERE clause of the query that changes so you can build that dynamically, patch it into the SQL statement, and assign it to the SQL property of the QueryDef you use for export. ... they can choose one Sales Rep, all cities, all states, all counties, just one type of business and one renewal month. ...
    (microsoft.public.access.queries)
  • Re: Whats wrong?
    ... variables into the SQL statement? ... That way, as the cursor is declared only once, and even if you change the query inside the statement variable, each time you open the cursor the first query will be executed, that's not what I need. ... The USING clause should be specific for opening the cursor with a changed query each time. ...
    (comp.sys.ibm.as400.misc)
  • Re: Ranking my results in a query to get the TOP#10 percent
    ... Without order by clause, it just returns 10 percent of the records. ... Can this be accomplished in same Total query where I perform a "COUNT" on ... the SQL statement suggested below. ... Could you please assist me with an Access SQL statement that can ...
    (microsoft.public.access.queries)
  • Re: Send filtered subform data to excel
    ... Private Sub Export_to_Excel_Click ... A QueryDef is just a saved query (i.e. the definition of the of the query, ... It is defined as a SQL statement, ... WHERE clause), goes in the constant we named strcTail. ...
    (microsoft.public.access.formscoding)
  • Re: Optimisation of a sql query
    ... About using group by and having, I have to confess that the query I ... Shall I go ahead with the where clause as suggested ... passed on to the grouping (and possible sorting operation for Oracle ... you can start tuning the SQL statement. ...
    (comp.databases.oracle.server)