Error 3071 for dates after July 3 2008

[Ary]

I have several reports giving this error. The reports all require To and From
date parameters. They all work perfectly fine for any datae range provided
that ends before July 3, but the second you put in a date from July 4
onwards, you get the 3071 error.

I have already tried switching between a basic parameter prompt and a form
supplying the criteria to the feeder queries.

I am getting the same error on the series of queries that feed up into these
reports, except for the base query that originally required the date range.

The structure of my queries is as follows:
QA 2 requires the input of a date range.
Count of Task takes a small subset of details from QA 2 and Totals them (one
field is a Sum, one is a Count, and the rest are 'Group by')
Average Score by Task takes the totals from Count of Task and adds one extra
field - (Average Score: [SumOfScore]/[CountOfContact QAID])
The report pulls information from Average Score by Task and displays it in
prettified format.

I can open QA 2 with no problems regardless of dates entered, but I cannot
open Count of Task, Average Score by Task, or the report without getting a
3071 error.


Anyone have any ideas what is going on here and how I can fix it? It all
worked seamlessly up until yesterday, and now it's broken somewhere.

 

[Allen Browne]

Blew up after 4th of July, eh?

The error probably means that Access/JET is not understanding the parameters
as dates, so the solution will be to explicitly declare the parameters. In
the query, you have something like [Date From] in the Criteria row? Choose
Parameters on the Query menu. Access pops up a dialog. Enter a row like
this:
[Date From] Date/Time

If the parameter looks like [Forms].[Form1].[txtStartDate], then enter than
name in the Parameters dialog
[Forms].[Form1].[txtStartDate] Date/Time
Also, if txtStartDate is an unbound textbox, set its Format property to
Short Date or similar so Access knows its data type.

If that doesn't solve it, there may be some other expression that Access
misunderstands the data type of.

 

[Ary]

Double checked all of these suggestions, they were already in place. The
whole mess still works just fine if I enter the date range as, say, June 1 -
July 3. But make it June 2 - July 4 and it starts coughing up errors. None of
these problems happen when I try to use the first layer of queries though.
I've tried entering the dates in a variety of formats, I've changed the data
type into a variety of different date fields. Hell, I've even already deleted
queries and built them again from scratch.
--
-Ary

Blew up after 4th of July, eh?

The error probably means that Access/JET is not understanding the parameters
as dates, so the solution will be to explicitly declare the parameters. In
the query, you have something like [Date From] in the Criteria row? Choose
Parameters on the Query menu. Access pops up a dialog. Enter a row like
this:
[Date From] Date/Time

If the parameter looks like [Forms].[Form1].[txtStartDate], then enter than
name in the Parameters dialog
[Forms].[Form1].[txtStartDate] Date/Time
Also, if txtStartDate is an unbound textbox, set its Format property to
Short Date or similar so Access knows its data type.

If that doesn't solve it, there may be some other expression that Access
misunderstands the data type of.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have several reports giving this error. The reports all require To and
From
date parameters. They all work perfectly fine for any datae range provided
that ends before July 3, but the second you put in a date from July 4
onwards, you get the 3071 error.

I have already tried switching between a basic parameter prompt and a form
supplying the criteria to the feeder queries.

I am getting the same error on the series of queries that feed up into
these
reports, except for the base query that originally required the date
range.

The structure of my queries is as follows:
QA 2 requires the input of a date range.
Count of Task takes a small subset of details from QA 2 and Totals them
(one
field is a Sum, one is a Count, and the rest are 'Group by')
Average Score by Task takes the totals from Count of Task and adds one
extra
field - (Average Score: [SumOfScore]/[CountOfContact QAID])
The report pulls information from Average Score by Task and displays it in
prettified format.

I can open QA 2 with no problems regardless of dates entered, but I cannot
open Count of Task, Average Score by Task, or the report without getting a
3071 error.


Anyone have any ideas what is going on here and how I can fix it? It all
worked seamlessly up until yesterday, and now it's broken somewhere.

 

[Ary]

I've double checked all the items you've suggested - they were all in place.
Everything works fine if I enter dates like June 1 to July 3, but if I change
it to June 2 to July 4, it all blows up.

I've even deleted the offending queries and built them again from scratch
just to make sure nothing had been accidentally changed without my knowledge,
error is still happening, and only started doing this this week (worked fine
last week, even with dates after July 3).
--
-Ary

Blew up after 4th of July, eh?

The error probably means that Access/JET is not understanding the parameters
as dates, so the solution will be to explicitly declare the parameters. In
the query, you have something like [Date From] in the Criteria row? Choose
Parameters on the Query menu. Access pops up a dialog. Enter a row like
this:
[Date From] Date/Time

