Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397

    Unanswered: Dcount with 3 criteria

    Hi,

    I am trying to count records (just number) for a specific period based on VisitDate and a column named "P_GumsProphylaxis". Visit Date range I am providing from a DtFrom and DtTo placed on form F_Reports The column P_GumsProphylaxis contains "-" if there is no data.

    Actually I placed one unbound text box name called "VisitCount" on F_Report form and I need to get the total records as figure in this after clicking a command button

    Would it be ok if I'll use Dcount in following way:

    VisitCount = DCount("*", "T_DiagRec", _
    "[P_GumsProphylaxis] = "-" & _
    "' AND [VisitDate] >= # " & Forms!F_Reports!DtFrom & "#" & _
    "And [VisitDate]<= # " & Forms!F_Reports!DtTo & "#")

    But it produces error in above. Can someone look at this error

    Also I want to know if using Dcount is ok to count the records figure and display it on a form? Or is there any other trick to do so?

    With kind regards,
    Ashfaque

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Just looking at it, and assuming "-" is a text field then try this

    VisitCount = DCount("*", "T_DiagRec", _
    "[P_GumsProphylaxis] = '-'" & _
    " AND [VisitDate] >= #" & Forms!F_Reports!DtFrom & "#" & _
    " AND [VisitDate] <= #" & Forms!F_Reports!DtTo & "#")

    If this is all in F_Reports module then this would do

    VisitCount = DCount("*", "T_DiagRec", _
    "[P_GumsProphylaxis] = '-'" & _
    " AND [VisitDate] >= #" & DtFrom & "#" & _
    " AND [VisitDate] <= #" & DtTo & "#")


    Also if this is in code and you use British dates (??) on you PC then you may need this.

    VisitCount = DCount("*", "T_DiagRec", _
    "[P_GumsProphylaxis] = '-'" & _
    " AND [VisitDate] >= #" & Format(DtFrom,"mm/dd/yy") & "#" & _
    " AND [VisitDate] <= #" & Format(DtTo,"mm/dd/yy") & "#")

    OR

    VisitCount = DCount("*", "T_DiagRec", _
    "[P_GumsProphylaxis] = '-'" & _
    " AND [VisitDate] BETWEEN #" & Format(DtFrom,"mm/dd/yy") & "#" & _
    " AND #" & Format(DtTo,"mm/dd/yy") & "#")


    Just a thought.



    MTB

  3. #3
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks Mike,

    I tried with all the 4 examples. But the output is 0 in textbox.
    I tried to change from "[P_GumsProphylaxis] = '-'" to "[P_GumsProphylaxis] <> '-'" bcz we need get theose records only which contains data and not the - sign.

    Any other idea?

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Have you tried using each of the three critia on their own, to check each one's syntax, and that they all return the correct count ??


    MTB

  5. #5
    Join Date
    Aug 2002
    Posts
    33
    The dcount's (and all domain aggregate functions) criteria is a string. If you need to use a value outside of that string (on a form for instance) you need to break the string add that form field, then close the string. If the dlookup field is a string, you need to add single quotation marks to the form field.

    Example with no outside references:

    ,"NumberColumn = 3")
    or
    "StringColumn = 'text'")

    Example with outside references:

    ,"NumberColumn = " & Me.[Numberfield])
    or
    "StringColumn = " & "'" & Me.[Textfield]) & "'")

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •