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 |
#11
|
|||
|
|||
Combine combo boxes with date range
On Thu, 1 Apr 2010 12:10:01 -0700, AccessKay
wrote: The report runs okay if I have the form open. If I don’t, then it asks for parameters. Well... yes. The query is asking for a parameter (the form reference). If the form isn't open the query won't know what its parameter value is, so it will ask. This is normal and expected behavior; what would you WANT the query to do if its parameter is not supplied? -- John W. Vinson [MVP] |
#12
|
|||
|
|||
Combine combo boxes with date range
Daryl, I’ve learned a lot from you about vba in the course of this dialog. I
thank you for that. Though, I’m starting to wonder if I’m in over my head, but I really don’t want to quit on this. I got an Error #13 Type mismatch but hey, no red text. Here is what I have. I hope you can figure it out but I still appreciate your help if you can’t. My vba: strWhere = "Category = '" & Forms!frmDialogBox!cboCategory.Column(0) & "' " And "[Group] = '" & Forms!frmDialogBox!cboGroup.Column(0) & "'" If IsDate(Me.txtStartDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " = " & Format(Me.txtStartDate, strcJetDate) & ")" End If If IsDate(Me.txtEndDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " " & Format(Me.txtEndDate + 1, strcJetDate) & ")" End If Here is my SQL: SELECT tblTrans_Mstr.Category, tblTrans_Mstr.TransDate, tblTrans_Mstr.Group, tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr.Credit_GL_Acct, tblTrans_Mstr.Lbr_Hours, Sum(tblTrans_Mstr.Labor_Cost) AS SumOfLabor_Cost, tblTrans_Mstr.Lbr_OH_Rate, Sum(tblTrans_Mstr.Lbr_OH_Cost) AS SumOfLbr_OH_Cost, Sum([Labor_Cost]+[Lbr_OH_Cost]) AS LbrTotCost, tblTrans_Mstr.ODC_Cost_Category, Sum(tblTrans_Mstr.ODC_Cost) AS SumOfODC_Cost FROM tblTrans_Mstr GROUP BY tblTrans_Mstr.Category, tblTrans_Mstr.TransDate, tblTrans_Mstr.Group, tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr.Credit_GL_Acct, tblTrans_Mstr.Lbr_Hours, tblTrans_Mstr.Lbr_OH_Rate, tblTrans_Mstr.ODC_Cost_Category HAVING (((tblTrans_Mstr.Category)=[Forms]![frmDialogBox]![cboCategory]) AND ((tblTrans_Mstr.TransDate)=[Forms]![frmDialogBox]![txtStartDate] And (tblTrans_Mstr.TransDate)=[Forms]![frmDialogBox]![txtEndDate]) AND ((tblTrans_Mstr.Group)=[Forms]![frmDialogBox]![cboGroup])); Thanks, Kay "Daryl S" wrote: AccessKay - If the query is pulling information from the form (as yours is), then the form must be open for the query to no ask for the values. I put the missing single-quote in on the second line below - if you get green text in an Access code window, then it is treated as a comment and ignored by the compiler. You usually put comments in by putting a single quote and then the comment. If the single quote is not a comment, but part of the line, then there is probably something missing (as you said, the matching single quote). strWhere = "Category = '" & Forms!frmDialogBox!cboCategory.Column(0) & "' AND " [Group] = '" & Forms!frmDialogBox!cboGroup.column(0) & "'" Hope this helps! -- Daryl S "AccessKay" wrote: Daryl, The report runs okay if I have the form open. If I don’t, then it asks for parameters. I have this feeling that it has something to do with the first strWhere. This is what I have right now: strWhere = "Category = '" & Forms!frmDialogBox!cboCategory.Column(0) & "' AND " [Group] = " & Forms!frmDialogBox!cboGroup.column(0) & "'" I noticed I had a missing ' after [Group] = from the version you sent me. If I put this in, the text turns red and I get a syntex error. I'm thinking I'm missing something else, but I can't tell what it is. Also, the last "'" at the end is green. I can send what you requested last time but I was hoping you could identify my error here. Thanks, Kay "Daryl S" wrote: AccessKay - Can you run the report by itself (rptLaborODC)? If not, then work on the report until it works by itself. Then work on the strWhere. It should be in your immediate window if you have the code open while you run the report from the form. There could be a typo or other issue there. If you can't figure it out, then post both the strWhere (copy/paste from the immediate window), and the SQL for your report's record source. -- Daryl S "AccessKay" wrote: Daryl, I got rid of the End If and put it all on one line…no more red. I appreciate the additional information. I think I understand but maybe not because I now have a new error message but I have no more red in my vba code and the bugger is not popping up so I’m not sure. The error message caption is Cannot Open Report and the error reads as: #2465: Microsoft can’t find the field “l” referred to in your expression. I looked all around for an “l” but didn’t see one. I REALLY appreciate your patience. I just have to be close to getting this to work. Any ideas about how to fix this error? Many, many thanks, Kay "Daryl S" wrote: AccessKay - There are two ways to do the If/Then statements. If there is only one statement to execute in the 'true' and optional 'else' clauses, then you can put them on one line, without an End If: If test Then result Else else result The more common way allows for multiple statements to be executed in the 'true' case and in the optional 'else' case. This format requires the End If (so it knows when the block of statements ends). If test Then result block Else else block End If Your statement could be either If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" End If '(Error message) or (this would be all on one line - the copy/paste may not show it that way) If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" -- Daryl S "AccessKay" wrote: Daryl, VERY useful to know and the red went away. The vba is running okay until it gets to the end and then I get this Compile error: End If without block If. It's this part he Err_Handler: If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" End If (Error message) Resume Exit_Handler End Sub Can you help me fix this please? Thanks again, Kay "Daryl S" wrote: AccessKay - Yes, the copy/paste does add some line breaks... In Access code, if your statement will take up more than one line, you will need to tell access that the code continues on the next line. This is done with an underscore character, but must be in a logical place (not within double-quotes for example). So you can change the code by removing the line feeds or by adding in more line continuation characters, or a combination of the two. Like this: strWhere = "Category = '" & Forms!DialogBox!cboCategory.Column(0) & _ "' AND "[Group] = '" & _ Forms!DialogBox!cboGroup.column(0) & "'" Hope that helps! -- Daryl S "AccessKay" wrote: Thanks Daryl. I’m very new to vba. I replaced what I thought was the made-up names. In future posts, I’ll remember to state these names. Anyways, I’ve got a lot of red related to the strWhere areas. ' start here building the WHERE clause - substitute your real names here. strWhere = "Category = '" & Forms!DialogBox!cboCategory.Column(0) & "' AND " [Group] = '" & _ Forms!DialogBox!cboGroup.column(0) & "'" Here…I changed my form name to DialogBox and the name of my first combo which is cboCategory. I did the same for Group. The text turns red at [Group]. Then in the second block, you put a note to update. My text box is named txtStartDate so I didn’t change this. I wasn’t sure if there was another name in there that I needed to change. If IsDate(Me.txtStartDate) Then If strWhere vbNullString Then 'updated this block strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " = " & Format(Me.txtStartDate, strcJetDate) & ")" The text goes red at strWhere = strWhere & “…. End If If IsDate(Me.txtEndDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " " & Format(Me.txtEndDate + 1, strcJetDate) & ")" End If Again at strWhere it goes red. Then at the very bottom it’s red for MsgBox "Error " & Err.Number & ": " & Err.Description, Just to clarify… Combo box 1: cboCategory Combo box 2: cboGroup Text box 1: txtStartDate Text box 2: txtEndDate Command Button: cmdPreview Form name: DialogBox Query name: LaborODC I hope I have provided you with enough information. I really appreciate the help!!! Kay "Daryl S" wrote: Kay - To update the code, you first fill in the strWhere with the values from the forms combo boxes, then add the dates as needed. I have updated this, but you will need to change my made-up names for your real ones: Private Sub cmdPreview_Click() On Error GoTo Err_Handler Dim strReport As String Dim strDateField As String Dim strWhere As String Dim lngView As Long Const strcJetDate = "\#mm\/dd\/yyyy\#" strReport = "rptLaborODC" strDateField = "[TransDate]" lngView = acViewPreview ' start here building the WHERE clause - substitute your real names here. strWhere = "Category = '" & Forms!formname!cboCatName.column(0) & "' AND [Group] = '" & _ Forms!formname!cboGpName.column(0) & "'" If IsDate(Me.txtStartDate) Then If strWhere vbNullString Then 'updated this block strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " = " & Format(Me.txtStartDate, strcJetDate) & ")" End If If IsDate(Me.txtEndDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " " & Format(Me.txtEndDate + 1, strcJetDate) & ")" End If If CurrentProject.AllReports(strReport).IsLoaded Then DoCmd.Close acReport, strReport End If Debug.Print strWhere DoCmd.OpenReport strReport, lngView, , strWhere Exit_Handler: Exit Sub Err_Handler: If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" End If Resume Exit_Handler End Sub -- Daryl S "AccessKay" wrote: Hi, I created my first dialog box that includes two combo boxes, one named cboCategory and another one named cboGroup. I also created a command button that opens a report. My VBA is as follows: Private Sub cmdOK_Click() DoCmd.OpenReport "rptLaborODC", acViewPreview, , , acNormal End Sub This worked out for me but then I tried to add a date range. I found some instructions from Allen Browne’s website for “Limiting a report to a date |
#13
|
|||
|
|||
Combine combo boxes with date range
Thanks for your reply. I fully understand this. It’s intended to be a
dialog box but I can’t get my code to work. The problem seems to be with my vba. "John W. Vinson" wrote: On Thu, 1 Apr 2010 12:10:01 -0700, AccessKay wrote: The report runs okay if I have the form open. If I don’t, then it asks for parameters. Well... yes. The query is asking for a parameter (the form reference). If the form isn't open the query won't know what its parameter value is, so it will ask. This is normal and expected behavior; what would you WANT the query to do if its parameter is not supplied? -- John W. Vinson [MVP] . |
#14
|
|||
|
|||
Combine combo boxes with date range
Kay -
You are pretty close. In the WHERE clause, you want the field name included within the double-quotes, but the values from the form need to be evaluated outside of the double-quotes. I have updated the code here (untested). strWhere = "Category = '" & Forms!frmDialogBox!cboCategory.Column(0) & "' " & _ " And [Group] = '" & Forms!frmDialogBox!cboGroup.Column(0) & "'" If IsDate(Me.txtStartDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "([strDateField] = " & _ Format(Me.txtStartDate, strcJetDate) & ")" End If If IsDate(Me.txtEndDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "([strDateField] " & _ Format(Me.txtEndDate + 1, strcJetDate) & ")" End If debug.print strWhere The SQL looks OK based on a quick glance. If you have issues, step through the code as it runs, and when the debug.print strWhere statement executes, the results will be in the immediate window. This is what you should look at for issues, and copy/paste into your next post. Good luck! -- Daryl S "AccessKay" wrote: Daryl, I’ve learned a lot from you about vba in the course of this dialog. I thank you for that. Though, I’m starting to wonder if I’m in over my head, but I really don’t want to quit on this. I got an Error #13 Type mismatch but hey, no red text. Here is what I have. I hope you can figure it out but I still appreciate your help if you can’t. My vba: strWhere = "Category = '" & Forms!frmDialogBox!cboCategory.Column(0) & "' " And "[Group] = '" & Forms!frmDialogBox!cboGroup.Column(0) & "'" If IsDate(Me.txtStartDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " = " & Format(Me.txtStartDate, strcJetDate) & ")" End If If IsDate(Me.txtEndDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " " & Format(Me.txtEndDate + 1, strcJetDate) & ")" End If Here is my SQL: SELECT tblTrans_Mstr.Category, tblTrans_Mstr.TransDate, tblTrans_Mstr.Group, tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr.Credit_GL_Acct, tblTrans_Mstr.Lbr_Hours, Sum(tblTrans_Mstr.Labor_Cost) AS SumOfLabor_Cost, tblTrans_Mstr.Lbr_OH_Rate, Sum(tblTrans_Mstr.Lbr_OH_Cost) AS SumOfLbr_OH_Cost, Sum([Labor_Cost]+[Lbr_OH_Cost]) AS LbrTotCost, tblTrans_Mstr.ODC_Cost_Category, Sum(tblTrans_Mstr.ODC_Cost) AS SumOfODC_Cost FROM tblTrans_Mstr GROUP BY tblTrans_Mstr.Category, tblTrans_Mstr.TransDate, tblTrans_Mstr.Group, tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr.Credit_GL_Acct, tblTrans_Mstr.Lbr_Hours, tblTrans_Mstr.Lbr_OH_Rate, tblTrans_Mstr.ODC_Cost_Category HAVING (((tblTrans_Mstr.Category)=[Forms]![frmDialogBox]![cboCategory]) AND ((tblTrans_Mstr.TransDate)=[Forms]![frmDialogBox]![txtStartDate] And (tblTrans_Mstr.TransDate)=[Forms]![frmDialogBox]![txtEndDate]) AND ((tblTrans_Mstr.Group)=[Forms]![frmDialogBox]![cboGroup])); Thanks, Kay "Daryl S" wrote: AccessKay - If the query is pulling information from the form (as yours is), then the form must be open for the query to no ask for the values. I put the missing single-quote in on the second line below - if you get green text in an Access code window, then it is treated as a comment and ignored by the compiler. You usually put comments in by putting a single quote and then the comment. If the single quote is not a comment, but part of the line, then there is probably something missing (as you said, the matching single quote). strWhere = "Category = '" & Forms!frmDialogBox!cboCategory.Column(0) & "' AND " [Group] = '" & Forms!frmDialogBox!cboGroup.column(0) & "'" Hope this helps! -- Daryl S "AccessKay" wrote: Daryl, The report runs okay if I have the form open. If I don’t, then it asks for parameters. I have this feeling that it has something to do with the first strWhere. This is what I have right now: strWhere = "Category = '" & Forms!frmDialogBox!cboCategory.Column(0) & "' AND " [Group] = " & Forms!frmDialogBox!cboGroup.column(0) & "'" I noticed I had a missing ' after [Group] = from the version you sent me. If I put this in, the text turns red and I get a syntex error. I'm thinking I'm missing something else, but I can't tell what it is. Also, the last "'" at the end is green. I can send what you requested last time but I was hoping you could identify my error here. Thanks, Kay "Daryl S" wrote: AccessKay - Can you run the report by itself (rptLaborODC)? If not, then work on the report until it works by itself. Then work on the strWhere. It should be in your immediate window if you have the code open while you run the report from the form. There could be a typo or other issue there. If you can't figure it out, then post both the strWhere (copy/paste from the immediate window), and the SQL for your report's record source. -- Daryl S "AccessKay" wrote: Daryl, I got rid of the End If and put it all on one line…no more red. I appreciate the additional information. I think I understand but maybe not because I now have a new error message but I have no more red in my vba code and the bugger is not popping up so I’m not sure. The error message caption is Cannot Open Report and the error reads as: #2465: Microsoft can’t find the field “l” referred to in your expression. I looked all around for an “l” but didn’t see one. I REALLY appreciate your patience. I just have to be close to getting this to work. Any ideas about how to fix this error? Many, many thanks, Kay "Daryl S" wrote: AccessKay - There are two ways to do the If/Then statements. If there is only one statement to execute in the 'true' and optional 'else' clauses, then you can put them on one line, without an End If: If test Then result Else else result The more common way allows for multiple statements to be executed in the 'true' case and in the optional 'else' case. This format requires the End If (so it knows when the block of statements ends). If test Then result block Else else block End If Your statement could be either If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" End If '(Error message) or (this would be all on one line - the copy/paste may not show it that way) If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" -- Daryl S "AccessKay" wrote: Daryl, VERY useful to know and the red went away. The vba is running okay until it gets to the end and then I get this Compile error: End If without block If. It's this part he Err_Handler: If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" End If (Error message) Resume Exit_Handler End Sub Can you help me fix this please? Thanks again, Kay "Daryl S" wrote: AccessKay - Yes, the copy/paste does add some line breaks... In Access code, if your statement will take up more than one line, you will need to tell access that the code continues on the next line. This is done with an underscore character, but must be in a logical place (not within double-quotes for example). So you can change the code by removing the line feeds or by adding in more line continuation characters, or a combination of the two. Like this: strWhere = "Category = '" & Forms!DialogBox!cboCategory.Column(0) & _ "' AND "[Group] = '" & _ Forms!DialogBox!cboGroup.column(0) & "'" Hope that helps! -- Daryl S "AccessKay" wrote: Thanks Daryl. I’m very new to vba. I replaced what I thought was the made-up names. In future posts, I’ll remember to state these names. Anyways, I’ve got a lot of red related to the strWhere areas. ' start here building the WHERE clause - substitute your real names here. strWhere = "Category = '" & Forms!DialogBox!cboCategory.Column(0) & "' AND " [Group] = '" & _ Forms!DialogBox!cboGroup.column(0) & "'" Here…I changed my form name to DialogBox and the name of my first combo which is cboCategory. I did the same for Group. The text turns red at [Group]. Then in the second block, you put a note to update. My text box is named txtStartDate so I didn’t change this. I wasn’t sure if there was another name in there that I needed to change. If IsDate(Me.txtStartDate) Then If strWhere vbNullString Then 'updated this block strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " = " & Format(Me.txtStartDate, strcJetDate) & ")" The text goes red at strWhere = strWhere & “…. End If If IsDate(Me.txtEndDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " " & Format(Me.txtEndDate + 1, strcJetDate) & ")" End If Again at strWhere it goes red. Then at the very bottom it’s red for MsgBox "Error " & Err.Number & ": " & Err.Description, Just to clarify… Combo box 1: cboCategory Combo box 2: cboGroup Text box 1: txtStartDate Text box 2: txtEndDate Command Button: cmdPreview Form name: DialogBox Query name: LaborODC I hope I have provided you with enough information. I really appreciate the help!!! Kay "Daryl S" wrote: Kay - To update the code, you first fill in the strWhere with the values from the forms combo boxes, then add the dates as needed. I have updated this, but you will need to change my made-up names for your real ones: Private Sub cmdPreview_Click() On Error GoTo Err_Handler Dim strReport As String Dim strDateField As String Dim strWhere As String Dim lngView As Long Const strcJetDate = "\#mm\/dd\/yyyy\#" strReport = "rptLaborODC" strDateField = "[TransDate]" lngView = acViewPreview ' start here building the WHERE clause - substitute your real names here. strWhere = "Category = '" & Forms!formname!cboCatName.column(0) & "' AND [Group] = '" & _ Forms!formname!cboGpName.column(0) & "'" If IsDate(Me.txtStartDate) Then If strWhere vbNullString Then 'updated this block strWhere = strWhere & " AND " End If |
#15
|
|||
|
|||
Combine combo boxes with date range
Hi Daryl,
I’m so excited…it works!!! You’re a good man to stick with me. I really appreciate your help and patience. It was my first attempt to use vba and I never could have done this without you. I’ve learned so much from this and I’m thinking that I might go out and get me a VBA for Dummies book. I can’t thank you enough! Kay "Daryl S" wrote: Kay - You are pretty close. In the WHERE clause, you want the field name included within the double-quotes, but the values from the form need to be evaluated outside of the double-quotes. I have updated the code here (untested). strWhere = "Category = '" & Forms!frmDialogBox!cboCategory.Column(0) & "' " & _ " And [Group] = '" & Forms!frmDialogBox!cboGroup.Column(0) & "'" If IsDate(Me.txtStartDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "([strDateField] = " & _ Format(Me.txtStartDate, strcJetDate) & ")" End If If IsDate(Me.txtEndDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "([strDateField] " & _ Format(Me.txtEndDate + 1, strcJetDate) & ")" End If debug.print strWhere The SQL looks OK based on a quick glance. If you have issues, step through the code as it runs, and when the debug.print strWhere statement executes, the results will be in the immediate window. This is what you should look at for issues, and copy/paste into your next post. Good luck! -- Daryl S "AccessKay" wrote: Daryl, I’ve learned a lot from you about vba in the course of this dialog. I thank you for that. Though, I’m starting to wonder if I’m in over my head, but I really don’t want to quit on this. I got an Error #13 Type mismatch but hey, no red text. Here is what I have. I hope you can figure it out but I still appreciate your help if you can’t. My vba: strWhere = "Category = '" & Forms!frmDialogBox!cboCategory.Column(0) & "' " And "[Group] = '" & Forms!frmDialogBox!cboGroup.Column(0) & "'" If IsDate(Me.txtStartDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " = " & Format(Me.txtStartDate, strcJetDate) & ")" End If If IsDate(Me.txtEndDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " " & Format(Me.txtEndDate + 1, strcJetDate) & ")" End If Here is my SQL: SELECT tblTrans_Mstr.Category, tblTrans_Mstr.TransDate, tblTrans_Mstr.Group, tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr.Credit_GL_Acct, tblTrans_Mstr.Lbr_Hours, Sum(tblTrans_Mstr.Labor_Cost) AS SumOfLabor_Cost, tblTrans_Mstr.Lbr_OH_Rate, Sum(tblTrans_Mstr.Lbr_OH_Cost) AS SumOfLbr_OH_Cost, Sum([Labor_Cost]+[Lbr_OH_Cost]) AS LbrTotCost, tblTrans_Mstr.ODC_Cost_Category, Sum(tblTrans_Mstr.ODC_Cost) AS SumOfODC_Cost FROM tblTrans_Mstr GROUP BY tblTrans_Mstr.Category, tblTrans_Mstr.TransDate, tblTrans_Mstr.Group, tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr.Credit_GL_Acct, tblTrans_Mstr.Lbr_Hours, tblTrans_Mstr.Lbr_OH_Rate, tblTrans_Mstr.ODC_Cost_Category HAVING (((tblTrans_Mstr.Category)=[Forms]![frmDialogBox]![cboCategory]) AND ((tblTrans_Mstr.TransDate)=[Forms]![frmDialogBox]![txtStartDate] And (tblTrans_Mstr.TransDate)=[Forms]![frmDialogBox]![txtEndDate]) AND ((tblTrans_Mstr.Group)=[Forms]![frmDialogBox]![cboGroup])); Thanks, Kay "Daryl S" wrote: AccessKay - If the query is pulling information from the form (as yours is), then the form must be open for the query to no ask for the values. I put the missing single-quote in on the second line below - if you get green text in an Access code window, then it is treated as a comment and ignored by the compiler. You usually put comments in by putting a single quote and then the comment. If the single quote is not a comment, but part of the line, then there is probably something missing (as you said, the matching single quote). strWhere = "Category = '" & Forms!frmDialogBox!cboCategory.Column(0) & "' AND " [Group] = '" & Forms!frmDialogBox!cboGroup.column(0) & "'" Hope this helps! -- Daryl S "AccessKay" wrote: Daryl, The report runs okay if I have the form open. If I don’t, then it asks for parameters. I have this feeling that it has something to do with the first strWhere. This is what I have right now: strWhere = "Category = '" & Forms!frmDialogBox!cboCategory.Column(0) & "' AND " [Group] = " & Forms!frmDialogBox!cboGroup.column(0) & "'" I noticed I had a missing ' after [Group] = from the version you sent me. If I put this in, the text turns red and I get a syntex error. I'm thinking I'm missing something else, but I can't tell what it is. Also, the last "'" at the end is green. I can send what you requested last time but I was hoping you could identify my error here. Thanks, Kay "Daryl S" wrote: AccessKay - Can you run the report by itself (rptLaborODC)? If not, then work on the report until it works by itself. Then work on the strWhere. It should be in your immediate window if you have the code open while you run the report from the form. There could be a typo or other issue there. If you can't figure it out, then post both the strWhere (copy/paste from the immediate window), and the SQL for your report's record source. -- Daryl S "AccessKay" wrote: Daryl, I got rid of the End If and put it all on one line…no more red. I appreciate the additional information. I think I understand but maybe not because I now have a new error message but I have no more red in my vba code and the bugger is not popping up so I’m not sure. The error message caption is Cannot Open Report and the error reads as: #2465: Microsoft can’t find the field “l” referred to in your expression. I looked all around for an “l” but didn’t see one. I REALLY appreciate your patience. I just have to be close to getting this to work. Any ideas about how to fix this error? Many, many thanks, Kay "Daryl S" wrote: AccessKay - There are two ways to do the If/Then statements. If there is only one statement to execute in the 'true' and optional 'else' clauses, then you can put them on one line, without an End If: If test Then result Else else result The more common way allows for multiple statements to be executed in the 'true' case and in the optional 'else' case. This format requires the End If (so it knows when the block of statements ends). If test Then result block Else else block End If Your statement could be either If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" End If '(Error message) or (this would be all on one line - the copy/paste may not show it that way) If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" -- Daryl S "AccessKay" wrote: Daryl, VERY useful to know and the red went away. The vba is running okay until it gets to the end and then I get this Compile error: End If without block If. It's this part he Err_Handler: If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" End If (Error message) Resume Exit_Handler End Sub Can you help me fix this please? Thanks again, Kay "Daryl S" wrote: AccessKay - Yes, the copy/paste does add some line breaks... In Access code, if your statement will take up more than one line, you will need to tell access that the code continues on the next line. This is done with an underscore character, but must be in a logical place (not within double-quotes for example). So you can change the code by removing the line feeds or by adding in more line continuation characters, or a combination of the two. Like this: strWhere = "Category = '" & Forms!DialogBox!cboCategory.Column(0) & _ "' AND "[Group] = '" & _ Forms!DialogBox!cboGroup.column(0) & "'" Hope that helps! -- Daryl S "AccessKay" wrote: Thanks Daryl. I’m very new to vba. I replaced what I thought was the made-up names. In future posts, I’ll remember to state these names. Anyways, I’ve got a lot of red related to the strWhere areas. ' start here building the WHERE clause - substitute your real names here. strWhere = "Category = '" & Forms!DialogBox!cboCategory.Column(0) & "' AND " [Group] = '" & _ Forms!DialogBox!cboGroup.column(0) & "'" Here…I changed my form name to DialogBox and the name of my first combo which is cboCategory. I did the same for Group. The text turns red at [Group]. Then in the second block, you put a note to update. My text box is named txtStartDate so I didn’t change this. I wasn’t sure if there was another name in there that I needed to change. If IsDate(Me.txtStartDate) Then If strWhere vbNullString Then 'updated this block strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " = " & Format(Me.txtStartDate, strcJetDate) & ")" The text goes red at strWhere = strWhere & “…. End If If IsDate(Me.txtEndDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " " & Format(Me.txtEndDate + 1, strcJetDate) & ")" End If Again at strWhere it goes red. Then at the very bottom it’s red for MsgBox "Error " & Err.Number & ": " & Err.Description, Just to clarify… Combo box 1: cboCategory Combo box 2: cboGroup Text box 1: txtStartDate Text box 2: txtEndDate |
#16
|
|||
|
|||
Combine combo boxes with date range
Great! Thanks...
-- Daryl S "AccessKay" wrote: Hi Daryl, I’m so excited…it works!!! You’re a good man to stick with me. I really appreciate your help and patience. It was my first attempt to use vba and I never could have done this without you. I’ve learned so much from this and I’m thinking that I might go out and get me a VBA for Dummies book. I can’t thank you enough! Kay "Daryl S" wrote: Kay - You are pretty close. In the WHERE clause, you want the field name included within the double-quotes, but the values from the form need to be evaluated outside of the double-quotes. I have updated the code here (untested). strWhere = "Category = '" & Forms!frmDialogBox!cboCategory.Column(0) & "' " & _ " And [Group] = '" & Forms!frmDialogBox!cboGroup.Column(0) & "'" If IsDate(Me.txtStartDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "([strDateField] = " & _ Format(Me.txtStartDate, strcJetDate) & ")" End If If IsDate(Me.txtEndDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "([strDateField] " & _ Format(Me.txtEndDate + 1, strcJetDate) & ")" End If debug.print strWhere The SQL looks OK based on a quick glance. If you have issues, step through the code as it runs, and when the debug.print strWhere statement executes, the results will be in the immediate window. This is what you should look at for issues, and copy/paste into your next post. Good luck! -- Daryl S "AccessKay" wrote: Daryl, I’ve learned a lot from you about vba in the course of this dialog. I thank you for that. Though, I’m starting to wonder if I’m in over my head, but I really don’t want to quit on this. I got an Error #13 Type mismatch but hey, no red text. Here is what I have. I hope you can figure it out but I still appreciate your help if you can’t. My vba: strWhere = "Category = '" & Forms!frmDialogBox!cboCategory.Column(0) & "' " And "[Group] = '" & Forms!frmDialogBox!cboGroup.Column(0) & "'" If IsDate(Me.txtStartDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " = " & Format(Me.txtStartDate, strcJetDate) & ")" End If If IsDate(Me.txtEndDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " " & Format(Me.txtEndDate + 1, strcJetDate) & ")" End If Here is my SQL: SELECT tblTrans_Mstr.Category, tblTrans_Mstr.TransDate, tblTrans_Mstr.Group, tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr.Credit_GL_Acct, tblTrans_Mstr.Lbr_Hours, Sum(tblTrans_Mstr.Labor_Cost) AS SumOfLabor_Cost, tblTrans_Mstr.Lbr_OH_Rate, Sum(tblTrans_Mstr.Lbr_OH_Cost) AS SumOfLbr_OH_Cost, Sum([Labor_Cost]+[Lbr_OH_Cost]) AS LbrTotCost, tblTrans_Mstr.ODC_Cost_Category, Sum(tblTrans_Mstr.ODC_Cost) AS SumOfODC_Cost FROM tblTrans_Mstr GROUP BY tblTrans_Mstr.Category, tblTrans_Mstr.TransDate, tblTrans_Mstr.Group, tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions, tblTrans_Mstr.Credit_GL_Acct, tblTrans_Mstr.Lbr_Hours, tblTrans_Mstr.Lbr_OH_Rate, tblTrans_Mstr.ODC_Cost_Category HAVING (((tblTrans_Mstr.Category)=[Forms]![frmDialogBox]![cboCategory]) AND ((tblTrans_Mstr.TransDate)=[Forms]![frmDialogBox]![txtStartDate] And (tblTrans_Mstr.TransDate)=[Forms]![frmDialogBox]![txtEndDate]) AND ((tblTrans_Mstr.Group)=[Forms]![frmDialogBox]![cboGroup])); Thanks, Kay "Daryl S" wrote: AccessKay - If the query is pulling information from the form (as yours is), then the form must be open for the query to no ask for the values. I put the missing single-quote in on the second line below - if you get green text in an Access code window, then it is treated as a comment and ignored by the compiler. You usually put comments in by putting a single quote and then the comment. If the single quote is not a comment, but part of the line, then there is probably something missing (as you said, the matching single quote). strWhere = "Category = '" & Forms!frmDialogBox!cboCategory.Column(0) & "' AND " [Group] = '" & Forms!frmDialogBox!cboGroup.column(0) & "'" Hope this helps! -- Daryl S "AccessKay" wrote: Daryl, The report runs okay if I have the form open. If I don’t, then it asks for parameters. I have this feeling that it has something to do with the first strWhere. This is what I have right now: strWhere = "Category = '" & Forms!frmDialogBox!cboCategory.Column(0) & "' AND " [Group] = " & Forms!frmDialogBox!cboGroup.column(0) & "'" I noticed I had a missing ' after [Group] = from the version you sent me. If I put this in, the text turns red and I get a syntex error. I'm thinking I'm missing something else, but I can't tell what it is. Also, the last "'" at the end is green. I can send what you requested last time but I was hoping you could identify my error here. Thanks, Kay "Daryl S" wrote: AccessKay - Can you run the report by itself (rptLaborODC)? If not, then work on the report until it works by itself. Then work on the strWhere. It should be in your immediate window if you have the code open while you run the report from the form. There could be a typo or other issue there. If you can't figure it out, then post both the strWhere (copy/paste from the immediate window), and the SQL for your report's record source. -- Daryl S "AccessKay" wrote: Daryl, I got rid of the End If and put it all on one line…no more red. I appreciate the additional information. I think I understand but maybe not because I now have a new error message but I have no more red in my vba code and the bugger is not popping up so I’m not sure. The error message caption is Cannot Open Report and the error reads as: #2465: Microsoft can’t find the field “l” referred to in your expression. I looked all around for an “l” but didn’t see one. I REALLY appreciate your patience. I just have to be close to getting this to work. Any ideas about how to fix this error? Many, many thanks, Kay "Daryl S" wrote: AccessKay - There are two ways to do the If/Then statements. If there is only one statement to execute in the 'true' and optional 'else' clauses, then you can put them on one line, without an End If: If test Then result Else else result The more common way allows for multiple statements to be executed in the 'true' case and in the optional 'else' case. This format requires the End If (so it knows when the block of statements ends). If test Then result block Else else block End If Your statement could be either If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" End If '(Error message) or (this would be all on one line - the copy/paste may not show it that way) If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" -- Daryl S "AccessKay" wrote: Daryl, VERY useful to know and the red went away. The vba is running okay until it gets to the end and then I get this Compile error: End If without block If. It's this part he Err_Handler: If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report" End If (Error message) Resume Exit_Handler End Sub Can you help me fix this please? Thanks again, Kay "Daryl S" wrote: AccessKay - Yes, the copy/paste does add some line breaks... In Access code, if your statement will take up more than one line, you will need to tell access that the code continues on the next line. This is done with an underscore character, but must be in a logical place (not within double-quotes for example). So you can change the code by removing the line feeds or by adding in more line continuation characters, or a combination of the two. Like this: strWhere = "Category = '" & Forms!DialogBox!cboCategory.Column(0) & _ "' AND "[Group] = '" & _ Forms!DialogBox!cboGroup.column(0) & "'" Hope that helps! -- Daryl S "AccessKay" wrote: Thanks Daryl. I’m very new to vba. I replaced what I thought was the made-up names. In future posts, I’ll remember to state these names. Anyways, I’ve got a lot of red related to the strWhere areas. ' start here building the WHERE clause - substitute your real names here. strWhere = "Category = '" & Forms!DialogBox!cboCategory.Column(0) & "' AND " [Group] = '" & _ Forms!DialogBox!cboGroup.column(0) & "'" Here…I changed my form name to DialogBox and the name of my first combo which is cboCategory. I did the same for Group. The text turns red at [Group]. Then in the second block, you put a note to update. My text box is named txtStartDate so I didn’t change this. I wasn’t sure if there was another name in there that I needed to change. If IsDate(Me.txtStartDate) Then If strWhere vbNullString Then 'updated this block strWhere = strWhere & " AND " End If strWhere = strWhere & "(" & strDateField & " = " & Format(Me.txtStartDate, strcJetDate) & ")" The text goes red at strWhere = strWhere & “…. End If If IsDate(Me.txtEndDate) Then If strWhere vbNullString Then strWhere = strWhere & " AND " End If |
|
Thread Tools | |
Display Modes | |
|
|