×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Passing parameter query to report

Passing parameter query to report

Passing parameter query to report

(OP)
Hi,

I’ve a requirement whereby multiple users run a report based on their areas interests. For this, I created a form so users can enter a start/end date, and select a value from a dropdown and click run query etc. What I’m trying to accomplish is the following:

#1. If a user selects start date, end date, and a value from a dropdown and clicks ‘Run Query’ button, then the report should limit the result to date intervals and value selected from the dropdowns.

#2. If a user doesn’t restrict date intervals, and/or doesn’t make any selection from a dropdown and clicks ‘Run Query’ button, then the report should display everything.

I created a query/report and a form for this and I’ve the following in my qbe:

=[Forms]! [MyFormName]! [MyControlName1]
=[Forms]! [MyFormName]! [MyControlName2]
etc.

[Forms]![MyFormName]![txtBeginDate1] And [Forms]![ MyFormName]![txtEndDate1]
[Forms]![MyFormName]![txtBeginDate2] And [Forms]![ MyFormName]![txtEndDate2]
etc.

DoCmd.OpenQuery "MyReportName", acViewPreview

So far, only the first control is working. When I select a value from say MyControlName1 and begin & end date intervals from the other query nothing happens.

Can you please assist?

TIA


OCM

RE: Passing parameter query to report

I'm not sure about all the controls since you seem to have names for six controls.

I would not place the filtering in the query. Consider moving this to the code. Ideally every filter control can be added to the code.

CODE --> vba

Dim strWhere as String
strWhere = " 1=1 "
If Not IsNull(Me.txtBeginDate1) Then
    strWhere = strWhere & " AND [YourDateField] >= #" & Me.txtBeginDate1 & "# "
End If
If Not IsNull(Me.txtEndDate1) Then
    strWhere = strWhere & " AND [YourDateField] <= #" & Me.txtBeginEnd1 & "# "
End If
Debug.Print strWhere   'show me the value
DoCmd.OpenQuery "MyReportName", acViewPreview, , strWhere 

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

RE: Passing parameter query to report

(OP)
Dhookom, thanks for the reply post.

