Problem with IIF statement in SQL

[MA04]

Hi,

I have been having a problem with an IIF statement in SQL, I have been able to get it to work for other parts of the sql statement, except for one. I want to put an IIF statement on the statement in bold below:

strSQL = "INSERT INTO ma_enq ( A4, A1, A2, A3 ) " & _
"SELECT I1.postcode, " & _
"Mid$(';' & I1.PRMF & ';',S1.num+1,S2.num-S1.num-1) & IIf(Trim(I1.Strd & '')='','',','+I1.Strd) & IIf(Trim(I1.STR & '')='','',', '+I1.STR), " & _
"IIF(IsNull([I1].[Locdd]),[I1].[Locd],[I1].[Locdd] & IIf(IsNull([Locd]),'',', ' & [Locd])) AS Expr1, IIF(IsNull([I1].[PTN]),[I1].[CNT],[I1].[PTN] & IIf(IsNull([CNT]),'',', ' & [CNT])) AS Expr2 " & _
"FROM ma_dbo_uk AS I1, integers AS S1, integers AS S2 " & _
"WHERE (((Mid$(';' & [I1].[PRMF] & ';',[S1].[num]+1,[S2].[num]-[S1].[num]-1))=[Forms]![Ma_search2]![PRMF_a1]) AND ((Mid$(';' & [I1].[PRMF] & ';',[S1].[num],1))=';') AND ((Mid$(';' & [I1].[PRMF] & ';',[S2].[num],1))=';') AND (([S1].[num]+1)<[S2].[num]) AND ((InStr(1,Mid$(';' & [I1].[PRMF] & ';',[S1].[num]+1,[S2].[num]-[S1].[num]-1),';'))=0));"


I want the line above to take into account null records, because at present if there is a record with a null value in the field in bold it will not append it.

Any suggestions very much appreciated. Thanks in advance,

M-.

 

[MA04]

Hi all,

I was wondering whether i may have to change the where statement above in addition to changing the statement in bold (above), if i wanted to put a iif statement on the SubString to take into account the null values?

I have placed my search query to make it clearer to what i was looking for. This is run on form view before my apppend query (above) is run. The user selects a record after the search query has been carried out and appends (with above statement) from a command button.

Search Query:

SELECT I1.postcode, Mid$(';' & I1.PRMF & ';',S1.num+1,S2.num-S1.num-1) AS SubString, IIF(IsNull([I1].[Locdd]),[I1].[Locd],[I1].[Locdd] & IIf(IsNull([Locd]),"",", " & [Locd])) AS Expr, IIF(IsNull([I1].[PTN]),[I1].[CNT],[I1].[PTN] & IIf(IsNull([CNT]),"",", " & [CNT])) AS Expr2
FROM ma_dbo_uk AS I1, integers AS S1, integers AS S2
WHERE (((I1.postcode) Like [Enter Postcode:]) AND ((Mid$(";" & [I1].[PRMF] & ";",[S1].[num],1))=';') AND ((Mid$(";" & [I1].[PRMF] & ";",[S2].[num],1))=';') AND (([S1].[num]+1)<[S2].[num]) AND ((InStr(1,Mid$(";" & [I1].[PRMF] & ";",[S1].[num]+1,[S2].[num]-[S1].[num]-1),";"))=0));

Hope someone can help, thanks in advance,

M-.

 

[MA04]

I have managed to put the IIF statement on the bold above but i am having a few problems.

1) if i change this part of my search query: Mid$(';' & I1.PRMF & ';',S1.num+1,S2.num-S1.num-1) AS SubString to match my append (part in bold below) it does not append, can anyone tell me why?

2) my search query does not show me records that have a null value in the first field i.e.substring, does anyone no why?

This is what my append now looks like:
strSQL = "INSERT INTO ma_enq ( A4, A1, A2, A3 ) " & _
"SELECT I1.postcode, " & _
"IIF(IsNull(Mid$(';' & I1.PRMF & ';',S1.num+1,S2.num-S1.num-1)), [I1].[Strd], (Mid$(';' & I1.PRMF & ';',S1.num+1,S2.num-S1.num-1)) & IIF(IsNull([Strd]),'',' ,' & [Strd]) & IIF(IsNull([STR]),'',' ,' & [STR])) AS SubString, " & _
"IIF(IsNull([I1].[Locdd]),[I1].[Locd],[I1].[Locdd] & IIf(IsNull([Locd]),'',', ' & [Locd])) AS Expr1, IIF(IsNull([I1].[PTN]),[I1].[CNT],[I1].[PTN] & IIf(IsNull([CNT]),'',', ' & [CNT])) AS Expr2 " & _
"FROM ma_dbo_uk AS I1, integers AS S1, integers AS S2 " & _
"WHERE (((Mid$(';' & [I1].[PRMF] & ';',[S1].[num]+1,[S2].[num]-[S1].[num]-1))=[Forms]![Ma_search2]![PRMF_a1]) AND ((Mid$(';' & [I1].[PRMF] & ';',[S1].[num],1))=';') AND ((Mid$(';' & [I1].[PRMF] & ';',[S2].[num],1))=';') AND (([S1].[num]+1)<[S2].[num]) AND ((InStr(1,Mid$(';' & [I1].[PRMF] & ';',[S1].[num]+1,[S2].[num]-[S1].[num]-1),';'))=0));"

Any help appreciated,

M-.