If the parameter looks like [Forms].[Form1].[txtStartDate], then enter than
name in the Parameters dialog
[Forms].[Form1].[txtStartDate] Date/Time
Also, if txtStartDate is an unbound textbox, set its Format property to
Short Date or similar so Access knows its data type.

If that doesn't solve it, there may be some other expression that Access
misunderstands the data type of.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have several reports giving this error. The reports all require To and
From
date parameters. They all work perfectly fine for any datae range provided
that ends before July 3, but the second you put in a date from July 4
onwards, you get the 3071 error.

I have already tried switching between a basic parameter prompt and a form
supplying the criteria to the feeder queries.

I am getting the same error on the series of queries that feed up into
these
reports, except for the base query that originally required the date
range.

The structure of my queries is as follows:
QA 2 requires the input of a date range.
Count of Task takes a small subset of details from QA 2 and Totals them
(one
field is a Sum, one is a Count, and the rest are 'Group by')
Average Score by Task takes the totals from Count of Task and adds one
extra
field - (Average Score: [SumOfScore]/[CountOfContact QAID])
The report pulls information from Average Score by Task and displays it in
prettified format.

I can open QA 2 with no problems regardless of dates entered, but I cannot
open Count of Task, Average Score by Task, or the report without getting a
3071 error.


Anyone have any ideas what is going on here and how I can fix it? It all
worked seamlessly up until yesterday, and now it's broken somewhere.

 

[Allen Browne]

Where are these parameters in the query?
Are they in the Criteria line?

What type of field are they under?
Is it a field from a table, or a calculated query field?
If from a table, open the table in design view, and tell us what type of
field it is: text? date/time?

There has to be a data mis-match here somewhere.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I've double checked all the items you've suggested - they were all in
place.
Everything works fine if I enter dates like June 1 to July 3, but if I
change
it to June 2 to July 4, it all blows up.

I've even deleted the offending queries and built them again from scratch
just to make sure nothing had been accidentally changed without my
knowledge,
error is still happening, and only started doing this this week (worked
fine
last week, even with dates after July 3).
--
-Ary

Blew up after 4th of July, eh?

The error probably means that Access/JET is not understanding the
parameters
as dates, so the solution will be to explicitly declare the parameters.
In
the query, you have something like [Date From] in the Criteria row?
Choose
Parameters on the Query menu. Access pops up a dialog. Enter a row like
this:
[Date From] Date/Time

If the parameter looks like [Forms].[Form1].[txtStartDate], then enter
than
name in the Parameters dialog
[Forms].[Form1].[txtStartDate] Date/Time
Also, if txtStartDate is an unbound textbox, set its Format property to
Short Date or similar so Access knows its data type.

If that doesn't solve it, there may be some other expression that Access
misunderstands the data type of.

I have several reports giving this error. The reports all require To and
From
date parameters. They all work perfectly fine for any datae range
provided
that ends before July 3, but the second you put in a date from July 4
onwards, you get the 3071 error.

I have already tried switching between a basic parameter prompt and a
form
supplying the criteria to the feeder queries.

I am getting the same error on the series of queries that feed up into
these
reports, except for the base query that originally required the date
range.

The structure of my queries is as follows:
QA 2 requires the input of a date range.
Count of Task takes a small subset of details from QA 2 and Totals them
(one
field is a Sum, one is a Count, and the rest are 'Group by')
Average Score by Task takes the totals from Count of Task and adds one
extra
field - (Average Score: [SumOfScore]/[CountOfContact QAID])
The report pulls information from Average Score by Task and displays it
in
prettified format.

I can open QA 2 with no problems regardless of dates entered, but I
cannot
open Count of Task, Average Score by Task, or the report without
getting a
3071 error.


Anyone have any ideas what is going on here and how I can fix it? It
all
worked seamlessly up until yesterday, and now it's broken somewhere.

 

[Ary]

I have switched back to using basic parameters instead of the form (for ease
of testing, if I can make it work, I will switch back to the form later).

The parameters existing in the query QA 2. Parameters are [From Date], which
is a date/time data type, and [To Date], another date/time data type.

In QA 2, there is a field called [Date Monitored], also a date/time data
type. In the criteria for this field, I have the expression

=[From Date] AND <=[To Date]

There is also a second (text) field as follows:
Period: [From Date] & " to " [To Date]

This query, with the above fields, works perfectly fine.

The second query, Count of Task, is drawn from the first query. The Count of
Task query has no parameters of it's own, but draws several text and number
fields as well as the Period field from the QA 2 query.


--
-Ary

Where are these parameters in the query?
Are they in the Criteria line?

What type of field are they under?
Is it a field from a table, or a calculated query field?
If from a table, open the table in design view, and tell us what type of
field it is: text? date/time?