The report works fine for all my txtControls (start date/end date. But, all my cboControls (dropdowns) not giving me the expected values.

Following are few examples:

1. Assign Date: entering (4/1/17 – 4/30/17) for begin/end date works fine. For other date controls as well. (e.g. Referral Date, Close Date etc.)

2. If I select any value form the dropdown, e.g. Status = Open, then the report returns everything. It doesn’t limit to the selection.

3. If I want to know what status = open for referral date: (4/1/17 4/30/17), I’ll get everything e.g. status = open, closed etc.

Below is the code I used. As you suggested I removed the filtering from the query and below is the code I used:

CODE -->

Private Sub cmdRunQuery_Click()
Dim strWhere As String
strWhere = " 1=1 "
If Not IsNull(Me.txtBeginRefDT) Then
    strWhere = strWhere & " AND [REFDT] >= #" & txtBeginRefDT & "# "
End If
If Not IsNull(Me.txtEndRefDT) Then
    strWhere = strWhere & " AND [REFDT] <= #" & txtEndRefDT & "# "
End If
If Not IsNull(Me.txtBeginAssignDT) Then
    strWhere = strWhere & " AND [ASSIGNDT] >= #" & txtBeginAssignDT & "# "
End If
If Not IsNull(Me.txtEndAssignDT) Then
    strWhere = strWhere & " AND [ASSIGNDT] <= #" & txtEndAssignDT & "# "
End If
 If (Me.cboStatus) = False Then
      strWhere = strWhere & (" AND [STATDESC] = " + Me.cboStatus)
 End If
 If Not IsNull(Me.txtBeginCloseDT) Then
    strWhere = strWhere & " AND [CLOSEDT] >= #" & txtBeginCloseDT & "# "
 End If
If Not IsNull(Me.txtEndCloseDT) Then
    strWhere = strWhere & " AND [CLOSEDT] <= #" & txtEndCloseDT & "# "
End If
  If (Me.cboCloseReason) = False Then
     strWhere = strWhere & ("AND [CLSREASON] = " + Me.cboCloseReason)
   End If
If Not IsNull(Me.txtBeginProsReferredDT) Then
    strWhere = strWhere & " AND [PROSDT] >= #" & txtBeginProsReferredDT & "# "
End If
If Not IsNull(Me.txtEndProsReferredDT) Then
    strWhere = strWhere & " AND [PROSDT] <= #" & txtEndProsReferredDT & "# "
End If
  If (Me.cboRefAgency) = False Then
      strWhere = strWhere & (" AND [AGENCYNM] = " + Me.cboRefAgency)
  End If
If Not IsNull(Me.txtBeginAcceptDT) Then
    strWhere = strWhere & " AND [PROSACCPTDT] >= #" & txtBeginAcceptDT & "# "
End If
If Not IsNull(Me.txtEndAcceptDT) Then
    strWhere = strWhere & " AND [PROSACCPTDT] <= #" & txtEndAcceptDT & "# "
End If
If Not IsNull(Me.txtBeginDeclineDT) Then
    strWhere = strWhere & " AND [PROSREJDT] >= #" & txtBeginDeclineDT & "# "
End If
If Not IsNull(Me.txtEndDeclineDT) Then
    strWhere = strWhere & " AND [PROSREJDT] <= #" & txtEndDeclineDT & "# "
End If

Debug.Print strWhere   'show me the value

DoCmd.OpenReport "rpt_All_Cases", acViewPreview, , strWhere

End Sub 

TIA

OCM

RE: Passing parameter query to report

I'm not sure why you added any of the ()s.
What are the data types of the non-date fields?
What do you see in the debug window. Press Ctrl+G to see the strWhere value.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

RE: Passing parameter query to report

(OP)

Thanks dhookom

Quote:

I'm not sure why you added any of the ()s.
I did remove the ()s. But, even if I selected a value form the dropdown, I still get everything.

Quote:

What are the data types of the non-date fields?
I’ve the following non-date fields that are dropdowns:
1. Status (text)
Values: open, open at location1, open at location2, pending, close

2. Close Reason (numeric)
values: 1, 2, 3, 4

3. Agency (text)
values: MFC, DHC, SAC

Quote:

What do you see in the debug window Press Ctrl+G to see the strWhere value.
When I press Ctrl+G while in vba window, nothing happens.

TIA


OCM

RE: Passing parameter query to report

Pressing Ctrl+G after running this code will display the results of the debug.print.

I don't know why if status can be "open, open at location1, open at location2, pending, close" you used:

CODE --> vba

If (Me.cboStatus) = False Then 


Since STATDWSC is text you must use quote delimiters and use & to concatenate text rather than +.

CODE --> vba

If Not IsNull(Me.cboStatus) Then
      strWhere = strWhere & " AND [STATDESC] = """ & Me.cboStatus & """ "
End If 

Provide the results of debug.print so you can troubleshoot.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

RE: Passing parameter query to report

(OP)
Thank you, I corrected the syntax and it works beautifully. Couple of questions regarding my other numeric data type (CLSREASON):
1. Do I need to surround it by # sign like below?

CODE -->

If Not IsNull(Me.cboCloseReason) Then
     strWhere = strWhere & " AND [CLSREASON] = #" & Me.cboCloseReason & “#”
   End If 

2. When I select its unbound control > properties > Row Source and run the query, the column displays repetitive values e.g.
2,1,1,1,2,2,3,3,4,2,2,1,1,4,4,1 etc. instead of only 1,2,3,4

Also, if I want the report in excel format, do I add another button to accomplish this, or this can be incorporated to the existing syntax?

TIA,

OCM

RE: Passing parameter query to report

Numeric data requires no delimiter:

CODE --> vba

If Not IsNull(Me.cboCloseReason) Then
     strWhere = strWhere & " AND [CLSREASON] = " & Me.cboCloseReason
End If 


If you are referencing cboCloseReason I think you should provide the Row Source.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

RE: Passing parameter query to report

(OP)
dhookom,

Thanks. though my cboCloseReason shows repetitive values in the dropdown, when I made a selection, the report limits the result to my selection. It's working as planned. How do I go about providing the Row Source?

TIA,

OCM

RE: Passing parameter query to report

The Row Source is a property of the combo box. You should be able to copy and paste it in a reply.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

RE: Passing parameter query to report

(OP)
Following is what is in the property...

SELECT tblPICTSDATA.CLSREASON FROM tblPICTSDATA;

TIA

OCM

RE: Passing parameter query to report

What do you see when you view this in datasheet view? If you have duplicates, you should try change the Row Source to:

CODE --> sql

SELECT DISTINCT tblPICTSDATA.CLSREASON FROM tblPICTSDATA ORDER BY 1; 

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

RE: Passing parameter query to report

(OP)
dhookom,
Thank you, the query now displays unique values (along with blank) as follows:

Blank
1
2
3
4

Just curious,
1. can the above be modified to limit only to values (no blank)?.
2. better yet, how do I go about adding blank to my other two drop-downs?

I personally think having blank as an option is a better way of clearing what previously selected (instead of highlight/delete).

TIA,


OCM

RE: Passing parameter query to report

(OP)

I’ve not yet figured out to how to remove blank from the drop down list, but the following will add a blank to the dropdown:

CODE -->

SELECT tblSTATUS.STATDESC FROM tblSTATUS 
UNION 
SELECT Null  FROM tblSTATUS ORDER BY 1; 


OCM

RE: Passing parameter query to report

To remove the null from the query, just add a where clause:

CODE --> sql

SELECT DISTINCT tblPICTSDATA.CLSREASON 
FROM tblPICTSDATA 
WHERE tblPICTSDATA.CLSREASON Is Not Null 
ORDER BY 1; 

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

RE: Passing parameter query to report

(OP)

dhookom, many thanks for your guidance. It works beautifully.

Please let me know if I need to start a separate topic on the following:

Exporting to the result to Excel (excel 2016)
1. I attempted to use the wizard, but I did not see any option to export data to excel
2. I created a button using the following syntax:

CODE -->

DoCmd.OutputTo acOutputReport, "MyReport", acFormatXLS, "C:\Users\UserName\Desktop\Excel Reports\MyReport - " & Format(Date, "yyyymmdd") & ".xls" 

The exported excel file doesn’t reflect the selection made when running the query (gives me everything) and the font of the excel file was grayed out.

Also, every time I export to excel, the old one will be overwritten by the latest one.

TIA,

OCM

RE: Passing parameter query to report

please start a new thread and mark this one as answered if you haven't already.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close