?
Solved

How to use a SQL Statement with more than one variable from a form

Posted on 2011-09-19
4
Medium Priority
?
215 Views
Last Modified: 2012-06-21
I have a report where the record source is a SQL statement that uses variables from a form.

The criteria for the SQL Statement/Query of the Report is using the choices from the combo boxes and/or text boxes on the form (frmReports).

I started out with just 4 (cboReportCateg, cboCategSelect, txtBeginDT and txtEndDT) combo and text box choices as the criteria, and this was working fine.  I then added a fifth combo box (cboDept) and for some reason it doesn't recognize the choice in the 5th combo box, so it is not pulling the correct data (so if the user makes a selection in the 3 combo boxes cboReportCateg, cboCategSelect, cboDept (and they may or may not choose dates, the data is pulling everything, and not just the data that matches the Dept that is selected.

I have posted the report's SQL statement in the code box below.  I have the OR statement with the txtBeginDT and txtEndDT criteria as NULL because I want the users to be able to run the report without any dates, if necessary.  Otherwise I allow for them to put in dates if they want to also.

Please review the SQL statement of the report and let me know what I am doing wrong.

Thanks,

gdunn59
SELECT
tblEmployee_Audits.Employee,
tblEmployee_Audits.Manager_Name,
tblEmployee_Audits.Quality_Review_Date,
tblEmployee_Audits.Auditor_Name,
tblEmployee_Audits.Audit_Type,
tblEmployee_Audits.Department,
tblEmployee_Audits.Region,
tblEmployee_Audits.System,
tblEmployee_Audits.InquiryNum,
tblEmployee_Audits.Audit_Date,
tblEmployee_Audits.Audit_Notes
FROM tblEmployee_Audits
WHERE (((tblEmployee_Audits.Employee)=Forms!frmReports!cboCategSelect)
And
((tblEmployee_Audits.Quality_Review_Date) Between Forms!frmReports!txtBeginDT And Forms!frmReports!txtEndDT))
And (((tblEmployee_Audits.Department)=Forms!frmReports!cboDept))
Or 
(((tblEmployee_Audits.Employee)=Forms!frmReports!cboCategSelect))
And 
(((tblEmployee_Audits.Department)=Forms!frmReports!cboDept)
And
((Forms!frmReports!txtBeginDT) Is Null)
And
((Forms!frmReports!txtEndDT) Is Null));

Open in new window

0
Comment
Question by:gdunn59
[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
  • 2
4 Comments
 
LVL 30

Accepted Solution

by:
hnasr earned 2000 total points
ID: 36563457
Try to reduce the query using few columns.

Select f1, f2 ....

Where ((True) AND (True)) OR ((True) AND (True))



The problem can be in the Where clause.
Revise your statement:
Where () OR ()
Where (() AND ()) OR (() AND ())
Fill in the inner brackets.
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 36563807
Also, if you may have NULLs in columns, move the test for NULL so that it is PRIOR to the tests that expect an actual value because testing for a vlue first can cause issues.
0
 
LVL 1

Author Comment

by:gdunn59
ID: 36584086
I tied hnasr's suggestion and that didn't work.  Not sure if I'm just not doing the SQL Statement/Query correct or not.

I have added to the Code section my SQL Statement/Query.

Thanks,
gdunn59

SELECT tblEmployee_Audits.Employee, tblEmployee_Audits.Manager_Name, tblEmployee_Audits.Quality_Review_Date, tblEmployee_Audits.Auditor_Name, tblEmployee_Audits.Audit_Type, tblEmployee_Audits.Department, tblEmployee_Audits.Region, tblEmployee_Audits.System, tblEmployee_Audits.InquiryNum, tblEmployee_Audits.Audit_Date, tblEmployee_Audits.Audit_Notes
FROM tblEmployee_Audits
WHERE (((tblEmployee_Audits.Employee)=[Forms]![frmReports]![cboCategSelect]) AND ((tblEmployee_Audits.Quality_Review_Date) Between [Forms]![frmReports]![txtBeginDT] And [Forms]![frmReports]![txtEndDT]) AND ((tblEmployee_Audits.Department)=[Forms]![frmReports]![cboDept])) OR (((tblEmployee_Audits.Employee)=[Forms]![frmReports]![cboCategSelect]) AND ((tblEmployee_Audits.Department)=[Forms]![frmReports]![cboDept]) AND (([Forms]![frmReports]![txtBeginDT]) Is Null) AND (([Forms]![frmReports]![txtEndDT]) Is Null));

Open in new window

0
 
LVL 30

Expert Comment

by:hnasr
ID: 36584182
Try to attach a simple database with relevant test data.
Show the expected output as a table.
0

Featured Post

Stack Overflow Podcast - Developer Story

Welcome to the Stack Overflow podcast recorded Thursday July 20 at Stack Overflow Headquearters in NYC. Your hosts today are podcast regulars Jay Hanlon, David Fullerton, and Ilana Yitzhaki, plus the quite irregular Matt Sherman (Stack Overflow Engineering Manager extraordinaire)

Question has a verified solution.

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

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.
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
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: …

801 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