Using a date range with Dcount function

[mleberso]

I am trying to calculate the 2nd pass yield in a text box on my report. The control source is:


=[rptReworkPass].[Report]![CountOfStatus]/(([rptDailyPaintData].[Report]![AccessTotalsDefect1])-(Nz(DCount("*","tblRework","Status='WIP' And Date=Between [TempVars]![startDate] And [TempVars]![endDate]"),0)))


This is the number of reworked parts that passed divided by the total number of defects minus the number that are still works in progress. The issue is with date range and tempvar. I have tried this eqn using the general date() formula and it works fine, but I need it to capture data for a specific range that has been stored to a tempvar when the report is opened. I would appreciate any suggestions. It may just be an issue with the syntax. I am using Access 2007.

[ssanfu]

I think the problem is that "[TempVars]![startDate]" and "[TempVars]![endDate]" cannot be evaluated as dates. They are being seen as text, so when compared to a date, it chokes. Try this:

=[rptReworkPass].[Report]![CountOfStatus]/(([rptDailyPaintData].[Report]![AccessTotalsDefect1])-(Nz(DCount("*","tblRework","[Status] = 'WIP' And [Date] = Between #" & [TempVars]![startDate] & "# And #" & [TempVars]![endDate] & "#),0)))

***
FYI, be aware that "Date" is a reserved word in Access. You shouldn't use reserved words as object names. Also, "Date" is also a function name... using reserved words will cause you headaches and they are not very descriptive. "Date" of what??

See: Problem names and reserved words in Access

http://allenbrowne.com/AppIssueBadWord.html

[mleberso]

I tried using the code you gave me, and when I hit enter I reveive an error message saying: "The expression you entered is an invalid string: A string can be up to 2048 characters long, including opening and closing quotation marks." That code is not even close to 2048 characters...any ideas?

[mleberso]

Never Mind. I just created a query to filter the information by date and did the calculation based on that query. Thank you!

[RuralGuy]

A good solution! Do you want to use the Thread Tools at the top of the thread and mark this thread as Solved?