Passing parameter query to report 1

[OCM]

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

 

[dhookom]

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.

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   [COLOR=#4E9A06]'show me the value[/color]
DoCmd.OpenQuery "MyReportName", acViewPreview, , strWhere

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

 

[OCM]

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:

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

 

[dhookom]

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

 

[OCM]

Thanks dhookom

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.

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

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

 

[dhookom]

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:

If (Me.cboStatus) = False Then

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

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

 

[OCM]

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?

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

 

[dhookom]

Numeric data requires no delimiter:

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

 

[OCM]

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

 

[dhookom]

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

 

[OCM]

Following is what is in the property...

SELECT tblPICTSDATA.CLSREASON FROM tblPICTSDATA;

TIA

OCM

 

[dhookom]

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

SELECT DISTINCT tblPICTSDATA.CLSREASON FROM tblPICTSDATA ORDER BY 1;

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

 

[OCM]

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

 

[OCM]

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:

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

OCM

 

[dhookom]

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

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

 

[OCM]

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:

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

 

[dhookom]

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