numeric data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How can I check if the third decimal of a number is 0? I know it can't be as
hard as I am making it - maybe I have Access burn-out?

I am checking (in a query) a field with only five digits two numbers with
three decimal places. If the number does not have a zero in that position I
have to manipulate another field that calculates it to a number that does
have a result of zero in that position.

Any takers?
 
Is this a number field or a text field? If a number field, what type of number
field - currency, single, double?

Can you give an example?

For instance, 12.359 needs to be converted to 12.35 or 12.36

If YourNumber*1000 = Int(YourNumber*100) * 10 Then the 3rd decimal position was
a zero otherwise, the digit was not a zero. Of course if you are using double
or single type numbers there is the possibility that you could still get a bad
result due to the imprecision of floating point numbers.
 
Thank you John,

In answer to your question, this field is a calculated field from case price
(which is a single/fixed field)

Case price/units = bottle price (simple enough)

Case price is usually a given and bottle price is calculated as above,
however, if the bottle price calculates to a number other than a zero for the
third digit of the decimal place, then the calculation is reversed and the
Case price needs to be calculated by the bottle price after making the third
digit a zero.

I used your example on on this data:
200 cases 12 units (200/12=16.66666667) obviously the third digit is not 0
if(16.66666667*1000=int((16.66666667*100)*10,1,0)
results = 0 meaning this number is not okay to use

changed the third digit to 0 (16.660)
if(16.660*1000=int((16.660*100)*10,1,0)
results = 1 meaning this number is okay to use

Exactly the answer to the question that I asked. The problem is that I
might not have asked the right question. Since the case price is usually a
given and the bottle price calculated, I guess I don't know how to
programically allow for the exception in Access.
Or am I just confusing the issue since I've already coded the program with
the original calculation?

Thank you for your response and time...I appreciate it.

Donna
 
Update:

I have a query which calculates whether the bottle price is valid, but I
don't know how to then change the third digit (automatically) to a zero when
it is not a valid bottle price.
That is the problem.
 
You could try another method. I'm not sure what you are doing, but perhaps the
following would work

IIF ([Case Price]*1000 MOD [Units] = 0, [Case Price]/[Units], [Case Price]*[Units])

Or perhaps you mean

IIF ([Case Price]*1000 MOD [Units] = 0, [Case Price]/[Units], [Case Price]*[Units])

If what you want is to strip off the last digit, then you could use

Int(CCur([Case Price])*100/[Units])/100
 
John…

I will have to recreate this thread from memory since I spent a lot of time
composing it and lost it when I tried to send it…I had many interruptions
(you’d think I’d learn to type it and copy it to the NG-since this isn’t the
first time this has happened to me) :-(

Here is what (I hope) I wrote:

Although this point is probably mute, since I think I found the solution. I
say “think†because it was too simple (red face). Here is the simple
solution that I came up with.

Valid Btl Price: =IIf([Bottle Price]*1000=Int([Bottle Price]*100)*10,1,0)
***your calc
Bottle Price: =Case Price/Units per case
Rnd Price: =Round([Bottle Price],3)
New Case Price: = IIf([Valid Btl Price]=0,[Rnd Price]*[Units per case])

***This calculation gives me inconsistent results. Examples:

Case price = 55.2
Units = 12
Bottle price (in Access) = 4.60000006357829
Bottle price on calculator = 4.6

Case price =116.4
Units = 12
Bottle price (in Access) =9.70000012715658
Bottle price on calculator = 9.7

Both of the bottle prices have a zero as the third position of the decimal
(which is what I thought I was checking for), so why does Access respond with
a zero (meaning that the bottle price is not acceptable) in the calculation?
This doesn’t seem to be a problem, since I still get the correct case price
when multiplied times the units. It just performs this calculation when it
is not necessary.

A little background, this company uses beverage industry software that was
designed to use bottle price (as opposed to case price). The pricing program
gives the case price and calculates the bottle price. The beverage software
will not allow the third digit of the decimal place to be anything other than
a zero. Thus, the question.

What is stored as the bottle price? Will this come back and bite me later?

Regarding your last thread, I don’t see any difference between the two
calculations that you gave first, am I missing something?...or did you just
copy and paste and forget to change the second calc?

I didn’t try the last calc, since I don’t want to strip off the last digit –
as you said it would do.

I think I captured it all…whew!

Thanks,

Donna

PS...after I composed this again...our server went down...can you believe it?



John Spencer (MVP) said:
You could try another method. I'm not sure what you are doing, but perhaps the
following would work

IIF ([Case Price]*1000 MOD [Units] = 0, [Case Price]/[Units], [Case Price]*[Units])

Or perhaps you mean

IIF ([Case Price]*1000 MOD [Units] = 0, [Case Price]/[Units], [Case Price]*[Units])

If what you want is to strip off the last digit, then you could use

Int(CCur([Case Price])*100/[Units])/100
Click to expand...
 
Back
Top