problem with syntax: IIF function

[fboehlandt]

hi everyone.
I would like to know the correct syntax for the following Iif function:

Select IIF([Performance.Return]='0','') As Performance

I would like get the return datafield from table 'Performance' and integrate it as a column in my query. However, whenever the datafield is null, I would like to have an empty cell returned to me. What's the mistake in my syntax above?
regards

 

[Brianwarnock]

Try

Nz([Performance.Return],"")

Brian

 

[fboehlandt]

Thanks for your response,
two problems though:
First, the query is used in a VBA script to export data to an excel pivottable. Unfortunately, Nz() is not recognized as a valid function in this context. The workaround is an IIf function. Second, Nz() would check for empty cells (null values), whereas I would like to check for cells = '0' (quite literally). Any further ideas?

 

[MSAccessRookie]

Thanks for your response,
two problems though:
First, the query is used in a VBA script to export data to an excel pivottable. Unfortunately, Nz() is not recognized as a valid function in this context. The workaround is an IIf function. Second, Nz() would check for empty cells (null values), whereas I would like to check for cells = '0' (quite literally). Any further ideas?

The IIf statement is formatted incorrectly. The proper syntax is:

IIf("Condition", "[Value if TRUE]", "[Value if FALSE]")
​

If [Performance.Return] is a string, then you can try the following:

IIf([Performance.Return]='0', '', [Performance.Return]) As Performance
​

 

[Brianwarnock]

you said

However, whenever the datafield is null, I would like to have an empty cell returned to me.

and this is the queries forum not VBA.

Rookie is correct about the IIF syntax but has copied your error , a zero length field is "" not " easily done.

Brian

 

[fboehlandt]

This is my VBA code for the commandarray. The code works if I don't implement the IIF function for Performance.FundsManaged. (see red font) but rather import the data as is. There must be something wrong with this function. It works in Access sql. I'm not very sure-footed when it comes to the differences here, so any help is appreciated. Here is the code:

