Trimming the right most text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
What is the point to the RTrim statement? It doesn't look like it does
anything and can be taken out.
 
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.
 
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.

Jeff L said:
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.
Click to expand...

Click to expand...
http://<>"",[URL],"") & IIf(RTrim("... is appreciated. Thank you.[/QUOTE] [/QUOTE][/QUOTE][/QUOTE]
 
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.
 
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.

John Spencer said:
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 said:
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.
Click to expand...

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