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.

LINK TO THIS FORUM!

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

Tek-Tips LinkedIn® Group

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site
Partner Button
(Download This Button Today!)

Feedback

"...Thanks for a great forum. My problem was answered just by scrolling through previously solved problems. Great service!!..."
More...

Geography

Where in the world do Tek-Tips members come from?
Click Here To Find Out!

Tek-Tips Shirts!

Get Tek-Tips Swag
Get your
Tek-Tips Forums
SWAG here!
Home > Forums > Programmers > DBMS Packages > Microsoft: Access Forms Forum

change query output depending on option buttonHelpful Member! 

thread702-1675472
Read More Threads Like This One
tamus121 (TechnicalUser)
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)
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)
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)
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

Read More Threads Like This One

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!

Back To Forum

Back To Microsoft: Access Forms

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