Report works for records entered this year but not previous years.

[jer007]

I have a report that works great for records entered this year. I enter in the start range into a form with an unbound text box txtstart and the end range into an unbound text box txtend. I then select the report I want and it selects the appropriate query and runs the report. I am now trying to run the same report to compare the first half of this year to the first half of last year but am unable to get any records for last years records.
The criteria for my query is Between [Forms]![RunPermitReport]![txtStart] And [Forms]![RunPermitReport]![txtEnd]

If anyone can help me fix this error so that I can run reports for any year I would appriciate the help.

Thanks

-Jeremy

 

[lupins46]

Then you have to conclude either that there are none or that the current year's report is also wrong.

Post the sql view of the query used by the report.

 

[jer007]

This is the SQL for the query.

SELECT tblPermit.area, Count(tblPermit.Approved) AS CountOfApproved, Sum(tblPermit.TotalFees) AS SumOfTotalFees, Sum(tblPermit.ValueUponCompletion) AS SumOfValueUponCompletion
FROM tblPermit
WHERE (((tblPermit.ApprovedDate) Between [Forms]![RunPermitReport]![txtStart] And [Forms]![RunPermitReport]![txtEnd]) AND ((tblPermit.Approved)=True))
GROUP BY tblPermit.area;

We have verified that the report for 2005 is correct. I created a temp query that gave me 265 records with the following:
SELECT tblPermit.Date, tblPermit.Approved
FROM tblPermit
WHERE (((tblPermit.Date) Like "2004-*-*") AND ((tblPermit.Approved)=True));

 

[lupins46]

So your date fields are really textfields is that correct?

 

[jer007]

date has a data type of Date/Time. I am using an unbound text box in a form to enter the range I want the report for.

 

[lupins46]

I think my problem would be why you are getting any sensible answers at all.
This ' Like "2004-*-*" ' is not valid syntax. You can't use wildcards with dates.

To get all records for 2004 you could use a date range or you could create a new column yr:year(mydatefield) and test that.

On the other hand I can't see anything wrong with your main query. You could try setting the parameters explicitly as datetime (Query>Paramters) but that's all.

(You ARE entering proper dates into the parameter boxes on the form,I take it - you're not trying to include wildcards there as well are you?)

 

[jer007]

I tried setting the parameters in the query like this SELECT tblPermit.area, Count(tblPermit.Approved) AS CountOfApproved, Sum(tblPermit.TotalFees) AS SumOfTotalFees, Sum(tblPermit.ValueUponCompletion) AS SumOfValueUponCompletion
FROM tblPermit
WHERE (((tblPermit.ApprovedDate) Between #1/1/2004# And #6/30/2004#) AND ((tblPermit.Approved)=True))
GROUP BY tblPermit.area;
But I still get no results.

 

[lupins46]

That query looks absolutely fine.

If it produces nothing there are no records which satisfy the criteria.
Is 'Approved' a Boolean (Yes/No) field?

Can I suggest that you create a new query using this:
(just drop it into the SQL view of any new query)

SELECT *
FROM tblPermit
WHERE ApprovedDate Between #1/1/2004# And #6/30/2004#

and see if you get any results.

 

[jer007]

I just got it. There was something wrong with my data, I've got it fixed now. Thanks for the help