There has to be a data mis-match here somewhere.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I've double checked all the items you've suggested - they were all in
place.
Everything works fine if I enter dates like June 1 to July 3, but if I
change
it to June 2 to July 4, it all blows up.

I've even deleted the offending queries and built them again from scratch
just to make sure nothing had been accidentally changed without my
knowledge,
error is still happening, and only started doing this this week (worked
fine
last week, even with dates after July 3).
--
-Ary

Blew up after 4th of July, eh?

The error probably means that Access/JET is not understanding the
parameters
as dates, so the solution will be to explicitly declare the parameters.
In
the query, you have something like [Date From] in the Criteria row?
Choose
Parameters on the Query menu. Access pops up a dialog. Enter a row like
this:
[Date From] Date/Time

If the parameter looks like [Forms].[Form1].[txtStartDate], then enter
than
name in the Parameters dialog
[Forms].[Form1].[txtStartDate] Date/Time
Also, if txtStartDate is an unbound textbox, set its Format property to
Short Date or similar so Access knows its data type.

If that doesn't solve it, there may be some other expression that Access
misunderstands the data type of.

I have several reports giving this error. The reports all require To and
From
date parameters. They all work perfectly fine for any datae range
provided
that ends before July 3, but the second you put in a date from July 4
onwards, you get the 3071 error.

I have already tried switching between a basic parameter prompt and a
form
supplying the criteria to the feeder queries.

I am getting the same error on the series of queries that feed up into
these
reports, except for the base query that originally required the date
range.

The structure of my queries is as follows:
QA 2 requires the input of a date range.
Count of Task takes a small subset of details from QA 2 and Totals them
(one
field is a Sum, one is a Count, and the rest are 'Group by')
Average Score by Task takes the totals from Count of Task and adds one
extra
field - (Average Score: [SumOfScore]/[CountOfContact QAID])
The report pulls information from Average Score by Task and displays it
in
prettified format.

I can open QA 2 with no problems regardless of dates entered, but I
cannot
open Count of Task, Average Score by Task, or the report without
getting a
3071 error.


Anyone have any ideas what is going on here and how I can fix it? It
all
worked seamlessly up until yesterday, and now it's broken somewhere.

 

[Allen Browne]

There is also a second (text) field as follows:
Period: [From Date] & " to " [To Date]

There's the mismatch. You are applying Date/Time parameters against a Text
type field.

 

[Ary]

Thought of that after I responded, so I tried going in and getting rid of
that field entirely. I now have Count of Task drawing from another query that
does not have any parameters, and Count of Task has it's own [From Date] and
[To Date] parameters, both date/time datatype. These are used in the field
[Date Monitored] criteria in the expression

=[From Date] AND <=[To Date]

With no text field in sight, I'm STILL getting the ridiculous 3071 error.
It's enough to make you smack your head against your desk!
--
-Ary

There is also a second (text) field as follows:
Period: [From Date] & " to " [To Date]

There's the mismatch. You are applying Date/Time parameters against a Text
type field.

 

[Ary]

Just to finish up the thread, I finally got it figured out. There was nothing
wrong with the query or design, the problem was the result of a corrupted
record in the source data. All that smacking my head against a desk and
doubting my designs, and I never even thought to check the data itself!
--
-Ary

There is also a second (text) field as follows:
Period: [From Date] & " to " [To Date]

There's the mismatch. You are applying Date/Time parameters against a Text
type field.

 

[David W. Fenton]

Just to finish up the thread, I finally got it figured out. There
was nothing wrong with the query or design, the problem was the
result of a corrupted record in the source data. All that smacking
my head against a desk and doubting my designs, and I never even
thought to check the data itself!

What kind of corruption? I so seldom see any form of corruption in
Access databases that I'd probably never consider this, either.

 

[Ary]

That's got me stumped still. The [Date Monitored] field was null in two
records, where it should have been July 4 and July 12. No clue how it got to
be that way, since it's a required field exactly so that this doesn't happen,
and on top of being a required field, it's also got a default value that
should pre-populate. Never seen it before, probably won't ever see it again,
but I've learned my lesson about checking the source data if the design
appears to be fine.

 

[David W. Fenton]

That's got me stumped still. The [Date Monitored] field was null
in two records, where it should have been July 4 and July 12. No
clue how it got to be that way, since it's a required field
exactly so that this doesn't happen, and on top of being a
required field, it's also got a default value that should
pre-populate. Never seen it before, probably won't ever see it
again, but I've learned my lesson about checking the source data
if the design appears to be fine.

Aha. That's not "corruption" in the sense of what you fix with a
compact and repair, but corruption of the data.

I can see how the default value can be gone, but not how a required
field could end up Null. Weird.