Solved

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

Posted on 2014-07-30
9
169 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
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 57
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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 36

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 36

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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 …

831 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