Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query formulas (2000)

    Hi,
    I have the following fields in a query:
    File Origin
    Ref
    File Type
    File Quality
    No days late
    Date Received

    I have produced a query that filter by DateReceived dependant upon whether it relates to a previous month.

    I am trying to create a report that will syphon out the No days late. I've tried:

    =dcount("*","Ref","No days late = 0 And File Origin = Norwich")

    in a text box in the report.

    What I need to do is sort the files by 3 origins and 3 values of No days late. So this?

    =dcount("*","Ref","No days late = 0 And File Origin = Norwich")
    =dcount("*","Ref","No days late >=0 And <=5 And File Origin = Norwich")
    =dcount("*","Ref","No days late >5 And File Origin = Norwich")

    And then change the origin or each to reflect the files that have these values. Basically I need a sum of each value dependent upon whether No days late is 0, greater than 0 but less than 5, or greater than 5. Hence why I'm counting the "Ref".

    Can anyone help?? I keep getting the error# value.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query formulas (2000)

    If you use field names with spaces in them (not recommended), you must enclose them in square brackets: [File Origin], [No days late] etc.

    If you compare a field to a text value, you must enclose the text value in quotes. Since you already have an expression in quotes, you must use single quotes or double double quotes.

    You cannot use ">=0 And <=5". And I think you mean >=1 instead of >=0. Either use
    [No days late]>=1 And [No days late]<=5
    or
    [No days late] Between 1 And 5

    Example:
    <pre>=DCount("*","Ref","([No days late] Between 1 And 5) And ([File Origin] = 'Norwich'"))
    </pre>


    Not sure about "Ref", though. The second argument of DCount should be the name of a table or query, not of a field.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query formulas (2000)

    Hi,
    Thanks, I've now tried:

    =DCount("*","QryAll","([No days late] Between 1 And 5) And ([File Origin]='Norwich'")

    where QryAll is the query that the report is based on.

    But still error#, any clues?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query formulas (2000)

    You're missing a closing bracket here.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query formulas (2000)

    I tried it without the extra closing bracket on the end as it was displaying the error 'the expression you entered has too many closing parenthesis' which I presume relates to the fact that there are too many brackets. But I still get the error#

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query formulas (2000)

    Oops, my bad. Try this:
    <pre>=DCount("*","QryAll","([No days late] Between 1 And 5) And ([File Origin]='Norwich')")
    </pre>

    Note that the closing parentheses are placed differently. Sorry about the confusion.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query formulas (2000)

    Hi Hans,
    Thanks for that, I've now formulated the majority of the statistics I need.
    Is it possible to add a further field to this, something like:
    =DCount("*","QryAll","([No days late] Between 1 And 5) And ([File Origin]='Norwich') And ([File Quality]='Fully Satisfactory')")
    It doesn't seem to be working how I would expect.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query formulas (2000)

    >> It doesn't seem to be working how I would expect.

    Can you elaborate on that? How is the result different from what you expected?

  9. #9
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query formulas (2000)

    It displays the #Error message, so the result isn't there.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query formulas (2000)

    What kind of field (text, number, ...) is File Quality?

  11. #11
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query formulas (2000)

    The field is Text for File Quality

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query formulas (2000)

    Since it works without the bit with File Quality (I hope), the error must be there. We have ruled out a data type mismatch, so an error in the name is the only other possible cause I can think of. Are you absolutely sure that there is a field named File Quality in QryAll?

  13. #13
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query formulas (2000)

    This is the formula in its current form:
    =DCount("*","QryAll","[No days late]=0) And ([File Origin]='Norwich') And ([File Quality]='Fully Satisfactory')")
    I have double checked and there is a text field in QryAll named File Quality, the only thing that I can see may be a problem is that QryAll has the * taken from TblJPM and that this field is in TblJPM. Therefore in the QryAll the reference to this is TblJPM.File Quality and not simply File Quality. I presume that this is where the problem lies.....what would be the work around for this? I've tried
    =DCount("*","QryAll","[No days late]=0) And ([File Origin]='Norwich') And ([TblJPM.File Quality]='Fully Satisfactory')")

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query formulas (2000)

    If you already have tblJPM.* in the query, you should not include it separately; if you need it for criteria or sorting, clear the Show check box.

    If the field File Quality can be from two different tables, you do need the table specification. Try

    =DCount("*","QryAll","[No days late]=0) And ([File Origin]='Norwich') And ([TblJPM].[File Quality]='Fully Satisfactory')")

    Note the extra brackets before and after the period.

  15. #15
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query formulas (2000)

    Still having the same problem. I'm sure its an oversight on my behalf........................
    I've attached the database for your perusal if you have time.
    The problem is with the last entry on the report.
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

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