• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 334
  • Last Modified:

I need to add to the script below a function that would ask for a date range using the field "DraftDate" which is a field in the tluFundsImport table.

The code below is a script attached to a button that pulls up a report, the report is based on query "qryRPT2".
In the query I tried to set the criteria "Between[start date]And[end date] under the DraftDate field criteria, and also for the  field criteria for "PlanType" is set to 92.  It gives me the correct "PlanType", but it does not ask for the date range.  This is what I am trying to accomplish.


Private Sub Command10_Click()
Dim dbs As Database
Dim rst As DAO.Recordset
Dim strReportName, strFileName As String
Dim strSQL As String
Dim rpt As Report

'On Error GoTo ProcError

strReportName = "rptMasterReport921"
strSQL = "SELECT tblProviderAccount.[Reference], tluFundsImport.DraftDate " _
    & "FROM tblProviderAccount INNER JOIN tluFundsImport ON tblProviderAccount.ProvID = tluFundsImport.ProvID " _
    & "GROUP BY tblProviderAccount.[Reference], tluFundsImport.DraftDate "


'strSQL = "SELECT tluFundsImport.ProvID, tblProviderAccount.ProviderName, " _
'                        & "tluFundsImport.DraftDate " _
'            & "FROM tblProviderAccount INNER JOIN tluFundsImport " _
'            & "ON tblProviderAccount.ProvID = tluFundsImport.ProvID " _
'            & "GROUP BY tluFundsImport.ProvID, tblProviderAccount.ProviderName, " _
'                            & "tluFundsImport.DraftDate;"
Debug.Print strSQL
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
rst.MoveFirst
'rst.MoveLast
'rst.MoveFirst
'MsgBox rst.RecordCount
Do While Not rst.EOF
        strFileName = "C:\tmp\" _
                    & rst![Reference] & " " & "(Plan 92) " & Format(rst!DraftDate, "mm_dd_yyyy") & ".pdf"
        Debug.Print strFileName
        StrFilter = "[Reference] = '" & rst![Reference] & "'"
       
        If DCount("*", "qryRPT2", StrFilter) > 0 Then   ' Add this line
       
        DoCmd.OpenReport strReportName, acViewPreview, "qryRPT1", StrFilter, acHidden
        DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, strFileName
        DoCmd.Close acReport, strReportName
       
        End If        'Add this
       
        rst.MoveNext
Loop


ProcExit:
    If Not rst Is Nothing Then
        rst.Close
        Set rst = Nothing
    End If
    Exit Sub
ProcError:
    MsgBox Err.Number & vbCrLf & Err.Description, vbOKOnly, "Error exporting file"
    Debug.Print "Error exporting file", Err.Number, Err.Description
    Resume ProcExit


End Sub

Private Sub Command12_Click()
Dim dbs As Database
Dim rst As DAO.Recordset
Dim strReportName, strFileName As String
Dim strSQL As String
Dim rpt As Report

'On Error GoTo ProcError

strReportName = "rptMasterReport93"
strSQL = "SELECT tblProviderAccount.[Reference], tluFundsImport.DraftDate " _
    & "FROM tblProviderAccount INNER JOIN tluFundsImport ON tblProviderAccount.ProvID = tluFundsImport.ProvID " _
    & "GROUP BY tblProviderAccount.[Reference], tluFundsImport.DraftDate "


'strSQL = "SELECT tluFundsImport.ProvID, tblProviderAccount.ProviderName, " _
'                        & "tluFundsImport.DraftDate " _
'            & "FROM tblProviderAccount INNER JOIN tluFundsImport " _
'            & "ON tblProviderAccount.ProvID = tluFundsImport.ProvID " _
'            & "GROUP BY tluFundsImport.ProvID, tblProviderAccount.ProviderName, " _
'                            & "tluFundsImport.DraftDate;"
Debug.Print strSQL
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
rst.MoveFirst
'rst.MoveLast
'rst.MoveFirst
'MsgBox rst.RecordCount
Do While Not rst.EOF
        strFileName = "C:\tmp\" _
                    & rst![Reference] & " " & "(Plan 93) " & Format(rst!DraftDate, "mm_dd_yyyy") & ".pdf"
        Debug.Print strFileName
        StrFilter = "[Reference] = '" & rst![Reference] & "'"
       
        If DCount("*", "qryRPT3", StrFilter) > 0 Then   ' Add this line
       
        DoCmd.OpenReport strReportName, acViewPreview, "qryRPT1", StrFilter, acHidden
        DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, strFileName
        DoCmd.Close acReport, strReportName
       
        End If        'Add this
       
        rst.MoveNext
Loop


ProcExit:
    If Not rst Is Nothing Then
        rst.Close
        Set rst = Nothing
    End If
    Exit Sub
ProcError:
    MsgBox Err.Number & vbCrLf & Err.Description, vbOKOnly, "Error exporting file"
    Debug.Print "Error exporting file", Err.Number, Err.Description
    Resume ProcExit
0
Delores_C
Asked:
Delores_C
  • 6
  • 4
  • 2
  • +1
4 Solutions
 
peter57rCommented:
I can't see anywhere where you are testing a date.

But you will not get parameter boxes popping up from a query used to create a recordset.
If you want to use run-time parameters then you must ask for them explicitly in your code.

You can do this by using an InputBox () for each value or you can (design and) open a pop-up form to get the values you want.

You then build the values into the sql string ...

"...where somedatefield = #" & format(forms!popupformname!controlname, "yyyy-mm-dd") & "# and....."
0
 
