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
|
|||
|
|||
Error 3071 for dates after July 3 2008
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 Sco [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 |
#2
|
|||
|
|||
Error 3071 for dates after July 3 2008
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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Ary" wrote in message ... 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 Sco [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 |
#3
|
|||
|
|||
Error 3071 for dates after July 3 2008
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 "Allen Browne" wrote: 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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Ary" wrote in message ... 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 Sco [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 |
#4
|
|||
|
|||
Error 3071 for dates after July 3 2008
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 "Allen Browne" wrote: 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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Ary" wrote in message ... 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 Sco [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 |
#5
|
|||
|
|||
Error 3071 for dates after July 3 2008
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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Ary" wrote in message ... 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 "Allen Browne" wrote: 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" wrote in message ... 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 Sco [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. |
#6
|
|||
|
|||
Error 3071 for dates after July 3 2008
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 "Allen Browne" wrote: 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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Ary" wrote in message ... 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 "Allen Browne" wrote: 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" wrote in message ... 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 Sco [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. |
#7
|
|||
|
|||
Error 3071 for dates after July 3 2008
Ary" wrote in message
... 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. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. |
#8
|
|||
|
|||
Error 3071 for dates after July 3 2008
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 "Allen Browne" wrote: Ary" wrote in message ... 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. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. |
#9
|
|||
|
|||
Error 3071 for dates after July 3 2008
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 "Allen Browne" wrote: Ary" wrote in message ... 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. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. |
#10
|
|||
|
|||
Error 3071 for dates after July 3 2008
=?Utf-8?B?QXJ5?= wrote in
: 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. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
|
Thread Tools | |
Display Modes | |
|
|