Solved

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

Posted on 2014-07-30
9
164 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 119

Expert Comment

by:Rey Obrero
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
 
LVL 119

Expert Comment

by:Rey Obrero
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 34

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 119

Accepted Solution

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

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

760 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now