Solved

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

Posted on 2014-07-30
9
172 Views
Last Modified: 2014-07-31
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
Comment
Question by:Patrick O'Dea
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 58
ID: 40228950
<<(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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40228952
"How do I change my SQL to reflect this check box?"

remove the Where clause part that filters the Agents from the SQL
0
 
LVL 3

Expert Comment

by:Thomas Qvidahl
ID: 40228957
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40228960
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
 
LVL 38

Expert Comment

by:PatHartman
ID: 40229250
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
 

Author Comment

by:Patrick O'Dea
ID: 40231739
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 40231884
test this
created a new query for all agents
EELOadAgents3107.accdb
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 40231926
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
 

Author Closing Comment

by:Patrick O'Dea
ID: 40232546
Thanks Rey,  that works well and more importantly I understand it !
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question