Decimal place issue (1 Viewer)

[munkeyroot]

Hi Everyone

hope you can help i am having an issues with data with decimal places.

Background:
My third party software controls our stock and qty's. when users enter new stock they enter the new amount. thats ok no worries but some qty's get entered with a decimal place and some do not.
so for instance qty for part xx23 gets entered in as "3" and qty for part yy34 get entered in as "3.00" (unfortunately we cannot change the format once it has been entered)

so when i view the table were the qtys for each stock it shows without any decimal places so 3.00 becomes 300, 30.00 becomes 3000 - this would not be bad in access i could say /100 in the form - however because of the other qty's without decimal place it will show up as "Zero".

the qty loads from a query with other stock information ie part number price etc.

so my question in access is there a way to tell the query to /100 but also ignore single number but then this would also cause issue if the actual qty was 10 or 100.

i hope i'm making my self clear

any thoughts guys?

cheer munk

 

[arnelgp]

use Int() function

Int([fieldname]) *100

 

[Ranman256]

format is irrelevant.
3.00 = 3
3 = 3
3.0000 = 3

so why is your query changing the value? Are you storing it in a Currency field? Double? What field type?
None will alter the value , even tho you can change the format.

Queries can /100 if you need.
IIF(instr([field]),".")>0 ,[field]/100,[field])

 

[Solo712]

Hi Everyone

hope you can help i am having an issues with data with decimal places.

Background:
My third party software controls our stock and qty's. when users enter new stock they enter the new amount. thats ok no worries but some qty's get entered with a decimal place and some do not.
so for instance qty for part xx23 gets entered in as "3" and qty for part yy34 get entered in as "3.00" (unfortunately we cannot change the format once it has been entered)

so when i view the table were the qtys for each stock it shows without any decimal places so 3.00 becomes 300, 30.00 becomes 3000 - this would not be bad in access i could say /100 in the form - however because of the other qty's without decimal place it will show up as "Zero".

the qty loads from a query with other stock information ie part number price etc.

so my question in access is there a way to tell the query to /100 but also ignore single number but then this would also cause issue if the actual qty was 10 or 100.

i hope i'm making my self clear

any thoughts guys?

cheer munk

I am afraid this is not clear at all. The values held by the fields are not controlled by the presence of a decimal point but the data type of the field. If the field is defined as integer then, naturally, it will not accept decimal points or will round off the number when they are present. In any case, Access will not change the placement of the decimal point. That just does not make sense. It makes no sense to devise methods to modify the actual numbers when the solution is in applying the correct data type (and perhaps rounding).

https://eggerapps.at/mdbviewer/docs/en/field-types.html

Best,
Jiri

 

[munkeyroot]

hey ranman and arnelgp
thanks for you comments.

arnelgp: thanks i will give it a shot

Ranman, access is not changing the format that should be easy enough, however its the third party table where the qty is stored, when the stock qty get inputted some parts are entered as "3" or "3.00". so when i look at the 3rd party table before it comes to access, 3 is still 3 but 3.00 is 300.

 

[ypma]

munkeyroot: Assuming the filed is numeric have you tried the setting, under data type the Decimal places to ZERO . This would work a tale level.

Just a thought

YPMA

 

[arnelgp]

im sorry that will not do the trick. instead use:

Val(Replace([Fieldname], "00", "", 1, 1))

so to Update the field using Update Query:

Update tablename set fieldname=Val(Replace([Fieldname], "00", "", 1, 1))

 

[The_Doc_Man]

This makes no sense at a much higher level. If this inventory data resides all in the same table then the same format MUST apply to every quantity data entry. Access doesn't DO conditional data types. So... what IS the third-party software that does this to you? And how quickly can you sue the vendor for fiduciary irresponsibility? In that the software appears to be incapable of doing that for which it was sold... And while I'm known to be facetious sometimes, this is about halfway NOT facetious. Do you have tech support from the vendor? Because if this really IS the fault of this third-party software, we aren't going to be able to do doodlum SQUAT with it without voiding your software warranty, assuming you have one.

 

[static]

the other qty's without decimal place it will show up as "Zero".

The only way a numeric data type would show as "Zero" (string) is if you've applied a format to the field...

 

[CJ_London]

perhaps the third party software is storing the value as text? what is the query the quantity loads from? How are you connecting to the third party software?

 

[Pat Hartman]

Can you look at the data in the tables? Can you see what the data type is of the column in question?

 

[JHB]

If all other solutions fails, try replace the . with a ,.

Replace([TheFieldName], ".",",")

 

[munkeyroot]

Hey everyone

