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
|
|||
|
|||
Report critera - Date Range
I have an unbound form that is used to set the criteria for a report. I have
date fields for a start date and an end date. I want the report to return all records when the date fields are empty and cannot get it to work in VBA. I have it working when the date fields are populated, but when they are emty I get the error "Invalid Use of Null" I have tried a conditional statement to test for Null in both fields, but I get the same results. Seems like it should be straight forward, but I am probably looking at it too hard. Thanks |
#2
|
|||
|
|||
Report critera - Date Range
How are you using the controls for criteria: in the Where Condition or
hard-coded in the query? You stated "when the date fields are empty". Do you mean the date "controls" on the form or the date "fields" in the records? -- Duane Hookom Microsoft Access MVP "Rockn" wrote: I have an unbound form that is used to set the criteria for a report. I have date fields for a start date and an end date. I want the report to return all records when the date fields are empty and cannot get it to work in VBA. I have it working when the date fields are populated, but when they are emty I get the error "Invalid Use of Null" I have tried a conditional statement to test for Null in both fields, but I get the same results. Seems like it should be straight forward, but I am probably looking at it too hard. Thanks |
#3
|
|||
|
|||
Report critera - Date Range
The controls are used to set the criteria for the DoCmd.OpenReport where
criteria. It is only when either of the fields are empty that I get the error. The date fields on the form. "Duane Hookom" wrote in message ... How are you using the controls for criteria: in the Where Condition or hard-coded in the query? You stated "when the date fields are empty". Do you mean the date "controls" on the form or the date "fields" in the records? -- Duane Hookom Microsoft Access MVP "Rockn" wrote: I have an unbound form that is used to set the criteria for a report. I have date fields for a start date and an end date. I want the report to return all records when the date fields are empty and cannot get it to work in VBA. I have it working when the date fields are populated, but when they are emty I get the error "Invalid Use of Null" I have tried a conditional statement to test for Null in both fields, but I get the same results. Seems like it should be straight forward, but I am probably looking at it too hard. Thanks |
#4
|
|||
|
|||
Report critera - Date Range
Provide the code used to open the report.
-- Duane Hookom Microsoft Access MVP "Rockn" wrote: The controls are used to set the criteria for the DoCmd.OpenReport where criteria. It is only when either of the fields are empty that I get the error. The date fields on the form. "Duane Hookom" wrote in message ... How are you using the controls for criteria: in the Where Condition or hard-coded in the query? You stated "when the date fields are empty". Do you mean the date "controls" on the form or the date "fields" in the records? -- Duane Hookom Microsoft Access MVP "Rockn" wrote: I have an unbound form that is used to set the criteria for a report. I have date fields for a start date and an end date. I want the report to return all records when the date fields are empty and cannot get it to work in VBA. I have it working when the date fields are populated, but when they are emty I get the error "Invalid Use of Null" I have tried a conditional statement to test for Null in both fields, but I get the same results. Seems like it should be straight forward, but I am probably looking at it too hard. Thanks |
#5
|
|||
|
|||
Report critera - Date Range
I figured it out. My syntax with the use of single and double quotes was
incorrect. I was using [filedname] Like "*" instead of [filedname] Like '*' Thanks!! "Duane Hookom" wrote in message ... Provide the code used to open the report. -- Duane Hookom Microsoft Access MVP "Rockn" wrote: The controls are used to set the criteria for the DoCmd.OpenReport where criteria. It is only when either of the fields are empty that I get the error. The date fields on the form. "Duane Hookom" wrote in message ... How are you using the controls for criteria: in the Where Condition or hard-coded in the query? You stated "when the date fields are empty". Do you mean the date "controls" on the form or the date "fields" in the records? -- Duane Hookom Microsoft Access MVP "Rockn" wrote: I have an unbound form that is used to set the criteria for a report. I have date fields for a start date and an end date. I want the report to return all records when the date fields are empty and cannot get it to work in VBA. I have it working when the date fields are populated, but when they are emty I get the error "Invalid Use of Null" I have tried a conditional statement to test for Null in both fields, but I get the same results. Seems like it should be straight forward, but I am probably looking at it too hard. Thanks |
#6
|
|||
|
|||
Report critera - Date Range
Shoot, It worked for my Combo box being null, but not for the date fields. I
guess I am still looking for an answer. Dim stDocName As String Dim strRange As String Dim varBegin As Variant Dim varEnd As Variant Dim strBuilder As String varBegin = CDate(Me![Text0]) varEnd = CDate(Me![Text2]) If IsNull(Me.Combo4) Then strBuilder = "[BLD_ID] Like '*' " Else strBuilder = "[BLD_ID] = " & Me.Combo4 End If If varBegin = "" And varEnd = "" Then strRange = "[JOB_ClDate] Like '*' " Else strRange = "[JOB_ClDate] Between #" & varBegin & "# AND #" & varEnd & "#" End If stDocName = "rpt_BldBreakdown" DoCmd.OpenReport stDocName, acPreview, , strRange & " AND " & strBuilder "Duane Hookom" wrote in message ... Provide the code used to open the report. -- Duane Hookom Microsoft Access MVP "Rockn" wrote: The controls are used to set the criteria for the DoCmd.OpenReport where criteria. It is only when either of the fields are empty that I get the error. The date fields on the form. "Duane Hookom" wrote in message ... How are you using the controls for criteria: in the Where Condition or hard-coded in the query? You stated "when the date fields are empty". Do you mean the date "controls" on the form or the date "fields" in the records? -- Duane Hookom Microsoft Access MVP "Rockn" wrote: I have an unbound form that is used to set the criteria for a report. I have date fields for a start date and an end date. I want the report to return all records when the date fields are empty and cannot get it to work in VBA. I have it working when the date fields are populated, but when they are emty I get the error "Invalid Use of Null" I have tried a conditional statement to test for Null in both fields, but I get the same results. Seems like it should be straight forward, but I am probably looking at it too hard. Thanks |
#7
|
|||
|
|||
Report critera - Date Range
I would correct the names of your controls and use code like:
Dim stDocName As String Dim strWhere as String strWhere = "1=1 " If Not IsNull(Me.cboBldID) Then strWhere = strWhere & " AND [BLD_ID] = " & Me.cboBldID End If If not IsNull(Me.txtStartDate) Then strWhere = strWhere & " AND [JOB_ClDate] =#" & _ Me.txtStartDate & "# " End If If not IsNull(Me.txtEndDate) Then strWhere = strWhere & " AND [JOB_ClDate] =#" & _ Me.txtEndDate & "# " End If stDocName = "rpt_BldBreakdown" DoCmd.OpenReport stDocName, acPreview, , strWhere -- Duane Hookom Microsoft Access MVP "Rockn" wrote: Shoot, It worked for my Combo box being null, but not for the date fields. I guess I am still looking for an answer. Dim stDocName As String Dim strRange As String Dim varBegin As Variant Dim varEnd As Variant Dim strBuilder As String varBegin = CDate(Me![Text0]) varEnd = CDate(Me![Text2]) If IsNull(Me.Combo4) Then strBuilder = "[BLD_ID] Like '*' " Else strBuilder = "[BLD_ID] = " & Me.Combo4 End If If varBegin = "" And varEnd = "" Then strRange = "[JOB_ClDate] Like '*' " Else strRange = "[JOB_ClDate] Between #" & varBegin & "# AND #" & varEnd & "#" End If stDocName = "rpt_BldBreakdown" DoCmd.OpenReport stDocName, acPreview, , strRange & " AND " & strBuilder "Duane Hookom" wrote in message ... Provide the code used to open the report. -- Duane Hookom Microsoft Access MVP "Rockn" wrote: The controls are used to set the criteria for the DoCmd.OpenReport where criteria. It is only when either of the fields are empty that I get the error. The date fields on the form. "Duane Hookom" wrote in message ... How are you using the controls for criteria: in the Where Condition or hard-coded in the query? You stated "when the date fields are empty". Do you mean the date "controls" on the form or the date "fields" in the records? -- Duane Hookom Microsoft Access MVP "Rockn" wrote: I have an unbound form that is used to set the criteria for a report. I have date fields for a start date and an end date. I want the report to return all records when the date fields are empty and cannot get it to work in VBA. I have it working when the date fields are populated, but when they are emty I get the error "Invalid Use of Null" I have tried a conditional statement to test for Null in both fields, but I get the same results. Seems like it should be straight forward, but I am probably looking at it too hard. Thanks |
#8
|
|||
|
|||
Report critera - Date Range
Not sure what you mean by correcting the names unless you are referring to
the fact that they mirror the DB field names on occaision. I figured it out anyway and if the text field is Null I set it's value to "" and that solved the problem. "Duane Hookom" wrote in message ... I would correct the names of your controls and use code like: Dim stDocName As String Dim strWhere as String strWhere = "1=1 " If Not IsNull(Me.cboBldID) Then strWhere = strWhere & " AND [BLD_ID] = " & Me.cboBldID End If If not IsNull(Me.txtStartDate) Then strWhere = strWhere & " AND [JOB_ClDate] =#" & _ Me.txtStartDate & "# " End If If not IsNull(Me.txtEndDate) Then strWhere = strWhere & " AND [JOB_ClDate] =#" & _ Me.txtEndDate & "# " End If stDocName = "rpt_BldBreakdown" DoCmd.OpenReport stDocName, acPreview, , strWhere -- Duane Hookom Microsoft Access MVP "Rockn" wrote: Shoot, It worked for my Combo box being null, but not for the date fields. I guess I am still looking for an answer. Dim stDocName As String Dim strRange As String Dim varBegin As Variant Dim varEnd As Variant Dim strBuilder As String varBegin = CDate(Me![Text0]) varEnd = CDate(Me![Text2]) If IsNull(Me.Combo4) Then strBuilder = "[BLD_ID] Like '*' " Else strBuilder = "[BLD_ID] = " & Me.Combo4 End If If varBegin = "" And varEnd = "" Then strRange = "[JOB_ClDate] Like '*' " Else strRange = "[JOB_ClDate] Between #" & varBegin & "# AND #" & varEnd & "#" End If stDocName = "rpt_BldBreakdown" DoCmd.OpenReport stDocName, acPreview, , strRange & " AND " & strBuilder "Duane Hookom" wrote in message ... Provide the code used to open the report. -- Duane Hookom Microsoft Access MVP "Rockn" wrote: The controls are used to set the criteria for the DoCmd.OpenReport where criteria. It is only when either of the fields are empty that I get the error. The date fields on the form. "Duane Hookom" wrote in message ... How are you using the controls for criteria: in the Where Condition or hard-coded in the query? You stated "when the date fields are empty". Do you mean the date "controls" on the form or the date "fields" in the records? -- Duane Hookom Microsoft Access MVP "Rockn" wrote: I have an unbound form that is used to set the criteria for a report. I have date fields for a start date and an end date. I want the report to return all records when the date fields are empty and cannot get it to work in VBA. I have it working when the date fields are populated, but when they are emty I get the error "Invalid Use of Null" I have tried a conditional statement to test for Null in both fields, but I get the same results. Seems like it should be straight forward, but I am probably looking at it too hard. Thanks |
#9
|
|||
|
|||
Report critera - Date Range
IMHO control names like Text0, Text2, and Combo8 are not acceptable. It takes
a couple seconds to make your application more maintainable. -- Duane Hookom Microsoft Access MVP "Rockn" wrote: Not sure what you mean by correcting the names unless you are referring to the fact that they mirror the DB field names on occaision. I figured it out anyway and if the text field is Null I set it's value to "" and that solved the problem. "Duane Hookom" wrote in message ... I would correct the names of your controls and use code like: Dim stDocName As String Dim strWhere as String strWhere = "1=1 " If Not IsNull(Me.cboBldID) Then strWhere = strWhere & " AND [BLD_ID] = " & Me.cboBldID End If If not IsNull(Me.txtStartDate) Then strWhere = strWhere & " AND [JOB_ClDate] =#" & _ Me.txtStartDate & "# " End If If not IsNull(Me.txtEndDate) Then strWhere = strWhere & " AND [JOB_ClDate] =#" & _ Me.txtEndDate & "# " End If stDocName = "rpt_BldBreakdown" DoCmd.OpenReport stDocName, acPreview, , strWhere -- Duane Hookom Microsoft Access MVP "Rockn" wrote: Shoot, It worked for my Combo box being null, but not for the date fields. I guess I am still looking for an answer. Dim stDocName As String Dim strRange As String Dim varBegin As Variant Dim varEnd As Variant Dim strBuilder As String varBegin = CDate(Me![Text0]) varEnd = CDate(Me![Text2]) If IsNull(Me.Combo4) Then strBuilder = "[BLD_ID] Like '*' " Else strBuilder = "[BLD_ID] = " & Me.Combo4 End If If varBegin = "" And varEnd = "" Then strRange = "[JOB_ClDate] Like '*' " Else strRange = "[JOB_ClDate] Between #" & varBegin & "# AND #" & varEnd & "#" End If stDocName = "rpt_BldBreakdown" DoCmd.OpenReport stDocName, acPreview, , strRange & " AND " & strBuilder "Duane Hookom" wrote in message ... Provide the code used to open the report. -- Duane Hookom Microsoft Access MVP "Rockn" wrote: The controls are used to set the criteria for the DoCmd.OpenReport where criteria. It is only when either of the fields are empty that I get the error. The date fields on the form. "Duane Hookom" wrote in message ... How are you using the controls for criteria: in the Where Condition or hard-coded in the query? You stated "when the date fields are empty". Do you mean the date "controls" on the form or the date "fields" in the records? -- Duane Hookom Microsoft Access MVP "Rockn" wrote: I have an unbound form that is used to set the criteria for a report. I have date fields for a start date and an end date. I want the report to return all records when the date fields are empty and cannot get it to work in VBA. I have it working when the date fields are populated, but when they are emty I get the error "Invalid Use of Null" I have tried a conditional statement to test for Null in both fields, but I get the same results. Seems like it should be straight forward, but I am probably looking at it too hard. Thanks |
Thread Tools | |
Display Modes | |
|
|