×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*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.

Students Click Here

Jobs

Replacing zeros of Numeric Field with Blank But require Field type Numeric not Character

Replacing zeros of Numeric Field with Blank But require Field type Numeric not Character

Replacing zeros of Numeric Field with Blank But require Field type Numeric not Character

(OP)
Following SQL command create query with replacing zeros with blank in Qty_InHand column which is Numeric field but by doing this it make the Qty_InHand column type Character.

SELECT Item_Name,
TRANSFORM(Qty_InHand,[@Z 99999999]) As Qty_InHand;
FROM Stock ;
INTO CURSOR Tc_Stk ;
READWRITE

Is it Possible that it will replace zeros with blank and field type remain Numeric of Qty_InHand field ?

RE: Replacing zeros of Numeric Field with Blank But require Field type Numeric not Character

What you say is, Qty_InHand originally is numeric. Then it doesn't have leading zeros and there is no need to transform it in any way, is there?

Can you give examples of Qty_InHand values and how you want them to end up?

Edit: I think I realize now you only want the exact values of 0 or 0.0 to be displayed blank. Well, that's a thing you can do in a report in the format tab:


Or in textboxes on forms use "Z" as Format.

Don't convert the data in a query, make use of output format options.

Bye, Olaf.

RE: Replacing zeros of Numeric Field with Blank But require Field type Numeric not Character

(OP)
Qty_InHand is originally numeric Field through transform() replace zeros with blank but it change the type of Qty_InHand to Character from Numeric in SQL Cursor

Data detail of Qty_InHand field

32
0
108
45
0
29
60
0
0

Cursor Created to display items qty in hand in List box control for View purpose , Grid column has option of formating but List control NOT and here using List box control

RE: Replacing zeros of Numeric Field with Blank But require Field type Numeric not Character

About Listbox. well, I see.

You may SET NULLDISPLAY TO '' and use EVL(Qty_InHand,.NULL.) AS Qty_InHand
I would perhaps prefer using a Grid for the availability of Format.

Bye, Olaf.

RE: Replacing zeros of Numeric Field with Blank But require Field type Numeric not Character

You can easily create a secondary Character field representing the Numeric field and display that one instead of the Numeric field itself.

CODE -->

SELECT Item_Name,
Qty_InHand,;
IIF(Qty_InHand > 0, TRANSFORM(Qty_InHand,"99999999"], "") As cQtyInHand;
FROM Stock ;
INTO CURSOR Tc_Stk READWRITE 


Now you have BOTH, the Numeric field (Qty_InHand) with which to do anything you want and a Character representation (cQtyInHand) of the Numeric to display wherever it is needed.

One other note, since you want this displayed in a List Box (although I too would prefer to use a Grid), you most likely want DISTINCT values shown - since duplicate values by themselves as you show above don't make much sense.

Good Luck,
JRB-Bldr

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.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close