Report critera - Date Range

  • Thread starter Thread starter Rockn
  • Start date Start date
R

Rockn

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
 
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?
 
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.
 
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!!
 
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
 
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 said:
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
Click to expand...
 
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 said:
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 said:
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 said:
Provide the code used to open the report.

--
Duane Hookom
Microsoft Access MVP


:

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.

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


:

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Provide the code used to open the report.

--
Duane Hookom
Microsoft Access MVP


:

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.

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


:

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top