Combine fielfds

[Gismo]

Hi all,

I need to combine 4 fields

I used below code

If field 3 and field 4 is blank, i dont want to see the seperator and the same with firled 1 and 2

Please could you advise

Check: [Detailed Inspections TBL]![Limit 1] & " " & [Detailed Inspections TBL]![Unit 1] & " / " & [Detailed Inspections TBL]![Limit 2] & " " & [Detailed Inspections TBL]![Unit 2]

 

[SHANEMAC51]

If field 3 and field 4 is blank, i dont want to see the seperator and the same with firled 1 and 2

Check: trim([Detailed Inspections TBL]![Limit 1]
& (" " + [Detailed Inspections TBL]![Unit 1])
& (" / " + [Detailed Inspections TBL]![Limit 2])
& (" " + [Detailed Inspections TBL]![Unit 2]))

 

[arnelgp]

you can also create a function and call it in your Query:

Public Function fncConcat(ParamArray p() As Variant) As String
    Dim i As Integer, s As String
    For i = 0 To UBound(p)
        s = s & p(i)
        If i = 1 Then
            s = s & " / "
        Else
            s = s & " "
        End If
    Next
    s = Trim$(s)
    If Left$(s, 1) = "/" Then s = Trim$(Right$(s, Len(s) - 1))
    If Right$(s, 1) = "/" Then s = Trim$(Left$(s, Len(s) - 1))
    Do While InStr(1, s, "  ") <> 0
        s = Replace$(s, "  ", " ")
    Loop
    fncConcat = s
End Function

Check: fncConcat([Detailed Inspections TBL]![Limit 1], [Detailed Inspections TBL]![Unit 1], [Detailed Inspections TBL]![Limit 2], [Detailed Inspections TBL]![Unit 2])

 

[Pat Hartman]

People don't always remember to use their words so I'll explain.

The & is the standard concatenation operator in VBA. The + is an arithmetic operator that can be used to concatenate text items but has concatenation capabilities different from the &. The & treats null as a ZLS but the + respects them and returns null So

A & Null = A
A + Null = Null

So, notice how the parts of the expression are enclosed in () and where the + is used instead of the &.

(" " & Something) returns --- blank somevalue
But
(" " + Something) returns --- blank somevalue when somevalue is not null but returns null if somevalue is null

 

[Gismo]

Check: trim([Detailed Inspections TBL]![Limit 1]
& (" " + [Detailed Inspections TBL]![Unit 1])
& (" / " + [Detailed Inspections TBL]![Limit 2])
& (" " + [Detailed Inspections TBL]![Unit 2]))

Thank you for the reply

I amended my code but still get the blank from field 1 and 2

Check: Trim([Detailed Inspections TBL]![Limit 1])
& (" "+[Detailed Inspections TBL]![Unit 1])
& (" / "+[Detailed Inspections TBL]![Limit 2])
& (" "+([Detailed Inspections TBL]![Unit 2]))

 

[SHANEMAC51]

Check: trim(
    (" "+[Detailed Inspections TBL]![Limit 1])
& (" " + [Detailed Inspections TBL]![Unit 1])
& iif(isnull([Detailed Inspections TBL]![Limit 2]) and isnull([Detailed Inspections TBL]![Unit 2]),""," /")
& (" " + [Detailed Inspections TBL]![Limit 2])
& (" " + [Detailed Inspections TBL]![Unit 2])
    )

 

[arnelgp]

i used my function on Query1.
try adding more data.

 

[Gismo]

i used my function on Query1.
try adding more data.

Thank you
Works like a charm

 

[Pat Hartman]

Again, nobody used their words.

f you are still getting blanks, then the field is NOT null, it is a ZLS (Zero Length String). The + technique ONLY solves the problem for null values.

To solve the ZLS problem goes deeper. You need to change the AllowZLS setting on all your text fields to No. Then, you need to run an update query to replace the ZLS with Null. You can't tell by looking at it whether the field is null or ZLS. Going forward, you have to be conscious as you define each new table to make a decision regarding the setting of the AllowZLS property. The only time I ever leave the default set to yes is if I have to import data from mainframe sources directly into a table. But even that can be avoided as an issue by linking to the table and running an append query that gets rid of the ZLS on import.

OR, you can use the function provided by arnelgp and leave the bad data to haunt you in other situations. You can't tell by looking at it whether the field is null or ZLS. So, the best solution is to avoid ZLS entirely and have all optional fields default to null. That way, you have only ONE "empty" value to account for in your code and queries and that value is Null. If you have text fields that are required but for which the AllowZLS property has been left at Yes, you have created a problem that shouldn't exist because you are allowing "blank" values in that field. People are afraid of nulls but only because they don't understand how to work with them and how valuable they actually are. If you are looking a a grade table, are the 0's actually 0 or are they defaults because the developer was afraid of defaulting to null because he didn't understand how null's affect arithmetic. You never actually know if a 0 is a zero or just some field that someone forgot to put a value into.

 

[Gismo]

i used my function on Query1.
try adding more data.

Hi,

How would you use the same code if you may or may not have preceding values?

Days Remaing2: Avg([Transactions]![ForecastDate]-Date())(& fncConcat( " Days"))

 

[Gismo]

Thank you
Works like a charm

Hi Arnelgp

How would I convert the code to only look at 2 controls with an " - " should the second control have any data?

I tried

RegSerial2: ([DAW Sheet Data File - Local]![Registration] & fncConcat([DAW Sheet Data File - Local]![Airframe Serial No]))

 

[arnelgp]

change the function to:

Public Function fncConcat(ByVal delim As String, ParamArray p() As Variant) As String
    Const single_space As String * 1 = " "
    Const double_space As String * 2 = "  "
    Dim i As Integer, s As String
    If Len(delim & "") = 0 Then delim = "/"
    delim = Trim$(delim)
    For i = 0 To UBound(p)
        s = s & p(i)
        If i = 1 Or delim <> "/" Then
            s = s & single_space & delim & single_space
        Else
            s = s & single_space
        End If
    Next
    Do While Right$(s, 2) = delim & single_space
        s = Left$(s, Len(s) - 2)
    Loop
    Do While InStr(1, s, delim & delim) <> 0
        s = Replace$(s, delim & delim, delim)
    Loop
    Do While InStr(1, s, double_space) <> 0
        s = Replace$(s, double_space, single_space)
    Loop
    s = Trim$(s)
    If Right$(s, 1) = delim Then s = Trim$(Left$(s, Len(s) - 1))
    If Left$(s, 1) = delim Then s = Trim$(Mid$(s, 2))
    fncConcat = s
End Function

now you change your Previous Queries to:

Check: fncConcat("/", [Detailed Inspections TBL]![Limit 1], [Detailed Inspections TBL]![Unit 1], [Detailed Inspections TBL]![Limit 2], [Detailed Inspections TBL]![Unit 2])

for your new Calculated column with " - ":

RegSerial2: ("-", [DAW Sheet Data File - Local]![Registration], [DAW Sheet Data File - Local]![Airframe Serial No])

 

[Pat Hartman]

Field1 + " - " + Field2