Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula Problem (Access 2000)

    I have converted a database from A97 to A2k without any error messages.

    BUT.. a formula in my query keeps telling me that i'm missing one of the following
    [,)

    This message does not appear in ny A97 version.

    Amazingly it still comes back with the correct data. I've put the formula below to see if anyone can tell me where I'm going blind. I've looked at this soooooooooooooooooo many times.

    ptd: Sum((((IIf(IsNull([claim_os_movements]![clo_paid_to_date_fees]),0,[claim_os_movements]![clo_paid_to_date_fees])+IIf(IsNull([claim_os_movements]![clo_paid_to_date_indem]),0,[claim_os_movements]![clo_paid_to_date_indem]))*[claim_os_movements]![clo_os_line_pcnt]/100)/[claim_os_movements]![clo_sett_exchange_rate]))

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula Problem (Access 2000)

    you could simplify your formula by using Nz like this:

    Sum(((Nz([claim_os_movements]![clo_paid_to_date_fees],0)+Nz([claim_os_movements]![clo_paid_to_date_indem],0))*[claim_os_movements]![clo_os_line_pcnt]/100)/[claim_os_movements]![clo_sett_exchange_rate])

    I *think* I got the parens right, but you'll have to double-check. The Nz function is certainly easier to read than all those "IIF(IsNull(whatever" expressions.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Problem (Access 2000)

    That is a handy little thing. I have a lot of "IIF(IsNull(whatever" expressions.

    Unfortunately, I still get the same message about a missing comma or whatever, and they all look fine to me.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Problem (Access 2000)

    I have attached an mdb with your query and Charlotte's query.
    Seems to work. Maybe you can copy and past it in your mdb and see what happens.
    Attached Files Attached Files
    Francois

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula Problem (Access 2000)

    It's also possible that if you typed in the expression, you missed one of the square brackets around a field or table object. That message is annoying because it is so vague. Try counting the left and right brackets and make sure you have the same number of left and right. Try the same thing with parens. That will at least tell you if you left one out. Unfortunately, it won't tell you if you misplaced one, and that could also be a problem.
    Charlotte

  6. #6
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Problem (Access 2000)

    OK - Found the problem.

    There were to calculated fields that started the same way and I only saw the first one. The second formula was 3 times as long as the first, when all it needed to be was one field plus another (this is an inherited database)

    ANYWAY, everything is fine and I got to know about the "Nz" function. - Carlotte, you're a smoothie and thanks to you too Francois.

    Are there any other groovy little functions that cut out a lot of unnecessar cr*p?

  7. #7
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Problem (Access 2000)

    My approach would be to encapsulate this kind of expression in a VBA function because i can use the de###### and the editing tools. Using the "immediate window" allows me to plug in values and see what the result is.

    There is a complete list of functions in the VBA help file - i had to use 'Chr' for something yesterday.

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula Problem (Access 2000)

    It's always a good idea to read through the "What's New" portion of the documentation. That will catch you up on any new features added from the previous version. I like Split() and Join() and Replace() in 2000, all so much easier than the do-it-yourself functions that had to be written in 97.
    Charlotte

Posting Permissions

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