Problem with IIf statement 1

[MA04]

Hi all,

I have an IIf statement which combines 2 fields together to create field Expr2. The statement seperates the fields by a comma "," it also gets rid of the comma if the first field length is less than 4, it also checks if values are null. What i want to do is add an extra field to this, field A3, but i can't seem to get syntax correct.

This is what the statement looks like, this works but only takes 2 fields A1 and A2:

SELECT IIf(IIf(len(a1)>0,Len(a1),0)<4,I1.A1 & " " & I1.A2,I1.A1 & IIf(IsNull([A2]),"",", " & [A2])) AS Expr2
FROM i1;

I want to add field A3 so that it is part of field Expr2. These are all the possible results that i want the code to achieve:

A1, A2 , A3
A1, A2 (A3 is null)
A1, A3 (A2 is null)
A1 A2, A3 (A1 is less than 4 characters)
A1 A2 (A1 is less than 4 characters + A3 is null)
A1 A3 (A1 is less than 4 characters + A2 is null)

Any help very much appreciated, thanks in advance,
M-.

 

[PHV]

You may consider creating your own function instead of using to much complex IIf.
In a standard code module:
Public Function getA1A2A3(A1 As String, A2, A3) As String
If IsNull(A2) And IsNull(A3) Then getA1A2A3 = A1: Exit Function
Dim s As String
s = A1 & IIf(Len(A1) < 4, " ", ", ")
If IsNull(A3) Then
s = s & A2
Else
s = s & IIf(IsNull(A2), "", A2 & ", ") & A3
End If
getA1A2A3 = s
End Function

And now your query:
SELECT getA1A2A3([A1],[A2],[A3]) AS Expr2 FROM i1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MA04]

Hi PHV,

Thanks for the reply, but I am a little confused as to where I should place the code as its in VBA. Would i place it as a module or in a form and how do i link it with the query. Thanks in advance,

M-.

 

[MA04]

Sorry,

I put the code as a module and the query to worked, did not realise you could do that with access, Thanks PSV,

M-.