×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Blank instead of zero in numeric column right justify issue

## Blank instead of zero in numeric column right justify issue

(OP)
With the help of this showing Zero value to Blank in Numeric column

XY = 8542

TRANSFORM(XY ,[@Z 99,99,999.99]) As ZB_XY

By Adding @ZB in Transform Numeric value Left Justify
TRANSFORM(XY ,[@ZB 99,99,999.99]) As ZB_XY

Q. How can a Numeric value ( which is actually convert to Alpha Value because of Transform) Right Justify with Transform

### RE: Blank instead of zero in numeric column right justify issue

Remove the "B", right justify is the default which you have changed. In other words, your first expression is what you need.

### RE: Blank instead of zero in numeric column right justify issue

Hello Sir,

In the format of the TRANSFORM you're using, the remaining numeric placements (10 thousandth and above) are being converted to spaces. This could be what is being displayed in the control which makes it to look like the value is right-aligned. Try using ALLTRIM(TRANSFORM(...)) without the "B" and see how it goes.

kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."

### RE: Blank instead of zero in numeric column right justify issue

(OP)
I have to apply this on whole column of a table

on Alltrim() column will Left Align and i want to Right Align the whole column

ALLTRIM(TRANSFORM(XY ,[@Z 99,99,999.99]) ) As ZB_XY,;

1,05,261.00
12,230.00
9754.00
834.50
67.25
9.20
0 showing as blank
34,223.00
210.00

### RE: Blank instead of zero in numeric column right justify issue

If I understand you right, you have a numeric field named XY and a character field named ZB_XY. You would like to fill ZB_XY with a character representation of XY, but if XY is zero you want ZB_XY to be blank. Is that correct?

If so, you shouldn't be doing ALLTRIM() if you want ZB_XY to be right-aligned. The rest of your code should be fine. In other words:

#### CODE -->

SELECT TheTable
REPLACE ALL ZB_XY WITH TRANSFORM(XY, "@Z 99,99,999.99") 

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

### RE: Blank instead of zero in numeric column right justify issue

You just need to use a monospaced font to show it right aligned. It is right aligned when you use TRANSFORM(XY, "@Z 99,99,999.99"), no computer and no software and no programming language or cntrol will show it right aligned with a proportional font, though. just think about how that should work out. Even if there would be a way to have the rendering of a proportional font be anchored at the right side of a rectangle, the digits can only align perfectly with a monospaced font. Always and only. Every time you see some numbers right aligned it's not only because of being displayed right justified, but it is because people use a monospaced font for that, too.

To show you why you never get to the correct right justified formatting with a proportional font, take a look at extreme slim (1) and wide (0) digits in a proportional font:

What this shows is the scenario the right justification isn't made by spaces from the left but really by rendering with the text anchored to the right border.

What TRANSFORM does is put enough spaces as the prefix. But displayed in a proportional font the space character is slimmer than a digit, just like here the 1 digit is slimmer than the 0 digit, so the numbers are not shifted the right amount to the right. Even in proportional fonts, which have all digits in the same width, the space character typically is slimmer.

Use Courier New, that's quite the standard font for monospaced or non-proportional fonts.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!