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

[mstrcmtr]

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 ?

 

[Olaf Doschke]

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.

 

[mstrcmtr]

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

 

[Olaf Doschke]

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.

 

[JRB-Bldr]

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

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

 

[Mike Gagnon]

JRB
Perhaps a typo?

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.