Trimming the right most text

[dmorgan777]

I have an SQL query that I am combining text from multiple fields and then I
am trying to trim the last character but I must be doing something wrong.
Here is the SQL Query:

SELECT HM.Service_City, IIf([Site_Name]<>"",[Site_Name] & " • ","") &
IIf([Mailing Addr]<>"",[Mailing Addr] & " • ","") &
IIf([Street_Addr]<>"",[Street_Addr] & " • ","") &
IIf([Zip_Code]<>"",[Zip_code] & " • ","") &
IIf([Contact_Addr]<>"",[Contact_Addr] & " • ","") &
IIf([Contact_City]<>"",[Contact_City] & " • ","") &
IIf([Contact_Stt]<>"",[Contact_Stt] & " • ","") &
IIf([Contact_Zip]<>"",[Contact_Zip] & " • ","") & IIf([Phone1]<>"",[Phone1] &
" • ","") & IIf([Phone2]<>"",[Phone2] & " • ","") &
IIf([Toll_Free]<>"",[Toll_Free] & " • ","") & IIf(<>"",[URL],"") &
IIf(RTrim(" • "),"","") AS Expr1, HM.Number_of_Rooms, HM.Breakfast,
HM.Restaurant, HM.Lounge, HM.Pool, HM.Hot_Tub, HM.Handicapped_Access,
HM.Non_Smoking_Rooms, HM.Pets_Allowed, HM.Rates, HM.Major_Credit_Cards
FROM HM
ORDER BY HM.Service_City;

The problem is the with this: IIf(RTrim(" • ","","") It does not work, any
ideas what I'm doing wrong? Any help is appreciated. Thank you.

 

[Jeff L]

What is the point to the RTrim statement? It doesn't look like it does
anything and can be taken out.

 

[Dale Fye]

the IIF function requires that its first parameter contain an argument that
will return a TRUE or FALSE, so it knows which if its return arguments to
return. RTrim(" • ") does not evaluate to TRUE or FALSE, so you cannot do
this.

 

[dmorgan777]

What happening is some of the fields have no data and the query leaves a " •
" at the end of the expression, I'm just trying to create a condition if that
is the last to delete it.

What is the point to the RTrim statement? It doesn't look like it does
anything and can be taken out.

I have an SQL query that I am combining text from multiple fields and then I
am trying to trim the last character but I must be doing something wrong.
Here is the SQL Query:

SELECT HM.Service_City, IIf([Site_Name]<>"",[Site_Name] & " · ","") &
IIf([Mailing Addr]<>"",[Mailing Addr] & " · ","") &
IIf([Street_Addr]<>"",[Street_Addr] & " · ","") &
IIf([Zip_Code]<>"",[Zip_code] & " · ","") &
IIf([Contact_Addr]<>"",[Contact_Addr] & " · ","") &
IIf([Contact_City]<>"",[Contact_City] & " · ","") &
IIf([Contact_Stt]<>"",[Contact_Stt] & " · ","") &
IIf([Contact_Zip]<>"",[Contact_Zip] & " · ","") & IIf([Phone1]<>"",[Phone1] &
" · ","") & IIf([Phone2]<>"",[Phone2] & " · ","") &
IIf([Toll_Free]<>"",[Toll_Free] & " · ","") & IIf(<>"",[URL],"") &
IIf(RTrim(" · "),"","") AS Expr1, HM.Number_of_Rooms, HM.Breakfast,
HM.Restaurant, HM.Lounge, HM.Pool, HM.Hot_Tub, HM.Handicapped_Access,
HM.Non_Smoking_Rooms, HM.Pets_Allowed, HM.Rates, HM.Major_Credit_Cards
FROM HM
ORDER BY HM.Service_City;

The problem is the with this: IIf(RTrim(" · ","","") It does not work, any
ideas what I'm doing wrong? Any help is appreciated. Thank you.

http://<>"",[URL],"") & IIf(RTrim("... is appreciated. Thank you.[/QUOTE] [/QUOTE][/QUOTE][/QUOTE]

 

[John Spencer]

You are testing for zero-length strings and are not testing for null values.

Trry changing the IIF to
IIF(Site_Name & "" <> "", Site_Name & " . ","")

Another way to handle this if you are really just testing for null values is

(Site_Name + " . ") & ([Mailing Addr] + " . ") & (Street_Addr + " . ")

The way that works is that Access handles concatentation differently with
the + and & operators.

The & treats nulls as if they were "" (a zero length string) and combines
that zero-length string with whatever else is there.

The + treats nulls as nulls and when you combine Null with anything you get
Null. To human eyes Null and a zero-length string look the same, but to
Access they are not the same.

 

[dmorgan777]

That is very similar to what I figured out myself, here's what I came up with:

IIf([Site_Name]<>""," • " & [Site_Name],"") Looks about the same except the
first IIf statement I left out the bullet. Thank you for your response.

You are testing for zero-length strings and are not testing for null values.

Trry changing the IIF to
IIF(Site_Name & "" <> "", Site_Name & " . ","")

Another way to handle this if you are really just testing for null values is

(Site_Name + " . ") & ([Mailing Addr] + " . ") & (Street_Addr + " . ")

The way that works is that Access handles concatentation differently with
the + and & operators.

The & treats nulls as if they were "" (a zero length string) and combines
that zero-length string with whatever else is there.

The + treats nulls as nulls and when you combine Null with anything you get
Null. To human eyes Null and a zero-length string look the same, but to
Access they are not the same.

I have an SQL query that I am combining text from multiple fields and then
I
am trying to trim the last character but I must be doing something wrong.
Here is the SQL Query:

SELECT HM.Service_City, IIf([Site_Name]<>"",[Site_Name] & " . ","") &
IIf([Mailing Addr]<>"",[Mailing Addr] & " . ","") &
IIf([Street_Addr]<>"",[Street_Addr] & " . ","") &
IIf([Zip_Code]<>"",[Zip_code] & " . ","") &
IIf([Contact_Addr]<>"",[Contact_Addr] & " . ","") &
IIf([Contact_City]<>"",[Contact_City] & " . ","") &
IIf([Contact_Stt]<>"",[Contact_Stt] & " . ","") &
IIf([Contact_Zip]<>"",[Contact_Zip] & " . ","") &
IIf([Phone1]<>"",[Phone1] &
" . ","") & IIf([Phone2]<>"",[Phone2] & " . ","") &
IIf([Toll_Free]<>"",[Toll_Free] & " . ","") & IIf(<>"",[URL],"") &
IIf(RTrim(" . "),"","") AS Expr1, HM.Number_of_Rooms, HM.Breakfast,
HM.Restaurant, HM.Lounge, HM.Pool, HM.Hot_Tub, HM.Handicapped_Access,
HM.Non_Smoking_Rooms, HM.Pets_Allowed, HM.Rates, HM.Major_Credit_Cards
FROM HM
ORDER BY HM.Service_City;

The problem is the with this: IIf(RTrim(" . ","","") It does not work,
any
ideas what I'm doing wrong? Any help is appreciated. Thank you.

http://<>"",[URL],"") & IIf(RTrim("... is appreciated. Thank you.[/QUOTE] [/QUOTE][/QUOTE][/QUOTE]