Report critera - Date Range

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
 
D

Duane Hookom

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?
 
R

Rockn

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.
 
R

Rockn

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!!
 
R

Rockn

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
 
D

Duane Hookom

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...
 
R

Rockn

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...
 
D

Duane Hookom

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Conditional DB reports 3
Muliple Critera 1
Getting rid of "to" when the date fields are null 4
Need Help with a VBA subroutine 0
Filter Report by Form - VBA Code 1
Summary Report 3
Including query dates in report 1
Access Report Average Miles/Gallon in Group Footer and Report Footer? 9

Top