Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Filter (Access 2000)

    I have the following field in a query.

    Z-Score: (IIf(IsNumeric([StringValue]),CDbl([StringValue]-[Average])/[StandardDeviation],Null))

    The calculation works and gives me the numbers I expect. Now I'm trying to filter the results that are >3 but it isn't working (no rows returned). I assume it's something to do with converting string values to number values.

    Thanks,

    Deni

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

    Re: Query Filter (Access 2000)

    Does this work better? (I placed the parentheses differently)

    Z-Score: IIf(IsNumeric([StringValue]),(CDbl([StringValue])-[Average])/[StandardDeviation],Null)

    If not, could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Filter (Access 2000)

    That didn't work. It would difficult to post a stripped down version as the majority of data comes from a linked SQL database. I understand that this would make to difficult to figure out the problem.

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

    Re: Query Filter (Access 2000)

    It works OK in a small demo I whipped up, so I'd check carefully that there are records for which the Z-score is greater than 3. If so, I fear it is impossible to tell why the query fails without seeing the database.

Posting Permissions

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