Concatenate, IsNull, Carriage Return 1

[UncleBonehead]

1) How can I prevent the following from creating the extra line returns if the [PartComments] field is null?
2) How can I prevent the following from creating the extra line returns at the end if there is only one record?

Component Info: Concatenate("SELECT [Issue#] + ') ' & [PartName] & '; P/N: ' + [PartNbr] & '; TOW: ' + [PartTOW] & IIf(IsNull([PartUnsRemTY]),'', Chr(13) & Chr(10) & 'UNS RMVLS:' & Chr(13) & Chr(10) & " & Year([ExDate]) & " & ': ' & [PartUnsRemTY] & Chr(13) & Chr(10) & " & Format(DateAdd("yyyy",-1,[ExDate]),"yyyy") & " & ': ' & [PartUnsRemTY-1] & Chr(13) & Chr(10) & " & Format(DateAdd("yyyy",-2,[ExDate]),"yyyy") & " & ': ' & [PartUnsRemTY-2])& Chr(13) & Chr(10) & Chr(13) & Chr(10) & UCase([PartComments]) FROM tblCompAnal WHERE ExHistID = '" & [ExHistID] & "'",Chr(13) & Chr(10) & Chr(13) & Chr(10))

Thank you!!

 

[dhookom]

You can experiment with replacing some & with +. For instance:

[FirstName] + Chr(10) + Chr(13) + [LastName] will return Null if either FirstName or LastName is null.

Duane
Hook'D on Access
MS Access MVP

 

[UncleBonehead]

Duane,

Issue #1 was resolved by using your suggestion, thank you...

Component Info: Concatenate("SELECT [Issue#] + ') ' & [PartName] & '; P/N: ' + [PartNbr] & '; TOW: ' + [PartTOW] & IIf(IsNull([PartUnsRemTY]),'', Chr(13) & Chr(10) & 'UNS RMVLS:' & Chr(13) & Chr(10) & " & Year([ExDate]) & " & ': ' & [PartUnsRemTY] & Chr(13) & Chr(10) & " & Format(DateAdd("yyyy",-1,[ExDate]),"yyyy") & " & ': ' & [PartUnsRemTY-1] & Chr(13) & Chr(10) & " & Format(DateAdd("yyyy",-2,[ExDate]),"yyyy") & " & ': ' & [PartUnsRemTY-2])& Chr(13) & Chr(10) + Chr(13) + Chr(10) + UCase([PartComments]) FROM tblCompAnal WHERE ExHistID = '" & [ExHistID] & "'",Chr(13) & Chr(10) & Chr(13) & Chr(10))

Do you have a suggestion for issue #2?

 

[dhookom]

You may need to modify the concatenate function. I don't have the time right now but it could be a matter of trimming off some ending characters.

Duane
Hook'D on Access
MS Access MVP