Help with output formating

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

Guest

I have been working for days on very complex formating query that I will be
updating a client regularly. I have reached the very end of the project with
only this to figure out.

Here is my delema: In my DB there are 3 fields I must merge into one. They
are:

[r_city] [r_state] [r_zip]

Seemed easy enough so I merged them with an expresion like this:

sql: [r_city] & ", " & [R_STATE] & " " & [R_ZIP] AS ADDR3
design view: ADDR3: [r_city] & ", " & [R_STATE] & " " & [R_ZIP]

The problem is due to the fact that [r_city] is 20 characters long so my
output is:

NASHVILLE , TN 37214 instead of : NASHVILLE, TN 37214 (the
client insists that it comes back with no extra space past the city name)

The other issue is that every line of data gets that comma. I need it to
return nothing if those fields are empty.

Please help.
Jason
 
To remove the space use the Trim function

ADDR3: Trim([r_city]) & ", " & [R_STATE] & " " & [R_ZIP]

To display comma only when there is value
Trim([r_city]) & (", " + [R_STATE] & " " & [R_ZIP])
 
That works except in a case where someone has accidentally been in that
field. so on some records there is just a ,

Any ideas?

Ofer said:
To remove the space use the Trim function

ADDR3: Trim([r_city]) & ", " & [R_STATE] & " " & [R_ZIP]

To display comma only when there is value
Trim([r_city]) & (", " + [R_STATE] & " " & [R_ZIP])

--
\\// Live Long and Prosper \\//


Jason Trolian said:
I have been working for days on very complex formating query that I will be
updating a client regularly. I have reached the very end of the project with
only this to figure out.

Here is my delema: In my DB there are 3 fields I must merge into one. They
are:

[r_city] [r_state] [r_zip]

Seemed easy enough so I merged them with an expresion like this:

sql: [r_city] & ", " & [R_STATE] & " " & [R_ZIP] AS ADDR3
design view: ADDR3: [r_city] & ", " & [R_STATE] & " " & [R_ZIP]

The problem is due to the fact that [r_city] is 20 characters long so my
output is:

NASHVILLE , TN 37214 instead of : NASHVILLE, TN 37214 (the
client insists that it comes back with no extra space past the city name)

The other issue is that every line of data gets that comma. I need it to
return nothing if those fields are empty.

Please help.
Jason
Click to expand...
Click to expand...
 
It will make it a bit more complicated

Trim([r_city]) & IIf(Len(Trim([R_STATE]) & Trim([R_ZIP])) > 0, ", " &
[R_STATE] & " " & [R_ZIP],"")

--
\\// Live Long and Prosper \\//


Jason Trolian said:
That works except in a case where someone has accidentally been in that
field. so on some records there is just a ,

Any ideas?

Ofer said:
To remove the space use the Trim function

ADDR3: Trim([r_city]) & ", " & [R_STATE] & " " & [R_ZIP]

To display comma only when there is value
Trim([r_city]) & (", " + [R_STATE] & " " & [R_ZIP])

--
\\// Live Long and Prosper \\//


Jason Trolian said:
I have been working for days on very complex formating query that I will be
updating a client regularly. I have reached the very end of the project with
only this to figure out.

Here is my delema: In my DB there are 3 fields I must merge into one. They
are:

[r_city] [r_state] [r_zip]

Seemed easy enough so I merged them with an expresion like this:

sql: [r_city] & ", " & [R_STATE] & " " & [R_ZIP] AS ADDR3
design view: ADDR3: [r_city] & ", " & [R_STATE] & " " & [R_ZIP]

The problem is due to the fact that [r_city] is 20 characters long so my
output is:

NASHVILLE , TN 37214 instead of : NASHVILLE, TN 37214 (the
client insists that it comes back with no extra space past the city name)

The other issue is that every line of data gets that comma. I need it to
return nothing if those fields are empty.

Please help.
Jason
Click to expand...
Click to expand...
Click to expand...
 
Back
Top