If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Please help w/form collecting parameters for Report
I am trying to create a form that will collect parameters for a Report. In
searching for help on this I came across Allen Brownes Access Tip, "Limiting a Report to a Date Range". However, the code below works fine for one date. My report has 4 date parameters that need to be filled by the form. How can this be achieved using Mr. Browne's code? I have an Appt date, CareStart Date and CareEndDate, HospStart Date and HospEndDate and ServiceStartDate and ServiceEndDate. Any help will be greatly appreciated. Thank you. Private Sub OK_Click() Dim strReport As String 'Name of report to open. Dim strField As String 'Name of your date field. Dim strWhere As String 'Where condition for OpenReport. Const conDateFormat = "\#mm\/dd\/yyyy\#" strReport = "rpt PPC Fax Cover Sheet" strField = "text109" If IsNull(Me.txtstartdate) Then If Not IsNull(Me.txtEndDate) Then 'End date, but no start. strWhere = strField & " = " & Format(Me.txtEndDate, conDateFormat) End If Else If IsNull(Me.txtEndDate) Then 'Start date, but no End. strWhere = strField & " = " & Format(Me.txtstartdate, conDateFormat) Else 'Both start and end dates. strWhere = strField & " Between " & Format(Me.txtstartdate, conDateFormat) _ & " And " & Format(Me.txtEndDate, conDateFormat) End If End If ' Debug.Print strWhere 'For debugging purposes only. DoCmd.OpenReport strReport, acViewPreview, , strWhere End Sub |
#2
|
|||
|
|||
Please help w/form collecting parameters for Report
"TotallyConfused" wrote in
message ... strField = "text109" You don't really have a date field named the above? (remeber, the above has NO relationship to the name of a contorl on your form...we talking about FIELD NAMES IN THE TABLE!!! Dim strReport As String 'Name of report to open. Dim strWhere As String 'Where condition for OpenReport. Const conDateFormat = "\#mm\/dd\/yyyy\#" If IsNull(Me.txtStartDate) = False Then strWhere = "InvoiceDate = " & Format(Me.txtStartDate, conDateFormat)" End If If IsNull(Me.txtEndDate) = False Then If strWhere "" Then strWhere = strWhere & " and " End If strWhere = "InvoiceDate = " & Format(Me.txtEndDate, conDateFormat) End If Debug.Print strWhere 'For debugging purposes only. DoCmd.OpenReport strReport, acViewPreview, , strWhere The above will do a date range for ONE field, in this example InvoiceDate You also not mentioned what text box controls on your form are to filter what fields in the table/report. For example, you have on your form, obviously two text boxes for a date range...but fail to mention what date field that me.txtStartDate is to filter? (ie: what field - Appt date, CareStart Date and CareEndDate, HospStart Date and HospEndDate and ServiceStartDate and ServiceEndDate.). Is the simple entering of ONE date into me.txtStartDate to filter on ALL of the above fields? You made no connection between what controls on your form are to be filtered by what controls. The above code snip can be repeated over and over for each set of text boxes and fields in the table (you have to add a set of start/end text boxes on that form and use code as above to build the field criteria. I suggest you get ONE text box filter working...and then add each additional piece of code for each additional text box you place on your filter form.... Also note in the above example how I've not used in if then...else structure to distinguish between two dates, and selecting a "between" command fror the sql. Reallly, what happens is: if startdate then strWhere - "InvoiceDate = #some date# at this point in time are where is correctly formed. if the user does decide to enter an ending date, then we simply append that eg: strWhere = strWhere & "InvoiceDate = #some date#" at this point I'm we now have strWhere = "InvvoiceDate = #some date# and InvoiceDate = "#some date#" we *could* write the above as: strWhere = "InvvoiceDate between #some date# and "#some date#" However both have the same logical meaning, and my approach takes a whole heck of a lot less code as we don't have to start concerning about usng "between" or "and" in the syntax. just dump the use of between altogether, and you can see that the code will flow and write and work a lot easier. Once you get the code snippet working for one set of text boxes on your form, then you can relatively with ease cut and paste over and over the same piece of code for the additional sets of text boxes and gate fields that you have. Also, you might want a reintroduce your idea of having a variable for the date field, since the date code is likely going to look much the same for the next four sets of fields that you use. eg: use: strField = "InvoiceDate" If IsNull(Me.txtStartDate) = False Then strWhere = strField & " = " & Format(Me.txtStartDate, conDateFormat)" End If So, get this working with ONE date field. also keep in mind that using spaces in field names will keep you frustrated in the salt mines dealing with spaces in your SQL. that if you're using spaces in your field names then you should likely put square brackets around them eg: strField = "[Invoice Date]" -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada |
Thread Tools | |
Display Modes | |
|
|