Results 1 to 4 of 4
  1. #1
    cliff.clayman is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2015
    Posts
    55

    Angry Replacing IIf with Nz function


    I would like to know if the Nz function would work as a replacement for the IIf function in this expression:

    IIf([Vol EUR Cross Org]ISNULL,0,Replace([Vol EUR Cross Org],",","."))*[Rate]))AS[PVO]

    I can't seem to get the syntax correct when I try to use Nz().

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    well you are doing 2 things, NZ and replacing commas.
    how bout in the query , you do
    NZ([Vol EUR Cross Org]) AS VolEurCrossOrg

    then run the replace on the above field.

  3. #3
    cliff.clayman is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2015
    Posts
    55
    What do you mean by run the replace on the above field? I'm trying to do it all in one expression.

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    First, nz wouldn't work terribly well in this formula (the way you currently have it), nz is intended (in my opinion) to show a zero if the formula has no value, rather than checking a value as part of a formula if that makes sense. In other words you would use it to fill in your dataset with zeros anywhere the result of another formula couldn't be evaluated.

    Second, it looks to me like you're trying to perform a mathematical operation on a text field in which case you'd have to handle that possibility as well, and what happens if the number is larger than 1,000,000? you'd get a conversion of 1.000.000 which would bomb out your formula. It may not be a possibility but you might want to clean up the formula to be something more like

    nz((cdbl([Vol EUR Cross Org])/1000) * [Rate], 0) as [PVO]

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

Similar Threads

  1. Replacing #Num! error with a zero
    By gmccray in forum Queries
    Replies: 4
    Last Post: 09-09-2015, 09:43 AM
  2. Replacing one value for another
    By Richiebob in forum Programming
    Replies: 10
    Last Post: 05-06-2013, 11:33 AM
  3. Replacing space with dot
    By JeroenMioch in forum Programming
    Replies: 15
    Last Post: 10-22-2012, 07:09 AM
  4. Replacing data in Table
    By JackT in forum Import/Export Data
    Replies: 1
    Last Post: 08-23-2010, 10:34 AM
  5. Replacing Null with 0
    By gilagain1 in forum Queries
    Replies: 5
    Last Post: 04-23-2009, 01:47 PM

Tags for this Thread

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