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 |
#21
|
|||
|
|||
Where you put it depends on what you want to do with it.
Typically, you would use the Open event procedure of the report to parse the string into your variables. From there you can perform math on the dates, or concatenate the strings, or assign values to text boxes (typically in Report_Header) to show them if you want. It's your call how you use it. I was simply showing you how to pass multiple values of different types via the OpenArgs string, and then parse them. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "albertsong" wrote in message ... Like this? ? Split([Report].[OpenArgs])(1) Where do I put this? In the Control Source of an unbound text box? In the Event Procedure of the OpenEvent of the report? "Allen Browne" wrote: If you pass in a string such as: "#1/1/2004#; #12/31/2004#; 365" you can parse out the parts of the string as: ? Split("#1/1/2004#; #12/31/2004#; 365", ";")(1) ? Split("#1/1/2004#; #12/31/2004#; 365", ";")(2) "albertsong" wrote in message ... OK, now you lost me... I added the results of my JustMinutes function to the OpenArgs so I now have a unbound text box on my report that says "between 10/01/04 07:00AM and 10/01/04 08:00AM;60" with the ;60 being the difference. Where do I put the Split()? The help file on the Split function in VBA is not very good. "Allen Browne" wrote: You can pass as many values as you like in the OpenArgs string, delimited by (say) ";". Use Split() to parse them. Another alternative is to use the Open event of the report to read the values from the form, e.g.: If CurrentProject.AllForms("Form1").IsLoaded Then With Forms("Form1") Debug.Print DateDiff("n", ![StartTime], ![EndTime]) End With End If -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "albertsong" wrote in message news OK, now I understand how to use the WhereCondition and how to use OpenArgs to send strings through. In my example, I can send a string through OpenArgs that says "between 10/1/04 07:00AM and 10/4/04 09:00AM". But, I also need either the values themselves or the difference between these two values to do some calculations in the report. I already have a function JustMinutes that calculates the difference in minutes so that's not the issue. I'm dividing the run time of a machine during the user entered time by the total time available between the user entered time to get utilization. As I see it, I can: 1. Pass both of the actual date/time values from the unbound controls and calculate the difference in the report OR 2. Calculate the difference between the actual date/time values from the unbound controls in another unbound control on the forms and pass it to the report Either way, how do I use OpenArgs to pass this value (to do calculations) in addition to the string? "Allen Browne" wrote: You will have a command button on your form that actually opens the report. In its Click event you need to build two strings: one as the WhereCondition for OpenReport, and the other as the description for the user. In Access 2002 and 2003, you can pass the description through the OpenArgs, and just add a text box on the report with Control Source of: =[Report].[OpenArgs] In earlier versions, assign strDescrip to the Caption of a hidden label on your form, and use the Format event of the Report Header section to read it, and copy to a text box on the report. This example shows how to create the 2 strings for different field types, using the different delimiters. Note that the date range example copes with the fact that the field may have a time component by asking for "less than the next day". Private Sub cmdPreview_Click() Dim strWhere As String 'Where Condition Dim strDescrip As String 'Description of Where Condition. Dim lngLen As Long 'Length of string. Const conJetDate ="\#mm\/dd\/yyyy\#" 'Format for dates. 'Text field example. If Not IsNull(Me.txtSurname) Then strWhere = strWhere & "([Surname] = """ & Me.txtSurname & """) AND " strDescrip = strDescrip & "Surname of " & Me.txtSurname & ". " End If 'Number field example. If Not IsNull(Me.txtAmount) Then strWhere = strWhere & "([Amount] = " & Me.txtAmount & ") AND " strDescrip = strDescrip & "Amount at least " & Format(Me.txtAmount, "Currency") & ". " End If 'Date range example If Not IsNull(Me.txtStartDate) Then strWhere = strWhere & "([MyDateField] = " & _ Format(Me.txtStartDate, conJetDate) & ") AND " strDescrip = ... End If If Not IsNull(Me.txtEndDate) Then strWhere = strWhere & "([MyDateField] " & _ Format(Me.txtEndDate + 1, conJetDate) & ") AND " End If 'Chop of the trailing " AND " lngLen = Len(strWhere) - 5 If lngLen 0 Then strWhere = Left$(strWhere, lngLen) strDescrip = Left$(strDescrip, Len(strDescrip) - 2) End If 'Open the report DoCmd.OpenReport "MyReport", acViewPreview, , strWhere, OpenArgs:=strDescrip End Sub "albertsong" wrote in message ... Thanks for all of the help and support...just a couple more questions... I've created an unbound form that has unbound controls for everything that a user might want to filter on (like date range, machine, shift, etc.) How do I use the information that the user enters in the unbound controls to: 1. Filter the results of query for the report 2. Display the user entry information on the report For instance, the user enters a StartDate and EndDate for records. The query must limit the records to info bewteen those dates and then I need to display the dates the user requested on the report. |
#22
|
|||
|
|||
Where you put it depends on what you want to do with it.
Typically, you would use the Open event procedure of the report to parse the string into your variables. From there you can perform math on the dates, or concatenate the strings, or assign values to text boxes (typically in Report_Header) to show them if you want. It's your call how you use it. I was simply showing you how to pass multiple values of different types via the OpenArgs string, and then parse them. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "albertsong" wrote in message ... Like this? ? Split([Report].[OpenArgs])(1) Where do I put this? In the Control Source of an unbound text box? In the Event Procedure of the OpenEvent of the report? "Allen Browne" wrote: If you pass in a string such as: "#1/1/2004#; #12/31/2004#; 365" you can parse out the parts of the string as: ? Split("#1/1/2004#; #12/31/2004#; 365", ";")(1) ? Split("#1/1/2004#; #12/31/2004#; 365", ";")(2) "albertsong" wrote in message ... OK, now you lost me... I added the results of my JustMinutes function to the OpenArgs so I now have a unbound text box on my report that says "between 10/01/04 07:00AM and 10/01/04 08:00AM;60" with the ;60 being the difference. Where do I put the Split()? The help file on the Split function in VBA is not very good. "Allen Browne" wrote: You can pass as many values as you like in the OpenArgs string, delimited by (say) ";". Use Split() to parse them. Another alternative is to use the Open event of the report to read the values from the form, e.g.: If CurrentProject.AllForms("Form1").IsLoaded Then With Forms("Form1") Debug.Print DateDiff("n", ![StartTime], ![EndTime]) End With End If -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "albertsong" wrote in message news OK, now I understand how to use the WhereCondition and how to use OpenArgs to send strings through. In my example, I can send a string through OpenArgs that says "between 10/1/04 07:00AM and 10/4/04 09:00AM". But, I also need either the values themselves or the difference between these two values to do some calculations in the report. I already have a function JustMinutes that calculates the difference in minutes so that's not the issue. I'm dividing the run time of a machine during the user entered time by the total time available between the user entered time to get utilization. As I see it, I can: 1. Pass both of the actual date/time values from the unbound controls and calculate the difference in the report OR 2. Calculate the difference between the actual date/time values from the unbound controls in another unbound control on the forms and pass it to the report Either way, how do I use OpenArgs to pass this value (to do calculations) in addition to the string? "Allen Browne" wrote: You will have a command button on your form that actually opens the report. In its Click event you need to build two strings: one as the WhereCondition for OpenReport, and the other as the description for the user. In Access 2002 and 2003, you can pass the description through the OpenArgs, and just add a text box on the report with Control Source of: =[Report].[OpenArgs] In earlier versions, assign strDescrip to the Caption of a hidden label on your form, and use the Format event of the Report Header section to read it, and copy to a text box on the report. This example shows how to create the 2 strings for different field types, using the different delimiters. Note that the date range example copes with the fact that the field may have a time component by asking for "less than the next day". Private Sub cmdPreview_Click() Dim strWhere As String 'Where Condition Dim strDescrip As String 'Description of Where Condition. Dim lngLen As Long 'Length of string. Const conJetDate ="\#mm\/dd\/yyyy\#" 'Format for dates. 'Text field example. If Not IsNull(Me.txtSurname) Then strWhere = strWhere & "([Surname] = """ & Me.txtSurname & """) AND " strDescrip = strDescrip & "Surname of " & Me.txtSurname & ". " End If 'Number field example. If Not IsNull(Me.txtAmount) Then strWhere = strWhere & "([Amount] = " & Me.txtAmount & ") AND " strDescrip = strDescrip & "Amount at least " & Format(Me.txtAmount, "Currency") & ". " End If 'Date range example If Not IsNull(Me.txtStartDate) Then strWhere = strWhere & "([MyDateField] = " & _ Format(Me.txtStartDate, conJetDate) & ") AND " strDescrip = ... End If If Not IsNull(Me.txtEndDate) Then strWhere = strWhere & "([MyDateField] " & _ Format(Me.txtEndDate + 1, conJetDate) & ") AND " End If 'Chop of the trailing " AND " lngLen = Len(strWhere) - 5 If lngLen 0 Then strWhere = Left$(strWhere, lngLen) strDescrip = Left$(strDescrip, Len(strDescrip) - 2) End If 'Open the report DoCmd.OpenReport "MyReport", acViewPreview, , strWhere, OpenArgs:=strDescrip End Sub "albertsong" wrote in message ... Thanks for all of the help and support...just a couple more questions... I've created an unbound form that has unbound controls for everything that a user might want to filter on (like date range, machine, shift, etc.) How do I use the information that the user enters in the unbound controls to: 1. Filter the results of query for the report 2. Display the user entry information on the report For instance, the user enters a StartDate and EndDate for records. The query must limit the records to info bewteen those dates and then I need to display the dates the user requested on the report. |
#23
|
|||
|
|||
Great answer... that helped tremendously! One last question (hopefully):
I have a report with a subreport in it that are based on the same query. From my unbound form, I click a button to OpenReport, passing the Where statement with the user's filter criteria. How do I insure the same criteria are passed to the subreport? "Allen Browne" wrote: Where you put it depends on what you want to do with it. Typically, you would use the Open event procedure of the report to parse the string into your variables. From there you can perform math on the dates, or concatenate the strings, or assign values to text boxes (typically in Report_Header) to show them if you want. It's your call how you use it. I was simply showing you how to pass multiple values of different types via the OpenArgs string, and then parse them. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "albertsong" wrote in message ... Like this? ? Split([Report].[OpenArgs])(1) Where do I put this? In the Control Source of an unbound text box? In the Event Procedure of the OpenEvent of the report? "Allen Browne" wrote: If you pass in a string such as: "#1/1/2004#; #12/31/2004#; 365" you can parse out the parts of the string as: ? Split("#1/1/2004#; #12/31/2004#; 365", ";")(1) ? Split("#1/1/2004#; #12/31/2004#; 365", ";")(2) "albertsong" wrote in message ... OK, now you lost me... I added the results of my JustMinutes function to the OpenArgs so I now have a unbound text box on my report that says "between 10/01/04 07:00AM and 10/01/04 08:00AM;60" with the ;60 being the difference. Where do I put the Split()? The help file on the Split function in VBA is not very good. "Allen Browne" wrote: You can pass as many values as you like in the OpenArgs string, delimited by (say) ";". Use Split() to parse them. Another alternative is to use the Open event of the report to read the values from the form, e.g.: If CurrentProject.AllForms("Form1").IsLoaded Then With Forms("Form1") Debug.Print DateDiff("n", ![StartTime], ![EndTime]) End With End If -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "albertsong" wrote in message news OK, now I understand how to use the WhereCondition and how to use OpenArgs to send strings through. In my example, I can send a string through OpenArgs that says "between 10/1/04 07:00AM and 10/4/04 09:00AM". But, I also need either the values themselves or the difference between these two values to do some calculations in the report. I already have a function JustMinutes that calculates the difference in minutes so that's not the issue. I'm dividing the run time of a machine during the user entered time by the total time available between the user entered time to get utilization. As I see it, I can: 1. Pass both of the actual date/time values from the unbound controls and calculate the difference in the report OR 2. Calculate the difference between the actual date/time values from the unbound controls in another unbound control on the forms and pass it to the report Either way, how do I use OpenArgs to pass this value (to do calculations) in addition to the string? "Allen Browne" wrote: You will have a command button on your form that actually opens the report. In its Click event you need to build two strings: one as the WhereCondition for OpenReport, and the other as the description for the user. In Access 2002 and 2003, you can pass the description through the OpenArgs, and just add a text box on the report with Control Source of: =[Report].[OpenArgs] In earlier versions, assign strDescrip to the Caption of a hidden label on your form, and use the Format event of the Report Header section to read it, and copy to a text box on the report. This example shows how to create the 2 strings for different field types, using the different delimiters. Note that the date range example copes with the fact that the field may have a time component by asking for "less than the next day". Private Sub cmdPreview_Click() Dim strWhere As String 'Where Condition Dim strDescrip As String 'Description of Where Condition. Dim lngLen As Long 'Length of string. Const conJetDate ="\#mm\/dd\/yyyy\#" 'Format for dates. 'Text field example. If Not IsNull(Me.txtSurname) Then strWhere = strWhere & "([Surname] = """ & Me.txtSurname & """) AND " strDescrip = strDescrip & "Surname of " & Me.txtSurname & ". " End If 'Number field example. If Not IsNull(Me.txtAmount) Then strWhere = strWhere & "([Amount] = " & Me.txtAmount & ") AND " strDescrip = strDescrip & "Amount at least " & Format(Me.txtAmount, "Currency") & ". " End If 'Date range example If Not IsNull(Me.txtStartDate) Then strWhere = strWhere & "([MyDateField] = " & _ Format(Me.txtStartDate, conJetDate) & ") AND " strDescrip = ... End If If Not IsNull(Me.txtEndDate) Then strWhere = strWhere & "([MyDateField] " & _ Format(Me.txtEndDate + 1, conJetDate) & ") AND " End If 'Chop of the trailing " AND " lngLen = Len(strWhere) - 5 If lngLen 0 Then strWhere = Left$(strWhere, lngLen) strDescrip = Left$(strDescrip, Len(strDescrip) - 2) End If 'Open the report DoCmd.OpenReport "MyReport", acViewPreview, , strWhere, OpenArgs:=strDescrip End Sub "albertsong" wrote in message ... Thanks for all of the help and support...just a couple more questions... I've created an unbound form that has unbound controls for everything that a user might want to filter on (like date range, machine, shift, etc.) How do I use the information that the user enters in the unbound controls to: 1. Filter the results of query for the report 2. Display the user entry information on the report For instance, the user enters a StartDate and EndDate for records. The query must limit the records to info bewteen those dates and then I need to display the dates the user requested on the report. |
#24
|
|||
|
|||
The simplest way to do that is to base the subreport on a query that reads
the values directly from the form, i.e. the Criteria row of the query contains things like: [Forms].[Form1].[Text1] If you want other alternatives, the question has been asked lots of times. Go to groups.google.com, choose the Advanced Search, and search the group microsoft.public.access.reports -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "albertsong" wrote in message ... Great answer... that helped tremendously! One last question (hopefully): I have a report with a subreport in it that are based on the same query. From my unbound form, I click a button to OpenReport, passing the Where statement with the user's filter criteria. How do I insure the same criteria are passed to the subreport? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Dynamic Page Margins | Vel | Setting Up & Running Reports | 6 | September 19th, 2004 09:46 PM |
programmatically add controls to report | G | Setting Up & Running Reports | 2 | August 4th, 2004 09:11 PM |
Restrict Report To Current Record | Katherine R | Setting Up & Running Reports | 1 | July 15th, 2004 07:23 PM |
6 Tables, 1 Report, W/O 6 Qrys | Andy | Setting Up & Running Reports | 9 | June 29th, 2004 09:52 PM |
Label | SRIT | General Discussion | 2 | June 22nd, 2004 09:42 PM |