String issue, how to remove null values (1 Viewer)

[dealwi8me]

Hello,

I have the following Fields that i use in a query:
CompanyName ="Test Company"
Reason1 = "Test1"
Reason2 = null
Reason3="Test3"

I need to build a string for each Company which describes all the reasons. If i build the string as following [Reason1] & Chr(13) & Chr(10) & [Reason2] & Chr(13) & Chr(10) & [Reason3] i get
"Test1
<empty line>
Test3"

Is it possible to get the string without the empty line?

Thank you in advance.

 

[Rabbie]

Yes it is possible. just test each field for Null and include the Chr(13) & Chr(10) as necessary

iif(isnull([Reason1]),"",[Reason1] & Chr(13) & Chr(10)) & iif(isnull([Reason2]),"",[Reason2] & Chr(13) & Chr(10)) & iif(isnull([Reason3]),"",[Reason3])

 

[dealwi8me]

Thank you for the quick answer!

The problem is that i have to join a 50 fields of reasons [Reason1 - Reason50] (i used only 3 for the examlpe) and if i use the if function will get complicated.

In an other thread i read that i could replace & with + ([Reason1] + Chr(13) & Chr(10) & [Reason2] + Chr(13) & Chr(10) & [Reason3]) but i get a small rectangle if the first or last field are null.

Any suggestions?

 

[gemma-the-husky]

its hard directly in a query

easier in a function

something like

dim s as string
dim x as long

s="Reasons"
for x = 1 to 50
  if nz(reason(x),vbnullstring)<>vbnullstring then
    s=s&reason(x)&vbcrlf
  end if
next

 

[dealwi8me]

Thanks!
I managed to create the string but is more than 255 characters and some characters are trancuted.

 

[neileg]

Is your structure normalised? I have a nasty feeling you've got 50 fields called Reason1 to Reason50. These should be up to 50 records in a related table. This way you can add a criterion in a query to exclude null values and they'll be returned in the order you want anyway. Tables could look like this:

tblCompany
CompanyID PK
CompanyName

tblReason
ReasonID PK
ReasonDescription
CompanyID FK
ReasonValue

 

[dealwi8me]

No it's more complicate than that, i'll try to explain.

I have applications which must agree with a checklist of 50 diffrent points.
If one or more points are not fulfilled then the particular point is marked as pending.

So, i created a query that gets only the description of the missing points, each one seperately.

At second query i want to join the description of all pending point in one paragraph, so i merged the non-null (which are the pending points) in one string. The problem is that the string is more than 255 and i can't put in a report without being trancuted.

I'm sorry if i'm not being clear enough.

 

[Rabbie]

Have you considered making that field Memo type rather than text so you can hold more than 255 chars?

 

[dealwi8me]

Yes, but how do i do that in a query?

 

[Shaunk23]

I got it.. I did have to put Len(String) = 0 then

Once i added those it works fine.

 

[robertbwainwright]

I think another way to solve (but I'm not entirely sure of your problem) would be the nz function: nz(String,"") this would replace the null with an empty string.