Query Field

Question

I built a test field in an Access query that isn't displaying the output desired. In the field line of the query, I entered the following:

Test:

IIf([Ai1] Is Not Null,[Ai1]

& Chr(13) & Chr(10) &

IIf([Ai2] Is Not Null,[Ai2]

& Chr(13) & Chr(10) &

IIf([Ai3] Is Not Null,[Ai3]

& Chr(13) & Chr(10) &

IIf([Ai4] Is Not Null,[Ai4]

& Chr(13) & Chr(10) &

IIf([Ai5] Is Not Null,[Ai5]

& Chr(13) & Chr(10) &

IIf([Ai6] Is Not Null,[Ai6]))))))

As long as Ai1 thru Ai6 are populated, the Test field displays the desired results. But of Ai1 and Ai6 are populated and nothing in between, then the Test field will display the Ai1 output, but not Ai6.

Let's assume that the below is true:

Ai1 = Output 1

Ai2 = Output 2

Ai3 = Output 3

Ai4 = Output 4

Ai5 = Output 5

Ai6 = Output 6

The results of the query would be:

Output 1

Output 2

Output 3

Output 4

Output 5

Output 6

Now let's assume this is true:

Ai1 = Output 1

Ai2 = Null

Ai3 = Null

Ai4 = Null

Ai5 = Null

Ai6 = Output 6

The results of the query is:

Output 1

How do I change the field expression so that the results display ALL the Ai fields that apply? In the last example, the results should be:

Output 1

Output 6

Novice level at best, so my understanding is limited.

Thanks in advance!

Answer 1

Like this:

Test: Mid((Chr(13)+Chr(10)+[Ai1]) & (Chr(13)+Chr(10)+[Ai2]) & (Chr(13)+Chr(10)+[Ai3]) & (Chr(13)+Chr(10)+[Ai4]) & (Chr(13)+Chr(10)+[Ai5]) & (Chr(13)+Chr(10)+[Ai6]), 3)

Answer 2

Your field names and specifications suggest your table isn’t normalized.

Answer 3

Hi shubox65,

For these purposes I use a small function, placed in a general module:

Function Concat(separator As String, ParamArray values()) As String 



  Dim result As String 



  Dim x As Integer 



   



  For x = 0 To UBound(values()) 



    If (values(x) > "") Then 



      result = result & separator & values(x) 



    End If 



  Next 



   



  Concat = Replace(result, separator, "", 1, 1) 



End Function 

You can call it anywhere in your database as:

Test = Concat(Chr(13) + Chr(10), Ai1, Ai2, Ai3, Ai4, Ai5, Ai6)

Imb.

Thank you for the globalized tip. :-)

Answer 4

Hi shubox65,

For these purposes I use a small function, placed in a general module:

Function Concat(separator As String, ParamArray values()) As String 

  Dim result As String 

  Dim x As Integer 

  For x = 0 To UBound(values()) 

    If (values(x) > "") Then 

      result = result & separator & values(x) 

    End If 

  Next 

  Concat = Replace(result, separator, "", 1, 1) 

End Function 

You can call it anywhere in your database as:

Test = Concat(Chr(13) + Chr(10), Ai1, Ai2, Ai3, Ai4, Ai5, Ai6)

Imb.

Answer 5

THANK YOU! Seems to work perfectly.