DCount function problem

[10 Gauge]

I am trying to have a form reference two fields on an unlinked table to make it's computation from. It works just fine with one field, but I don't think I have the code right for what I want it to do.



This is what I have:

=IIf(DCount("*","[Tasks - DC]","[Update Date] < Date() + 2")>0 & DCount("*","[Tasks - DC]","[Status]")="In Progress","DC Updates Due","")

I want the form to check the Update Date and Status and if the date is less than 2 days away AND has a status of "In Progress" to display the text "DC Updates Due".

Currently I am using this:

=IIf(DCount("*","[Tasks - DC]","[Update Date] < Date() + 2")>0,"DC Updates Due","")

And it works just fine but I have run in to a problem with it reading completed tasks as well so now it it showing the text all the time, I need it to narrow down to only tasks that are showing in progress.

Thanks for the help!

[pbaldy]

Is this what you're after?

DCount("*","[Tasks - DC]","[Update Date] < Date() + 2 AND Status = 'In Progress'")

[jzwp11]

I think you want something like this where the criteria are contained within the same DCount() function

IIf(DCount("*","[Tasks - DC]","[Update Date] <#" &  dateadd("d",2,Date()) &  "# AND [Status]='In Progress'")>0,"DC Updates Due","")

[10 Gauge]

Is this what you're after?

DCount("*","[Tasks - DC]","[Update Date] < Date() + 2 AND Status = 'In Progress'")

Thank you, this worked!

@jzwp11: Sorry I haven't got back to you on the other thread about the other database. I have been tied up doing other things around the office. Hopefully later this week I will be able to get back to it. Thanks for all of your help so far with it.

[ssanfu]

I see a couple of problems...

=IIf(DCount("*","[Tasks - DC]","[Update Date] < Date() + 2")>0  & DCount("*","[Tasks - DC]","[Status]")="In Progress","DC Updates  Due","")

* You are using the concatenation operator instead of the comparison operator "AND".

Looking at the two parts of the condition part of the IIF() function, the first one is OK:

DCount("*","[Tasks - DC]","[Update Date] < Date() + 2")>0

but the second one is wrong:

DCount("*","[Tasks - DC]","[Status]")="In Progress"

*The closing parenthesis is in the wrong place
*There should be single quotes (or doubled double quotes) around "In Progress"
* Shouldn't the result be compared to zero like the first condition??

DCount("*","[Tasks - DC]","[Status] = 'In Progress'")>0

So your formula should be like this:

=IIf(DCount("*","[Tasks - DC]","[Update Date] < Date() +  2")>0  & DCount("*","[Tasks - DC]","[Status] = 'In Progress'")>0,"DC Updates Due","")

So, if the first DCount() >0 AND the second DCount() >0, then use "DC Updates Due" else use ""




As an aside, you shouldn't have spaces or use special characters in your object names.

[Tasks - DC] should be something like [TasksDC] or [Tasks_DC]
[Update Date] should be something like [UpdateDate] or [Update_Date]

Only the programmer should ever see the object names, so 'proper' usage of spaces between words doesn't matter.

(Also see http://mvps.org/access/tencommandments.htm )

[ssanfu]

Dang you guys are fast!!!