• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 178
  • Last Modified:

Change my SQL to show ALL agents (i.e. salesman)

Hi,
See http://screencast.com/t/fn0Sc83Qc

This is a screen which controls my report.

I have the option of "Printing sales for a single 'agent'".   This works fine!

However, I also have a check box called "Print All Agents".  
If this is ticked that I want to show all data (regardless of agent).

How do I change my SQL to reflect this check box?

(I am not showing my SQL in the post as it is complex and might confuse the issue!)
0
Patrick O'Dea
Asked:
Patrick O'Dea
  • 3
  • 2
  • 2
  • +2
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<(I am not showing my SQL in the post as it is complex and might confuse the issue!)>>

  Well it boils down to how are you restricting the report to a single agent?

   You can be doing that in a number of ways...

1. SQL Statement
2. WHERE criteria when opening report
3. Filter when opening the report
4. In code in the reports OnOpen statement

etc.

Jim.
0
 
Rey Obrero (Capricorn1)Commented:
"How do I change my SQL to reflect this check box?"

remove the Where clause part that filters the Agents from the SQL
0
 
Thomas QvidahlDeveloperCommented:
I would think your SQL includes a WHERE statement with the ID of the agent you have chosen.
So if you need all agents, just omit the WHERE statement.

The WHERE statement filters out returned records, so by not including it, SQL will return all matching records.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rey Obrero (Capricorn1)Commented:
use two sql statement
1. strSQL= 'select * from... where Agent=<criteria>"
use the sql statement below to print all
2. strSQL= 'select * from tableX"
0
 
PatHartmanCommented:
Change the Where clause to something like:

Where (SalesmanID = Forms!yourform!cboSalesmanID OR Forms!yourform!chkSelectAll = True) AND ...

You didn't post the SQL so I'm not sure how to integrate this but what you are doing is turning the criteria for the salesman from a simple condition to a compound one.  If you have other conditions in the query, it is IMPERATIVE that you enclose this compound condition in parentheses.
0
 
Patrick O'DeaAuthor Commented:
Thanks for contributions.
I understand the principle here but am struggling to implement the solution!

See attached.
Run frmDateRangeForReports  (uses qryBookingsPerWeekPerAgent)

Ignore dates,

Note the checkbox (yellow) "ShowAllAgents".     If "ticked" here then print all records.

The SQL is qryBookingsPerWeekPerAgent

How do I change this SQL "Print ALL Records" when checkbox is ticked.

Hopefully this is clear!


Thanks!  3:30pm here.   I am going for a walk by the sea now with 3  bored 11-year old boys!  (My son plus his friends)
EELOadAgents3107.accdb
0
 
Rey Obrero (Capricorn1)Commented:
test this
created a new query for all agents
EELOadAgents3107.accdb
0
 
PatHartmanCommented:
I can't download either copy.  They open as .htm instead.  Why not post the where clause of the SQL where you tried my solution.
0
 
Patrick O'DeaAuthor Commented:
Thanks Rey,  that works well and more importantly I understand it !
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now