Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

change query output depending on option buttonHelpful Member! 

tamus121 (TechnicalUser) (OP)
19 Feb 12 20:08
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
 
Helpful Member!  dhookom (Programmer)
19 Feb 12 22:36
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 (Programmer)
19 Feb 12 22:56
How are ya tamus121 . . .

Post the SQL of the query ...

See Ya! .  .  .  .  .  .

Be sure to see FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?  Worthy Reading! thumbsup2
Also FAQ181-2886: How can I maximize my chances of getting an answer?  Worthy Reading! thumbsup2

tamus121 (TechnicalUser) (OP)
20 Feb 12 8:41
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?

CODE

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

CODE

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 (TechnicalUser) (OP)
20 Feb 12 15:29
ok I am thinking maybe I have got it with this

CODE

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 (Programmer)
20 Feb 12 21:59
For easier viewing of the SQL:

CODE

Post Origination 19 Feb 12 20:08
********************************

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

Latest 20 Feb 12 15:29
**********************

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

See Ya! .  .  .  .  .  .

Be sure to see FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?  Worthy Reading! thumbsup2
Also FAQ181-2886: How can I maximize my chances of getting an answer?  Worthy Reading! thumbsup2

tamus121 (TechnicalUser) (OP)
22 Feb 12 6:27
Still not getting this to work

Is there anthing wrong with this in the query for DateInsp

CODE

=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 (IS/IT--Management)
22 Feb 12 9:22
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 (Programmer)
22 Feb 12 11:37
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

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close