Dale FyeCommented:
Do you want this range of dates to apply to each of the reports that you are running within your Do Loop, or do you want to enter this information for each report.  Assuming that you only want to enter this range once, I would recommend that you put two textboxes on your form (near the button that starts the reports).  Then, modify this line:

        StrFilter = "[Reference] = '" & rst![Reference] & "'"

to something like:

        StrFilter = "[Reference] = '" & rst![Reference] & "' AND " _
                      & "[DateFieldName] BETWEEN #" & me.txt_StartDate & "# AND #" & me.txt_EndDate & "#"

HTH
Dale
         
0
 
Delores_CAuthor Commented:
I added the script as advise from previous suggestion, and added the two unbound text boxes.  

What code should be attached to the boxes and where.  

Also, the Reference field is a text field, that is the reason for adding the (& "'"), to prevent it from producing blank reports if there were no records.  See the code:

If DCount("*", "qryRPT3", StrFilter) > 0 Then  

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Dale FyeCommented:
You don't need to "attach" anything to those textboxes, although you should probably add a couple of error checks to your code to ensure that those textboxes are filled in.  Can you post the code you have now?  I'll add a couple of lines of error handling and pass it back to you.
0
 
Delores_CAuthor Commented:
A sample of the database with the text boxes and code is attached.  

When you run the command from the main menu form, I get an " Compile Error, Method or Data member not found"
message
Copy-for-Analyze-Test-EFT-10-12-.accdb
0
 
mbizupCommented:
<<StrFilter = "[Reference] = '" & rst![Reference] & "' AND " _
                      & "[DateFieldName] BETWEEN #" & me.txt_StartDate & "# AND #" & me.txt_EndDate & "#"  >>


When we suggest code for you to add to your databases, you almost always have to ensure that you have modified the code to use your own field names, or modified your database to use the suggested names.

You have not done that here, and that is why you are getting this error.

You need to make sure that your textboxes are named txt_StartDate and txt_EndDate, respectively. That will alleviate your current error.

You also need to replace "DateFieldName" in the code provided with the actual name of your date field (DraftDate)
0
 
Delores_CAuthor Commented:
I actually copied your code, replacing DateFieldName with DraftDate and named each text box.  When I ask for records between the same period, example 9/23/11 thru 9/23/11, it is giving me the date in between, when I may only need records with date 9/23/2011 only.  Should I just set it up for 1 day and not in between a date range?  If yes, what should I change in the script?

a copy of the database is attached.
Copy-for-Analyze-Test-EFT-Revise.accdb
0
 
mbizupCommented:
There's a varietty of methods for doing that.  One suggestion is to have option buttons (for example) that designate whehter the user wants to enter an "Exact Date"  or "Date Range" and have the End Date textbox enabled or disabled accordingly.

You would use code in the After Update event of the Option Group would be something like this:

If Me.optGrp = 1 then  ' User wants to enter an exact date
     me.txt_EndDate.enabled = false
Else
     me.txt_EndDate.enabled = true
end if


And the code for your criteria would be along these lines:

If Me.optGrp = 1 Then
         StrFilter = "[Reference] = '" & rst![Reference] & "' AND " _
                      & "[DraftDate] = #" & me.txt_StartDate & "#"
Else
        StrFilter = "[Reference] = '" & rst![Reference] & "' AND " _
                      & "[DateFieldName] BETWEEN #" & me.txt_StartDate & "# AND #" & me.txt_EndDate
End IF


Copy-for-Analyze-Test-EFT-Revise.accdb
0
 
Delores_CAuthor Commented:
After running the revised attached file, I tried both.  They seems to work until I look at the pdf filename.


It is important that the DraftDate appears in the pdf filename.  But, it continued to put a different date in the filename, a date that do not exist in the DraftDate of the records.  
0
 
mbizupCommented:
Okay - the DraftDate does not appear in the reported records, but it does appear in tluFundsImport, which your code is looping through.  As the code runs through the records in that table, the DraftDate in your filename changes according to the current record in tluFundsImport.

If you want a constant DraftDate (the one chosen in the filter), you need to modify this line:

<<            strFileName = "C:\Users\Eipc\" _
                    & rst![Reference] & " " & "(Plan 92) " & Format(rst!DraftDate, "mm_dd_yyyy") & ".pdf">>

rst!DraftDate is taking on the various date values you see in tluFundsImport.

You want to replace rst!DraftDate with the date that is chosen on your form.

ie:
           me.txt_StartDate
0
 
Delores_CAuthor Commented:
I am still getting an error after changing as listed below:

 strFileName = "C:\Users\Eipc\" _
                    & rst![Reference] & " " & "(Plan 92) " & Format(rst!txt_StartDate, "mm_dd_yyyy") & ".pdf"
        Debug.Print strFileName
       StrFilter = "[Reference] = '" & rst![Reference] & "' AND " _
                      & "[DraftDate] BETWEEN #" & Me.txt_StartDate & "# AND #" & Me.txt_EndDate & "#"

Please let me know what I am doing wrong.  Thanks for your patience...
0
 
mbizupCommented:
<< strFileName = "C:\Users\Eipc\" _
                    & rst![Reference] & " " & "(Plan 92) " & Format(rst!txt_StartDate, "mm_dd_yyyy") & ".pdf"  >>


You're prefixing txt_StartDate with the name of your recordset (rst).  Since it is a control on your form, not a field in your recordset, you should be prefixing it with the form reference (Me.):

me.txt_StartDate
0
 
Delores_CAuthor Commented:
Worked great.  Patience in assisting was really appreciated.  Thanks again!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now