change query output depending on option button 1

[tamus121]

I have a table of clients and a table of their inspetion details. I check inspections details between two select details with the report based on a query.

I want to allow users to select an option button and the query to list of clients who haven't been inspected during the selected dates.

I'm trying to find a solution to this but so far nothing. Do I need a second query?

tamus

 

[dhookom]

Have you considered using a query with a LEFT or RIGHT JOIN so all records from Clients are included?

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya tamus121 . . .

Post the [blue]SQL[/blue] of the query ...

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[tamus121]

First, sorry for the mistake in my first line, it should be

I have a table of clients and a table of their inspetion details. I will check inspections details for clients between two selected dates with the report based on this query.

Now first the SQL of the query (the left side is a query on table CustomerRecords which just weeds out the stuff not needed in ths table). Maybe that is the query I should be editing with a left join to the PH4Part1 table?

SELECT CustomerRecordsQuery.Name, CustomerRecordsQuery.RegNo, CustomerRecordsQuery.AreaCode, CustomerRecordsQuery.Address1, CustomerRecordsQuery.Address2, CustomerRecordsQuery.Address3, CustomerRecordsQuery.Town, CustomerRecordsQuery.County, PH4Part1.Crecords, PH4Part1.Dpassports, PH4Part1.Etraceback, PH4Part1.Fisolation, PH4Part1.Girrigating, PH4Part1.Action, PH4Part1.Insp, PH4Part1.DateInsp, PH4Part1.UKNI
FROM CustomerRecordsQuery INNER JOIN PH4Part1 ON CustomerRecordsQuery.[RegNo] = PH4Part1.[UKNI]
WHERE (((PH4Part1.Insp) Like IIf([Forms]![PH4Part1Report]![InspCmb]="ALL","*",[Forms]![PH4Part1Report]![InspCmb])) AND ((PH4Part1.DateInsp)>=[Forms]![PH4Part1Report]![FirstDateTxt] And (PH4Part1.DateInsp)<=[Forms]![PH4Part1Report]![LastDateTxt]));

Now I have tried a simpler query for now and have changed the join properties from 1 to 2

SELECT CustomerRecordsQuery.RegNo, CustomerRecordsQuery.AreaCode, CustomerRecordsQuery.Name, CustomerRecordsQuery.Address1, CustomerRecordsQuery.Address2, CustomerRecordsQuery.Address3, CustomerRecordsQuery.Town, CustomerRecordsQuery.County, PH4Part1.DateInsp
FROM CustomerRecordsQuery LEFT JOIN PH4Part1 ON CustomerRecordsQuery.[RegNo] = PH4Part1.[UKNI]
WHERE (((PH4Part1.DateInsp) Is Null)) OR (((PH4Part1.DateInsp)<[Forms]![PH4Part1Report]![FirstDateTxt] And (PH4Part1.DateInsp)>[Forms]![PH4Part1Report]![LastDateTxt]));

I think this will work (maybe that is a bit strong) but is this what you mean dhookom?

If so, I need to build it up with more detail to give the report I am looking
tamus

 

[tamus121]

ok I am thinking maybe I have got it with this

SELECT CustomerRecordsQuery.Name, CustomerRecordsQuery.RegNo, CustomerRecordsQuery.AreaCode, CustomerRecordsQuery.Address1, CustomerRecordsQuery.Address2, CustomerRecordsQuery.Address3, CustomerRecordsQuery.Town, CustomerRecordsQuery.County, PH4Part1.Crecords, PH4Part1.Dpassports, PH4Part1.Etraceback, PH4Part1.Fisolation, PH4Part1.Girrigating, PH4Part1.Action, PH4Part1.Insp, PH4Part1.DateInsp
FROM CustomerRecordsQuery LEFT JOIN PH4Part1 ON CustomerRecordsQuery.[RegNo] = PH4Part1.[UKNI]
WHERE (((PH4Part1.Insp) Like IIf([Forms]![PH4Part1Report]![InspCmb]="ALL","*",[Forms]![PH4Part1Report]![InspCmb])));

then use the WhereCondition of OpenReport to show clients with inspections within the date range or not depending on the users selection.

Think I'll leave the CustomerRecords query alone for now as I use it for other things and it would make my query complex (for me anyway)

 

[TheAceMan1]

For easier viewing of the SQL:

[blue][green]Post Origination 19 Feb 12 20:08
********************************[/green]
SELECT CRQ.Name, 
       CRQ.RegNo, 
       CRQ.AreaCode, 
       CRQ.Address1, 
       CRQ.Address2, 
       CRQ.Address3, 
       CRQ.Town, 
       CRQ.County, 
       P1.Crecords, 
       P1.Dpassports, 
       P1.Etraceback, 
       P1.Fisolation, 
       P1.Girrigating, 
       P1.Action, 
       P1.Insp, 
       P1.DateInsp, 
       P1.UKNI
FROM CustomerRecordsQuery AS CRQ
INNER JOIN PH4Part1 AS P1
ON CRQ.RegNo = P1.UKNI

WHERE (((P1.Insp) Like IIf(Forms!PH4Part1Report!InspCmb="ALL",
                                                        "*",
                                                        Forms!PH4Part1Report!InspCmb)) AND
       ((P1.DateInsp)>=Forms!PH4Part1Report!FirstDateTxt And 
        (P1.DateInsp)<=Forms!PH4Part1Report!LastDateTxt));

[green]Latest 20 Feb 12 15:29
**********************[/green]
SELECT CRQ.Name, 
       CRQ.RegNo, 
       CRQ.AreaCode, 
       CRQ.Address1, 
       CRQ.Address2, 
       CRQ.Address3, 
       CRQ.Town, 
       CRQ.County, 
       P1.Crecords, 
       P1.Dpassports, 
       P1.Etraceback, 
       P1.Fisolation, 
       P1.Girrigating, 
       P1.Action, 
       P1.Insp, 
       P1.DateInsp
FROM CustomerRecordsQuery AS CRQ
LEFT JOIN PH4Part1 AS P1
ON CRQ.RegNo = P1.UKNI
WHERE (((P1.Insp) Like IIf(Forms!PH4Part1Report!InspCmb="ALL",
                                                        "*",
                                                        Forms!PH4Part1Report!InspCmb)));[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[tamus121]

Still not getting this to work

Is there anthing wrong with this in the query for DateInsp

=IIf([Forms]![PH4Part1Report]![Option31]=True,>=[Forms]![PH4Part1Report]![FirstDateTxt] And <=[Forms]![PH4Part1Report]![LastDateTxt],<[Forms]![PH4Part1Report]![FirstDateTxt] And >[Forms]![PH4Part1Report]![LastDateTxt] Or Is Null)

Also tried it as the control source of the date field in a report and also blank. Both the query and report run but no records are found.
tamus

 

[MazeWorX]

IIF(something to evaluate,answerif true,answeriffalse)

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[dhookom]

tamus121,
You can't place conditions (=, <=, >=, Like, AND, etc) inside the IIf() except as the first argument to determine true or false.

Duane
Hook'D on Access
MS Access MVP