Flashing #Error message using Dcount in Form control

[bmhuettinger]

Good morning all,

I've spent two days scouring the web for a solution to what should be a simple problem. I've got a summary form that displays various metrics for the month's shipments. The form has controls that reference several queries based on one table. I have Dsums and Dcounts that work fine but my issue is with a Dcount control ([On Time]) that is trying to count the number of orders that are "on time" based on query that first calculates the number of days late:

(user defined field) [Past Due]ateDiff("d",[due date],[Delete Dt])

And then assigns a status:

(user defined field) [Status]: IIf([Past Due] Between -8 And 0,"ON TIME: WEEK OF",IIf([Past Due]<-8,"MORE THAN 1 WEEK EARLY",IIf([Past Due] Between 1 And 7,"LATE:WEEK OF",IIf([Past Due] Between 7 And 14,"2 WEEKS LATE",IIf([Past Due]>14,"MORE THAN 3 WEEKS LATE","")))))

The query works fine. But I can't get the form control to count the orders that meet the criteria ("More than 1 week Early", "On Time: Week of", etc...

I've tried Dcount and Sum (assigning value to the results), and I believe that I've tried every iteration of the formula referenced on the internet.

most basic and most popular is this one:

=DCount("[orders]","qry_OnTimeDeliveryPerformance","[Status] = 'Two Weeks Late'")

Thank you in advance.

Brandi

 

[CJ_London]

possible because your calculation uses this

IIf([Past Due] Between 7 And 14,"2 WEEKS LATE",

and you are looking for

"[Status] = 'Two Weeks Late'"

why not just use the iif part

=DCount("[orders]","qry_OnTimeDeliveryPerformance","[Past Due] Between 7 And 14")

 

[bmhuettinger]

Thanks for the reply! I get the same "flashing" error. Is it because I'm still referencing a calculated field - [Past Due]?

 

[CJ_London]

Have just tried it here with dummy data and works OK for me

providing your calculated value is in a query it should be OK, so implies you have a typo somewhere.

Also suggest if using dcount, use "*" rather than "[orders]"

Although should not be a factor here, I recommend not using spaces in names - use the caption property if you need them, or the underscore. Reason is if you have a control called 'my field', if you write code, it will be changed to 'my_field' which can cause confusion

 

[bmhuettinger]

I usually don't use spaces - everything that I create is TitleCase but I'm working with imported data most of the time. Ironically, the reason that this calculated field currently exists as "Past Due" rather than PastDue is because I was having an earlier issue where I was getting a parameter request every time I ran the query and I thought perhaps it was taking issue with the name (it wasn't of course, it was because I was using Totals, but I was frustrated and trying everything). I digress.

I changed the "orders" to the asterisk and BAM! - two days of angst and irritation (and cursing) are seemingly behind me. I HAD tried the asterisk before but I think at the time, I hadn't yet resolved the query/parameter issue so my formula was probably correct and my query was wrong.
Either way! Thank you very, very much!