Function dbquery(filepath)
    Dim Selectquery, Innerquery As String
    
    Selectquery = "SELECT Performance.ID As Code,  Information.F1 As Fund, Performance.Date As MM_DD_YYYY, IIF([SystemInformation6.F4]='','No','Yes') As Leverage, "
    Innerquery = "FROM `" & filepath & "`.`Performance` INNER JOIN (`" & filepath & "`.`Information` INNER JOIN `" & filepath & "`.`SystemInformation6` ON Information.ID = SystemInformation6.ID)" & Chr(10) & Chr(13) & "ON Performance.ID = Information.ID"
    
    dbquery = Array( _
    Selectquery & "Performance.Return As Performance, 'ROI' As [PType], 'Event Driven' As Main_Strategy", Innerquery, "WHERE Information.C1 = -1", "Union", _
    Selectquery & "[COLOR=red]IIF([Performance.FundsManaged]='0','', [Performance.FundsManaged]) As Performance[/COLOR], 'AUM' As [PType], 'Event Driven' As Main_Strategy", Innerquery, "WHERE Information.C1 = -1", "Union", _
    Selectquery & "Performance.Return As Performance, 'ROI' As [PType], 'Long/Short Equity' As Main_Strategy", Innerquery, "WHERE Information.C2 = -1", "Union", _
    Selectquery & "IIF([Performance.FundsManaged]='0','', [Performance.FundsManaged])As Performance, 'AUM' As [PType], 'Long/Short Equity' As Main_Strategy", Innerquery, "WHERE Information.C2 = -1", "Union", _
    Selectquery & "Performance.Return As Performance, 'ROI' As [PType], 'Equity Market Neutral' As Main_Strategy", Innerquery, "WHERE Information.C3 = -1", "Union", _
    Selectquery & "IIF([Performance.FundsManaged]='0','', [Performance.FundsManaged])As Performance, 'AUM' As [PType], 'Equity Market Neutral' As Main_Strategy", Innerquery, "WHERE Information.C3 = -1", "Union", _
    Selectquery & "Performance.Return As Performance, 'ROI' As [PType], 'Convertible Arbitrage' As Main_Strategy", Innerquery, "WHERE Information.C4 = -1", "Union", _
    Selectquery & "IIF([Performance.FundsManaged]='0','', [Performance.FundsManaged])As Performance, 'AUM' As [PType], 'Convertible Arbitrage' As Main_Strategy", Innerquery, "WHERE Information.C4 = -1", "Union", _
    Selectquery & "Performance.Return As Performance, 'ROI' As [PType], 'Fixed Income Arbitrage' As Main_Strategy", Innerquery, "WHERE Information.C5 = -1", "Union", _
    Selectquery & "IIF([Performance.FundsManaged]='0','', [Performance.FundsManaged])As Performance, 'AUM' As [PType], 'Fixed Income Arbitrage' As Main_Strategy", Innerquery, "WHERE Information.C5 = -1", "Union", _
    Selectquery & "Performance.Return As Performance, 'ROI' As [PType], 'Dedicated Short Bias' As Main_Strategy", Innerquery, "WHERE Information.C6 = -1", "Union", _
    Selectquery & "IIF([Performance.FundsManaged]='0','', [Performance.FundsManaged])As Performance, 'AUM' As [PType], 'Dedicated Short Bias' As Main_Strategy", Innerquery, "WHERE Information.C6 = -1", "Union", _
    Selectquery & "Performance.Return As Performance, 'ROI' As [PType], 'Emerging Markets' As Main_Strategy", Innerquery, "WHERE Information.C7 = -1", "Union", _
    Selectquery & "IIF([Performance.FundsManaged]='0','', [Performance.FundsManaged])As Performance, 'AUM' As [PType], 'Emerging Markets' As Main_Strategy", Innerquery, "WHERE Information.C7 = -1", "Union", _
    Selectquery & "Performance.Return As Performance, 'ROI' As [PType], 'Managed Futures' As Main_Strategy", Innerquery, "WHERE Information.C13 = -1", "Union", _
    Selectquery & "IIF([Performance.FundsManaged]='0','', [Performance.FundsManaged])As Performance, 'AUM' As [PType], 'Managed Futures' As Main_Strategy", Innerquery, "WHERE Information.C13 = -1", "Union", _
    Selectquery & "Performance.Return As Performance, 'ROI' As [PType], 'Global Macro' As Main_Strategy", Innerquery, "WHERE Information.C14 = -1", "Union", _
    Selectquery & "IIF([Performance.FundsManaged]='0','', [Performance.FundsManaged])As Performance, 'AUM' As [PType], 'Global Macro' As Main_Strategy", Innerquery, "WHERE Information.C14 = -1", "Union", _
    Selectquery & "Performance.Return As Performance, 'ROI' As [PType], 'Fund of Funds' As Main_Strategy", Innerquery, "WHERE Information.C15 = -1", "Union", _
    Selectquery & "IIF([Performance.FundsManaged]='0','', [Performance.FundsManaged])As Performance, 'AUM' As [PType], 'Fund of Funds' As Main_Strategy", Innerquery, "WHERE Information.C15 = -1")
End Function

 

[MSAccessRookie]

Is [Performance.FundsManaged] defined as a Number or a String. Your query requires a String.

 

[fboehlandt]

It is defined as integer. Is there any way to test for integer = 0 in field?

 

[Brianwarnock]

Numbers do not need ' just =0, but then a blank makes no sense.

Brian

 

[MSAccessRookie]

It is defined as integer. Is there any way to test for integer = 0 in field?

Sorry about that, I should have put this in my original reply. If it is an integer it would look like this:

IIf([Performance.FundsManaged]=0,'', [Performance.FundsManaged]) As Performance, 'AUM' As [PType], 'Event Driven' As Main_Strategy", Innerquery, "WHERE Information.C1 = -1", "Union", _

 

[MSAccessRookie]

Numbers do not need ' just =0, but then a blank makes no sense.

Brian

The Blank only makes sense to me if the user does not want to display a 0 for some reason.

 

[fboehlandt]

I think I need to clearify. The '0' is a faulty entry. It actually means that there is no value for that field. I know that this should be changed in the original database but this is the format in which the data is received (I've got to work with it as is). I'm going to experiment a bit with the IIF formula and see if I can get it to work (skipping the ' ' for 0 doesn't interupt the code but it imports zero values only...). Thanks everybody for your input