By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,829 Members | 673 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,829 IT Pros & Developers. It's quick & easy.

Passing parameters to query and report

P: 23
I have a button on a form that passes parameters to a query and opens a report. The code is listed below (someone helped me with this a long time ago and I kept it and reuse it). This works without a problem but what I need to do is also pass [dtmStartDate] and [dtmEndDate] which both get passed to [dtmSessionDate] in the query to the report that is opened.

Since both fields are being passed to the field [dtmSessionDate] in the query I don’t know what I need on my report or in the code to make it show up like the following: “From Period: [dtmStartDate] To Period: [dtmEndDate] on my report.

Can anyone help me? Thanks in advance.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdApplyFilter_Click()
  2.  
  3.     Dim strFilter As String
  4.     Dim dtmStartDate As Date
  5.     Dim dtmEndDate As Date
  6.  
  7.         'Location
  8.     If Not IsNull(Me.cboLocation) Then
  9.         strFilter = strFilter & " AND txtCity=""" & Me.cboLocation & """ "
  10.     End If
  11.  
  12.         'Supervisor Name
  13.     If Not IsNull(Me.cboSupervisorName) Then
  14.         strFilter = strFilter & " AND txtSupervisorName=""" & Me.cboSupervisorName & """ "
  15.     End If
  16.  
  17.         'Begin and End Dates
  18.     If Not (IsNull(Me.dtmStartDate) Or Me.dtmStartDate = "") Then
  19.     strFilter = strFilter & " AND (dtmSessionDate) Between #" & Format(Me.dtmStartDate, "mm/dd/yyyy") & "# AND #" & Format(Me.dtmEndDate, "mm/dd/yyyy") & "#"
  20.     End If
  21.  
  22.         'If the report is closed, open the report
  23.     If SysCmd(acSysCmdGetObjectState, acReport, "rptCallDetailsForAllAgentsBySupervisor") <> acObjStateOpen Then
  24.         DoCmd.OpenReport "rptCallDetailsForAllAgentsBySupervisor", acPreview
  25.     End If
  26.  
  27.         'if report was open, use filter
  28.     With Reports![rptCallDetailsForAllAgentsBySupervisor]
  29.         .Filter = Mid(strFilter, 6)
  30.         .FilterOn = True
  31.     End With
  32.  
  33. End Sub
Jul 23 '12 #1

✓ answered by Seth Schrock

I just fixed the date/time problem. In your original post, delete lines 4 and 5. What is happening is that Access is having trouble deciphering between the variable dtmStartDate and the control name dtmStartDate. Never name variables the same as a field name or a control name and you won't have this happen. It also makes it easier for you to know which you are dealing with when you can look at a line of code and know if what you are looking at is a variable, field name, or control name.

Share this Question
Share on Google+
12 Replies


Seth Schrock
Expert 2.5K+
P: 2,937
There is probably a cleaner way to do it, but this is what I would do:

Put a textbox on the report that will hold your From Period: [dtmStartDate] To Period: [dtmEndDate] and name it txtDatePeriod. Now put the following line of code in both the
Expand|Select|Wrap|Line Numbers
  1. 'If the report is closed, open the report
section and the
Expand|Select|Wrap|Line Numbers
  1. 'if report was open, use filter
section:

Expand|Select|Wrap|Line Numbers
  1. Me.txtDatePeriod = "From Period: " & dtmStartDate _
  2.                 & " to Period: " & dtmEndDate
So your last lines of code will look like this:

Expand|Select|Wrap|Line Numbers
  1. 'If the report is closed, open the report
  2. If SysCmd(acSysCmdGetObjectState, acReport, "rptCallDetailsForAllAgentsBySupervisor") <> acObjStateOpen Then
  3. DoCmd.OpenReport "rptCallDetailsForAllAgentsBySupervisor", acPreview
  4. Me.txtDatePeriod = "From Period: " & dtmStartDate _
  5.                 & " to Period: " & dtmEndDate
  6. End If
  7.  
  8. 'if report was open, use filter
  9. With Reports![rptCallDetailsForAllAgentsBySupervisor]
  10. .Filter = Mid(strFilter, 6)
  11. .FilterOn = True
  12. Me.txtDatePeriod = "From Period: " & dtmStartDate _
  13.                 & " to Period: " & dtmEndDate
  14. End With
Jul 23 '12 #2

P: 23
Thank you for helping me. I did what you said but am receiving a compile error on txtDatePeriod. Method or data member not found.

Here is the changed code.

Expand|Select|Wrap|Line Numbers
  1.         'If the report is closed, open the report
  2.     If SysCmd(acSysCmdGetObjectState, acReport, "rptCallDetailsForAllAgentsBySupervisor") <> acObjStateOpen Then
  3.         DoCmd.OpenReport "rptCallDetailsForAllAgentsBySupervisor", acPreview
  4.         Me.txtDatePeriod = "From Period: " & dtmStartDate _
  5.                 & " to Period: " & dtmEndDate
  6.     End If
  7.  
  8.         'if report was open, use filter
  9.     With Reports![rptCallDetailsForAllAgentsBySupervisor]
  10.         .Filter = Mid(strFilter, 6)
  11.         .FilterOn = True
  12.         Me.txtDatePeriod = "From Period: " & dtmStartDate _
  13.                 & " to Period: " & dtmEndDate
  14.     End With
Jul 24 '12 #3

Rabbit
Expert Mod 10K+
P: 12,357
Please use code tags when posting code. This is your second warning.

Seth's code is only an example of how to do it. It will only work if you have a control on your form named txtDatePeriod to store it. From the error message, it is clear that you don't have said control.
Jul 24 '12 #4

Seth Schrock
Expert 2.5K+
P: 2,937
(don't forget to use code tags when posting code. It makes it much easier to read.)

Okay. I thought that it would work since the report had already been opened in previous code. What you need to do now is to create a standard module (if using Access 2007 or 2010, go to the create tab and select Module from the far right group. Other versions of access I don't know where to go to create a module). In the Module, type the following code after the Option Compare Database line
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Public strDatePeriod As String
Change the Me.txtDatePeriod in my code to be strDatePeriod so that it looks like this in the form:
Expand|Select|Wrap|Line Numbers
  1. strDatePeriod = "From Period: " & dtmStartDate _
  2. & " to Period: " & dtmEndDate
You have now declared a public variable and assigned it the value that you want to be put into the textbox txtDatePeriod. Now we need to assign the value of strDatePeriod to txtDatePeriod. Create an On_Load event for your report. Add the following code to the event:
Expand|Select|Wrap|Line Numbers
  1. Me.txtDatePeriod = strDatePeriod
This should work. I don't know if there is a more efficient way to do it, but I just used a similar process to create a title for a report that I'm designing and it worked for me. If an expert would like to suggest a better way, I would love to hear it so that I can improve my database as well.
Jul 24 '12 #5

P: 23
It still didn't work. I'm not receiving an error but the text box is empty. The text box is an Unbound text box named txtDatePeriod and there isn't a Control Source. Did I do that wrong? Is there a way I can attache my database?

This makes sense I just don't know what I've done wrong.

Sorry about the tags.
Jul 24 '12 #6

Seth Schrock
Expert 2.5K+
P: 2,937
If you've double checked everything and it still isn't working, I don't know what is wrong. You should be able to compress the file and then attach it here and I will look to see what is missing. I just don't know where to tell you to look.
Jul 24 '12 #7

P: 23
I have attached the database in a zip file. There are many objects but the ones I am using are:

frmCallDetailsForAllAgentsBySupervisor - this is where the parameters are entered and passed to the query when the Apply Filter button is clicked. Where I also am adding the code to pass to the report. I choice I use from the two dropdowns are Toledo and Andrea Lipinski. The Start and End dates are 01/01/2012 and 05/01/2012.

rptCallDetailsForAllAgentsBySupervisor - this is where I have added the unbound text box called txtDatePeriod. Where I want to pass the period end and start dates.

The module is still called Module1.

Thank you so much for doing this.
Attached Files
File Type: zip Database.zip (525.4 KB, 111 views)
Jul 24 '12 #8

Seth Schrock
Expert 2.5K+
P: 2,937
Well, I just deleted the On_Load event and recreated it and it worked. However, I don't think that you want the results to be like this:

From Period: 12:00:00 AM to Period: 12:00:00 AM

I've tried using the Format(Me.dtmStartDate,"mm/dd/yyyy"), but that isn't working. I'll keep trying.

Update: I'm getting intermittent success on the txtDatePeriod getting populated. I'm working on a solution that will work always.
Jul 24 '12 #9

Seth Schrock
Expert 2.5K+
P: 2,937
Okay. I think that I have found a solution for the txtDatePeriod problem. Instead of putting
Expand|Select|Wrap|Line Numbers
  1. Me.txtDatePeriod = strDatePeriod
in the On_Load event of the report, put it in the On_Print event of the Header. I think that should work 100% of the time.

Back to working on the date formatting...
Jul 24 '12 #10

P: 23
I made the change to the header and it worked great. It was the time instead of the date but I'm so glad it showed something. Thank you!!
Jul 24 '12 #11

Seth Schrock
Expert 2.5K+
P: 2,937
I just fixed the date/time problem. In your original post, delete lines 4 and 5. What is happening is that Access is having trouble deciphering between the variable dtmStartDate and the control name dtmStartDate. Never name variables the same as a field name or a control name and you won't have this happen. It also makes it easier for you to know which you are dealing with when you can look at a line of code and know if what you are looking at is a variable, field name, or control name.
Jul 24 '12 #12

P: 23
It worked!! Thank you so much for all of your help, you did so much. I didn't realize that was wrong, so thank you for teaching me too.

For others looking at this it was all helpful, not just the one I marked as the best answer.
Jul 25 '12 #13

Post your reply

Sign in to post your reply or Sign up for a free account.