Solved

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

Posted on 2014-07-30
9
171 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 37

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 37

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

729 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