Results 1 to 6 of 6
  1. #1
    10 Gauge's Avatar
    10 Gauge is offline Getting the hang of it...
    Windows XP Access 2007
    Join Date
    Feb 2011
    Location
    Fredericksburg, VA
    Posts
    202

    DCount function problem

    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!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Is this what you're after?

    DCount("*","[Tasks - DC]","[Update Date] < Date() + 2 AND Status = 'In Progress'")
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I think you want something like this where the criteria are contained within the same DCount() function


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

  4. #4
    10 Gauge's Avatar
    10 Gauge is offline Getting the hang of it...
    Windows XP Access 2007
    Join Date
    Feb 2011
    Location
    Fredericksburg, VA
    Posts
    202
    Quote Originally Posted by pbaldy View Post
    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.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I see a couple of problems...

    Code:
    =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:

    Code:
    DCount("*","[Tasks - DC]","[Update Date] < Date() + 2")>0
    but the second one is wrong:

    Code:
    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??


    Code:
    DCount("*","[Tasks - DC]","[Status] = 'In Progress'")>0
    So your formula should be like this:

    Code:
    =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 )

  6. #6
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Dang you guys are fast!!!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. DCount and SQL
    By DSTR3 in forum Queries
    Replies: 3
    Last Post: 12-06-2010, 03:07 PM
  2. Problem with Environ function in 2003 SP3
    By wgreynol in forum Programming
    Replies: 2
    Last Post: 10-21-2010, 11:07 PM
  3. VBA Function problem
    By smikkelsen in forum Programming
    Replies: 5
    Last Post: 07-16-2010, 07:46 AM
  4. dlookup function problem
    By bdaniel in forum Programming
    Replies: 3
    Last Post: 04-26-2010, 05:55 AM
  5. I have Problem in processing Dlookup Function
    By Katada in forum Programming
    Replies: 2
    Last Post: 04-23-2006, 12:07 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums