Query Parameter not udpating Report

[krutoigoga]

Hello.
I built a query, which pulls data from a table and it has a date parameter i.e. Between [Start Date] And [End Date]), so when I open the query it prompts me for the start date and end date.
I also built a report, which calculates the number of records from the query mentioned above, I use: =DCount("[Product]","Issues","[Product] Like 'Fr*'"). The dcount is giving me an #Error.
I have the same dcount formula pulling data from a different query (which does not prompt for date) and it works fine.



Any suggestions on how to get it to show the # of records?

[maximus]

according to me the Dcount syntax is right and there is no problem with it. can you upload you database in Ms access 2000 format.

[krutoigoga]

Hi, please find the db attached. Thanks.

[maximus]

The problem is first your query has a criteria that you have to enter the beginning and the ending date. but when you run the report which has a text box control with the DCount function you get an error because you don't run the query the opening and the closing date is not provided. But if you keep the criteria static by specifying the opening date and the closing date in the criteria like Between #2/20/2010# And #2/20/2010# the count will be displayed. to prove my point I have done the following:

I have a Form1 that opens as a Startup. type opening and closing date and click on the command button. The Count for products FR is diplayed in the report in the first text box. In the second text box in the report I have added a Dcount function using a query Issues_all Query in which the dates criteria is static. The Dcount function shows no error.

I am attaching a mdb file for your reference.

[krutoigoga]

Thank you Maximus. I actually do have criteria for the date on a different query.
My idea was to automatically update numbers in the report based on the date paramet. with the query. the report has a dcount formula: =DCount("[Product]","Issues","[DATE] Between #4/1/2010# And #4/30/2010# AND [Product] Like 'Fr*'"), which can look at certain dates from a query, I just didnt want anyone to go into the report and start modifying formulas.
Thats why i wanted to set to date pramet. in the query to define dates and the dcount formula: =DCount("[Product]","Issues","[Product] Like 'Ft*'") to find only the results in the query withing that date range (which is set with the paramet. Between [Start Date] And [End Date] in the query).
Any other way I can auto update the report with a certain date range in the query without modifying the formulas?

[maximus]

Sorry to say this but I think you have not gone through the sample database that I had attached atall, and if you did go through it you understood nearly nothing.

For your problem I wouldn't even create a query. I have a start up form which allows you to type the begining and the ending date and the report has a text box with the folowing DCount formula in its ControlSource:

=DCount("[Product]","Issues_all","[Product] Like 'Fr*' And dates Between #" & [Forms]![Form1]![Text0] & "# And #" & [Forms]![Form1]![Text2] & "#")


Here the Begining Date and the Ending date is provided by the Text Boxes on the Form. Then they are passed into the criteria of the the DCount Function. This serves all your purposes:
1) The Date range is Dynamic so the user can specify it
2) You don't get an error.

There may be a situation when no products starting with "Fr*" is available in a particular date range to prevent an error being displayed in the text box use this code:

=If(isNull(DCount("[Product]","Issues_all","[Product] Like 'Fr*' And dates Between #" & [Forms]![Form1]![Text0] & "# And #" & [Forms]![Form1]![Text2] & "#")),0,DCount("[Product]","Issues_all","[Product] Like 'Fr*' And dates Between #" & [Forms]![Form1]![Text0] & "# And #" & [Forms]![Form1]![Text2] & "#"))

I just didnt want anyone to go into the report and start modifying formulas.

This following statement is completely misplaced here. Because these formulas are not required to be modified as the date range is derived from the textboxes on the form and hence dynamic.

I tried to explain why you were geting an error. When your query Criteria for dates is set to Between [Type:Begin Date] And [Type:End Date] you cannot use this query because untill and unless you run the query the date range is not provided and thus the query cannot pull up the reqired data from the tables. But if the criteria is static that is predifined and not needed to be inputed by the user like Between #4/1/2010# And #4/30/2010# then you can use the query to get you DCount going.

Hope I was able to explain myself clearly

[krutoigoga]

Thanks for your help Maximus. I'm new with access so didn''t completely understand the sample db, also I do not have much knowledge of scripting.
Your form does resolve my issue. I do have 2 requests:

1> I cannot figure out one thing in your attached sample. I have renamed the table name in the formula you provided from Issues_all to issues and i am not getting the values. So when i modify: =DCount("[Product]","Issues_all","[Product] Like 'Fr*' And dates Between #" & [Forms]![Form1]![Text0] & "# And #" & [Forms]![Form1]![Text2] & "#")
to
=DCount("[Product]","Issues","[Product] Like 'Fr*' And dates Between #" & [Forms]![Form1]![Text0] & "# And #" & [Forms]![Form1]![Text2] & "#")
The table I have is names issues and I did not want to rename it, as It is linked to multiple other reports/queries.

2> Is there a way to open the report in "report view" insptead of print prview? I tried modifying the code from :
DoCmd.OpenReport stDocName, acPreview
to
DoCmd.OpenReport stDocName
but that didnt work.

Please advise. Thanks.

[maximus]

As far as I remember Issues_all was the table and Issues was the query where you had the Criteria put in. Check it, if it is so remove the Criteria from the dates Field from the Query. Make sure your dates Field Type is Date/Time.

There are Three View Options:
1) acViewDesign=This will open your report in design View
2) acViewNormal = This will print your report directly without opening it.
3) acViewPreview=This will open the report for you to see and the you can frint it from the File option on the menu bar.

I think you may want the third option
enxample: DoCmd.OpenReport "MyReport", acViewPreview

[krutoigoga]

Thanks I will use the DoCmd.OpenReport "MyReport", acViewPreview

As far as the Table name, you are right, the table name in the test upload is issues_all, I had uploaded a test db not the original and the name of the table is different in my original db. that is why i am trying to change the formula.

I checked the formula once again, and in my table the date/time section was names date .. the formula called for dates. It has now been corrected.

=DCount("[Product]","Name","[Product] Like 'Fr*' And dates Between #" & Forms![Audit_Form]!Text0 & "# And #" & Forms![Audit_Form]!Text2 & "#")


Thank you for all your help Maximus, I have learnt a lot.

[maximus]

plase mark the thread solved thanks