A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Report critera - Date Range



 
 
Thread Tools Display Modes
  #1  
Old July 26th, 2007, 02:22 PM posted to microsoft.public.access.reports
Rockn
external usenet poster
 
Posts: 48
Default 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  
Old July 26th, 2007, 03:14 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default 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  
Old July 26th, 2007, 04:13 PM posted to microsoft.public.access.reports
Rockn
external usenet poster
 
Posts: 48
Default 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  
Old July 26th, 2007, 04:40 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default 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  
Old July 26th, 2007, 04:49 PM posted to microsoft.public.access.reports
Rockn
external usenet poster
 
Posts: 48
Default 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  
Old July 26th, 2007, 04:56 PM posted to microsoft.public.access.reports
Rockn
external usenet poster
 
Posts: 48
Default 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  
Old July 26th, 2007, 05:30 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default 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  
Old July 26th, 2007, 07:37 PM posted to microsoft.public.access.reports
Rockn
external usenet poster
 
Posts: 48
Default 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  
Old July 26th, 2007, 09:00 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:31 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.