Access 2007 Report Form

[BrucesAccessWorldFor]

We are using a Access Database at work that records our customers as they register to use our facilities. I am using a query to pull information from a table to use to count (using DCount) the number of from branch field and rank field. It works fine embedded in the report form. But as soon as I put a between command in the query to select a date range I get a #Error in the calculated field. The dcount criteria field is as follows: =DCount("*","Records_by_date_qry","[Records_by_date_qry]![Branch]='USMC' AND [Records_by_date_qry]![Rank]='E1'"). In the records by date qry I have this in the critera for Date: Between [Enter Start Date] And [Enter End Date]. Like I said when I take the between out of the query it adds the fields correctly, but when it's in I get the #Error. Thanks for your help.

 

[vbaInet]

DCount() doesn't know what to do with the parameter because it cannot prompt you to enter it.

Welcome to the forum.

 

[BrucesAccessWorldFor]

So. Then how do I use the dcount that will include only a date range period.

 

[vbaInet]

You can include it in the third parameter of the function but you won't get a prompt. If you want something that prompts, then you need to create a form with textboxes and get your values from those textboxes.

 

[BrucesAccessWorldFor]

So if i understand you if i put that date request in the header of the report it shoud work. I will give it a try thank you

 

[vbaInet]

Not quite. I said:

1. Create a pop-up form that will contain two textboxes
2. Use the textboxes to enter the dates
3. Change your BETWEEN code to be:

BETWEEN [Forms]![[COLOR=Red]FormName[/COLOR]]![[COLOR=Red]txtStartDate[/COLOR]] AND [Forms]![[COLOR=Red]FormName[/COLOR]]![[COLOR=Red]txtEndDate[/COLOR]]

... where FormName should be substituted for the name of the form you created in step 1 and txtStartDate and txtEndDate are the names of textboxes
4. Run your query to test it and ensure that the form remains open (and with dates entered) before running the query.