How to make it a real zero "0".

[Romano_odK]

Good afternoon,
My sheet gets it data from SQL using a query. When I refresh I get some huge numbers back that are '0' (column K) when you change from "General" to "Number" with 1 decimal, but the fact stays that underneath its still a huge number. What I would like is for these number to become a real Zero (0) after refreshing using VBA. Is this possible?

Thank you for your time.

Locatie controle 3.16f beta.xlsm
	A	B	C	D	E	F	G	H	I	J	K
5	Artikelcode	Omschrijving	Kostprijs	Ontvgstdatum	Aantal	Waarde	Vrrdrek	Std_locatie	Magazijn	Locatie	Voorraad
6	103017	Vitoglas 110 celband 9x8 mm grijs - 10x10 m	1,00	07/12/2022	52	0	3901	P07B	1	A00	0,0
7	106001	Coroplast AWX celband 6x2 mm zwart - 15x25 m folie	1,00	24/07/2020	50	0	3901	L06A	1	A00	0,0
8	133091	Kunststof Rasterblok 22x6 mm zwart - 100 stuks	1,00	25/05/2023	100	0	3901	B04B	1	A00	0,0
9	133093	Kunststof Rasterblok 24x1 mm wit - 100 stuks	1,00	28/07/2023	390	0	3901	A06	1	A00	0,0
10	133095	Kunststof Rasterblok 24x2 mm blauw - 100 stuks	1,00	28/08/2023	320	0	3901	A07	1	A00	0,0
11	134011	Kunststof Tunnelblok 22x3 mm rood - 100 stuks	1,00	11/09/2023	690	0	3901	A03	1	A00	0,0
12	134019	Kunststof Tunnelblok 24x3 mm rood - 100 stuks	1,00	29/06/2023	700	0	3901	A08	1	A00	0,0
13	134021	Kunststof Tunnelblok 24x4 mm geel - 100 stuks	1,00	28/08/2023	590	0	3901	A09	1	A00	0,0
14	134023	Kunststof Tunnelblok 24x5 mm groen - 100 stuks	1,00	31/08/2023	250	0	3901	A10	1	A00	0,0
15	134027	Kunststof Tunnelblok 26x3 mm rood - 100 stuks	1,00	13/07/2023	300	0	3901	A17	1	A00	0,0
16	134029	Kunststof Tunnelblok 26x4 mm geel - 100 stuks	1,00	30/08/2023	200	0	3901	A18	1	A00	0,0
Boekingen

Cell Formulas
Range	Formula
F6:F16	F6	=[@Kostprijs]*[@Voorraad]

 

[RoryA]

Those are not huge numbers, they are tiny. The simplest way would be to alter the query - what is it?

 

[Romano_odK]

Those are not huge numbers, they are tiny. The simplest way would be to alter the query - what is it?

Yes indeed, the better description would have been large numbers.
select top 100 percent

s.itemcode as Artikelcode
, i.Description as Omschrijving
, i.CostPriceStandard as Kostprijs
, o.datum as Ontvgstdatum
, o.binnen as Aantal
--, r.GLStock as Vrrdrek
, max(i.GLAccountDistribution) as Vrrdrek
, s.Warehouse as Magazijn
, v.maglok as Std_locatie
, s.WarehouseLocation as Locatie
, sum(s.quantity) as Voorraad


--, sum(s.freestock) as Voorraad
from StockBalances s with (nolock)
inner join items i with (nolock) on i.ItemCode = s.ItemCode
left join ItemAssortment r with (nolock) ON i.Assortment = r.Assortment
left join voorrd v with (nolock) ON v.artcode = s.itemcode and v.magcode = s.Warehouse
left join _cop_laatste_ont o with (nolock) on o.artcode = s.ItemCode
left join grtbk g (nolock) on i.GLAccountDistribution = g.reknr
where 1=1
and g.omzrek = 'G'

 

[RoryA]

Yes indeed, the better description would have been large numbers.

Again, no. They are very small (or they wouldn’t round to 0).

What type of database is the source, and how are you connecting to it?

 

[Peter_SSs]

Perhaps you mean this?

the better description would have been large long numbers.

 

[Romano_odK]

Again, no. They are very small (or they wouldn’t round to 0).

What type of database is the source, and how are you connecting to it?

Ok so my English isn't perfect, but I try and yes its a long number. Its a connection to a SQL database. I connect though Data\Queries and connection.

Connection string: DRIVER=SQL Server Native Client 11.0;SERVER=kohlersql01;UID=Romano;Trusted_Connection=Yes;APP=Microsoft Office;WSID=RKE-PC01;DATABASE=100;

 

[RoryA]

Do you know what data type the quantity field in the database is? It seems very odd that summing quantities would produce this problem, but you may have to simply round the result of the sum in the query: ROUND(sum(s.quantity), 1) as Voorraad for instance

 

[Romano_odK]

Do you know what data type the quantity field in the database is? It seems very odd that summing quantities would produce this problem, but you may have to simply round the result of the sum in the query: ROUND(sum(s.quantity), 1) as Voorraad for instance

Good morning, that "simple" suggestion worked. Thank you for that advise and have a great day.