Results 1 to 8 of 8
  1. #1
    frustratedwithaccess is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Oct 2014
    Posts
    35

    expression builder

    Hello All,

    Quick question re building an expression to calculate the value of a calculated column:



    I have 4 columns: "Precipitation1", "Precipitation2", "Totalrainfall1", and "Totalrainfall2".

    Totalrainfall2 is the calculated column of Precipitation1 + Precipitation2. If there is no value (i.e. blank cell) from adding Precipitation1 and Precipitation2, I want to use the value from Totalrainfall1 in Totalrainfall2. Any help with building this expression is much appreciated.

    Cheers!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    This sounds like 2 different queries
    Q1 : an update qry to upd Totalrainfall2 =Totalrainfall1, where of isnull(Precipitation1) or isnull(Precipitation2)
    then
    Q2 : an update qry to upd Totalrainfall2 =(Precipitation1 + Precipitation2), where of not isnull(Precipitation1) and not isnull(Precipitation2)

  3. #3
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    How about something like:
    Totalrainfall2 = IIF( (Nz(Precipitation1,0) + Nz(Precipitation2,0)) = 0 , Totalrainfall1 , Nz(Precipitation1,0) + Nz(Precipitation2,0))

  4. #4
    frustratedwithaccess is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Oct 2014
    Posts
    35
    unfortunately both of these don't seem to be working...

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    How are you using the solutions? Calculated values should be derived in a query and not occupy a field in a table.

  6. #6
    mitch_pearce79 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2012
    Location
    Perth
    Posts
    15
    try
    Expression for Totalrainfall2
    =IIF(Precipitation1 is null, IIF(Precipitation2 is null,Totalrainfall1,Precipitation2),IIF(Precipitat ion2 is null,Precipitation1,Precipitation1+Precipitation2) )

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I think you want to use isnull([field]) not field is null in your expression mitch
    As someone pointed out totalrainfall2 is a calculated value and should not be stored. But in a query you could have the formula


    TotalRainfall2: iif(isnull([precipitation1]) and isnull([precipitation2]), totalrainfall1, nz([precipitation1],0) + nz([precipitation2],0))

  8. #8
    mitch_pearce79 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2012
    Location
    Perth
    Posts
    15
    Can't argue with that!

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

Similar Threads

  1. Expression Builder Help
    By Vibhor in forum Access
    Replies: 4
    Last Post: 12-11-2013, 11:42 AM
  2. Replies: 2
    Last Post: 11-20-2012, 03:21 AM
  3. Expression builder help
    By jigg14 in forum Forms
    Replies: 1
    Last Post: 03-19-2012, 09:47 AM
  4. Expression builder
    By PJ_d_DJ in forum Access
    Replies: 2
    Last Post: 02-24-2011, 03:38 AM
  5. Expression Builder
    By mistaken_myst in forum Access
    Replies: 2
    Last Post: 05-07-2008, 01:30 PM

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