Solved Deleting blank space (1 Viewer)

[sbaud2003]

Is it possible to delete blank space in a Text Box.
For example, I am fetching data using Dlookup Function

LC = Nz(DLookup("OFFICE", "OFFICE_CODE", "OF_CODE = 1 AND CHK=TRUE"))
MC = Nz(DLookup("OFFICE", "OFFICE_CODE", "OF_CODE = 2 AND CHK=TRUE"))
NC = Nz(DLookup("OFFICE", "OFFICE_CODE", "OF_CODE = 3 AND CHK=TRUE"))
OC = Nz(DLookup("OFFICE", "OFFICE_CODE", "OF_CODE = 4 AND CHK=TRUE"))
PC = Nz(DLookup("OFFICE", "OFFICE_CODE", "OF_CODE = 5 AND CHK=TRUE"))

Add.value = (LC) & Chr(13) & Chr(10) & (MC) & Chr(13) & Chr(10) & (NC) & Chr(13) & Chr(10)(OC) & Chr(13) & Chr(10) (PC) & Chr(13) & Chr(10)

So, if MC and OC have null value, is it possible to delete the line space in Add.Value Text Box.
May be I am asking a very foolish type question.
or is there any other way,
Add.value is an address text box

 

[arnelgp]

not tested:

Add.Value = (LC) & (vbCrLf + (MC)) & (vbCrLf + (NC)) & (vbCrLf + (OC)) & (vbCrLf + (PC))

 

[sbaud2003]

not tested:

Add.Value = (LC) & (vbCrLf + (MC)) & (vbCrLf + (NC)) & (vbCrLf + (OC)) & (vbCrLf + (PC))

Thanks for your support, but not functioning

 

[arnelgp]

sorry:

Me.Add.Value = (LC) & _
            IIf(Len(MC & "") = 0, "", vbCrLf + MC) & _
            IIf(Len(NC & "") = 0, "", vbCrLf + NC) & _
            IIf(Len(OC & "") = 0, "", vbCrLf + OC) & _
            IIf(Len(PC & "") = 0, "", vbCrLf + PC)

 

[ebs17]

SELECT OFFICE FROM OFFICE_CODE WHERE OF_CODE BETWEEN 1 AND 5 AND CHK=TRUE

I would start two steps earlier and replace the DLookup fireworks with a single query.
Using a loop in a recordset, you can then put the values together while leaving out the NULL contents.

 

[sbaud2003]

sorry:

Me.Add.Value = (LC) & _
            IIf(Len(MC & "") = 0, "", vbCrLf + MC) & _
            IIf(Len(NC & "") = 0, "", vbCrLf + NC) & _
            IIf(Len(OC & "") = 0, "", vbCrLf + OC) & _
            IIf(Len(PC & "") = 0, "", vbCrLf + PC)

Awesome, Thanks a lot. Parfectly working as I want

 

[sbaud2003]

SELECT OFFICE FROM OFFICE_CODE WHERE OF_CODE BETWEEN 1 AND 5 AND CHK=TRUE

I would start two steps earlier and replace the DLookup fireworks with a single query.
Using a loop in a recordset, you can then put the values together while leaving out the NULL contents.

Thanks, i will try these too