I spent a pretty significant amount of time working with Access in college, but that was years ago. Now that I'm actually trying to do something practical with it in the office, I can't remember how to do anything! This board has been helpful to read so far, and hopefully you can help me with my specific question.

In our system, shipping charges by unit vary between customers. I have a table set up with a field associating a customer's charge with the customer's name. The problem is, the two most common charge amounts are $0.25/unit and $0.305/unit (dealing in fractions of a cent here). Right now the field is set to show 3 decimal places, so the numbers are showing up as $0.250 and $0.305 respectively. While this works and all, I want to format the field so that when a currency figure with 2 decimal places is input, it will display only 2 decimal places, but in the event you enter one with three decimal places, all 3 places are shown (and the number is not rounded off).

Can anyone help me? My database is in its beginning stages, and I'm still somewhat of a beginner, so the less complicated, the better.

Thanks!

Hello

Make sure your Field DATATYPE is set to currency. Then it will do this automatically

Regards
Mark

The datatype is set to currency. If you do that, the decimal places property becomes "Auto", under which it still rounds off my amounts that include fractions of a cent. $0.305 becomes $0.31, and I don't want that.

Any other ideas?

no you can increase the decimal display accuracy to suit. It doesnt have to be auto (2dps). I think currency is the most accurate representation of data of all the available real number fromats.

This is actually more of a conceptual issue than a technical one. If your field CAN contain fractions to three places, it SHOULD contain fractions to three places - so that you can see the ones that DON'T contain fractions to three places. If you truncate the ones that show 0 in the third digit, someone else will always ask - Is that a rounded 2nd digit or an exact 2nd digit?

gemma's comment about CURRENCY being the most accurate data representation is not exactly correct, though it is a matter of some opinion. DOUBLE is the most accurate in general. CURRENCY - for things that SHOULD be treated as currency, might be better for some specific uses.

gemma is correct that you don't have to choose AUTO. You can pick a number, any number that makes sense.

My comment about showing the 3rd digit is due to a simple display concept. Don't leave displayed data in an ambiguous state. If I see a number such as 0.31, I'll never know whether it is .310 or .305 or .314 - and therefore will sometimes be blind-sided by the effects of the 3rd digit.

Show what you've got. Don't hide it if it makes a difference in your results.

I know I don't need to leave the decimal places on Auto -- right now I have it set on 3 decimal places, which displays the numbers as $0.250 and $0.305.

I know I can leave it at 3 decimal places -- that's what I've had all along. I just didn't know if there was any way to conditionally format the field. From what it sounds like, there isn't, in which case I'll leave it at 3 places.

The only reason I wanted to do this in the first place is that the $0.25 charge is associated with our older accounts, whereas the $0.305 is associated with our newer ones. There would never be a question as to whether $0.25 meant $0.25 or $0.254 or $0.246.

If there is no way to conditionally format, I'll just leave it like it is.

If the table field decimal place setting is set to 3, but the text box where the user enters details is set to Auto, then it should show 1,2 or 3 decimal places depending on what user has input