sorry i have not posted much for this thread, other work came up.
so little update. i have managed to find the tables on the TPS (Third Party Software.
so the tables in the TPS called

"tbl_Fact"
This stores the "code", "dps" field

"tbl_name"
this stores the "cn_fact", "Instock" fields

the "code" is a list of different styles, (this is also in the "tbl_name" called "CN_fact". So Code in tbl_FACT = CN_fact in tbl_Parts).

"DPS" gives a number of decimal places.

"INstock" gives a whole number of qty.

so on a qry i can include a relationship against tbl_fact - Code and tbl_parts - CN_fact. so far so good.

so if now the dps field in the "tbl_Fact" tells the decimal place - how would i tell the instock field to place a decimal place from what the "code".

if this doesn't make sense lol. i have attached a spreadsheet to help

 

[The_Doc_Man]

OK, a couple of questions:

1. When you use this putative query, is it to import data to your database or is it to make data available for some other purpose such as reporting. You CAN use a form to view this from a query, but if it is in Excel, you can't change contents. Which means you can't be using the Excel data as a back-end external table for inventory control.

2. How often would you use this putative query? I.e. once a day? Every time you look? Every time anyone wants a report?

What I am thinking is that you might need do something like this using the SWITCH function: (And the reference for SWITCH is also included).

https://support.office.com/en-us/article/switch-function-d750c10d-0c8e-444c-9e63-f47504f9e379

1. Make an INNER JOIN query between the NAME and FACT tables (though you need to watch out because NAME is a reserved word). JOIN between the CN_FACT and CF_CODE fields.

2. Include something like this in your query:

SELECT ....., CF_INSTOCK / SWITCH( CF_DPS=2, 100, CF_DPS=1, 10, CF_DPS=0, 1, TRUE, 1), ... 
FROM CFITEMS INNER JOIN CFDETL ON CFITEMS.CN_FACT = CFDETL.CF_CODE
WHERE ...

Look at the reference to switch. With an inner join, this should not produce any nulls. If it ever COULD produce nulls, then the expressions in the query get more complex because you would need to include the NZ function to prevent trying to compare anything to a null. Note also that I included a "trap" for a case where something other than 0, 1, or 2 was included as the CF_DPS field. Remember that SWITCH processes left to right, so that "TRUE" is the catch-all case.

 

[CJ_London]

I'm assuming that if a value is 200 and the dps is 2 you mean 2.00 and not 200.00

why not just to a simply division

instead of (quoting Doc)

CF_INSTOCK / SWITCH( CF_DPS=2, 100, CF_DPS=1, 10, CF_DPS=0, 1, TRUE, 1)

use

[cn_instock]/10^[cf_dps]


cn_intstock...cf_dps..result
200...…………..0...……..200
200...…………..1...……..20
200...…………..2...……..2
200...…………..null...….null

 

[The_Doc_Man]

CJ - you would need INT( CN_INTSTOCK / 10^CF_DPS ) and even then would need to be careful. The "^" operator is implemented as EXP( LOG(X) * Y ) and therefore can have some occasional rounding anomalies. Eg. 10^2 might not be exactly 100, might be 99.9999 and change. And CERTAINLY don't use FIX because that is totally unforgiving in the case of those pesky little fractional results.

Also, the "^" operator is no better than the SWITCH function if it hits a NULL, so the possibility of needing an NZ in the mix is certain not out of the question.

 

[CJ_London]

why would you want to use int? if the value was 123, wouldn't you want 1.23 or 12.3? - although I note from the OP's examples, values with a dp value >0 all end in 0 or 00. And why don't you need to use it with the switch function?

I accept your rounding anomalies argument, however in the case of 10^1 or 10^2 it returns 10, 100 etc - perhaps not for other numbers or other scenarios but that is not a concern here.

Also, the "^" operator is no better than the SWITCH function if it hits a NULL, so the possibility of needing an NZ in the mix is certain not out of the question.

sorry, don't agree

123/10^Null

returns null (no error) so to use nz would only be relevant if you wanted a default of 0,1 or 2 dp in the event of a null - which I would think the OP would want to know has not been defined

It also has the benefit of covering the situation if 3 or more dp's are required

 

[The_Doc_Man]

No matter how we cut it, the OP at least has a couple of ideas on how to proceed. Maybe we can wait to see if another question comes up.

 

[Pat Hartman]

If you've never worked with software such as SAP, you're probably never run into this type of abomination. These ERP systems are designed to be everything to everybody and therefore are nothing to anybody. Pretty much everything is stored as a string which then requires attributes to understand. It's like my "smart" phone. It is a flashlight, a camera, a GPS, and a hundred other things but it is a p**s-poor phone.

 

[CJ_London]

It is a flashlight, a camera, a GPS, and a hundred other things but it is a p**s-poor phone.

totally agree - I have an iPhone X. It sends me a message to say I missed a call (rather than ringing and/or giving me enough time to answer) and I have to use speaker if I want the person on the other end hear what I'm saying since the microphone doesn't work when held a la phone style - so now everyone else can hear both sides of my conversation. Perhaps a conspiracy theory but I cynically think the former behaviour is so my carrier gets to make a charge for the return call!