Question about decimal place formatting

[SonicClang]

The topic of decimal places seems to come up a lot, which I realized after an extensive use of the Search tool. After reading countless threads on the subject, I'm almost certain this question has never been asked here, or my situation is actually unique.

I have a much longer back story of what I'm working on that I typed up but then extremely truncated so I get more to the point.

On a form, I have VBA code setting the decimal places of two text fields. Everything looks great on the form, but when I send the values to the table, the decimal places disappear if they're zeros. If you need me to explain why 1.000 isn't the same as 1, I can. The question is, how do I send the values as they're being displayed on the form, to the table.

 

[HiTechCoach]

The question is, how do I send the values as they're being displayed on the form, to the table.

If the data is stored as a numeric data type, then normally you do not store the formatting. The formatting is applied when you display the value. The foramttoing does not determine the data precision, that data type does.

On forms and reports, you will set the format you want used to display the numbers within the control's properties.

I find it best to store data not formated whenever possible. This way you can easily change the format in the future.

 

[SonicClang]

The formatting is applied when you display the value.

Trust me, I'd prefer to keep it that way. I'd rather keep people out of the tables 100% of the time. Unfortunately the person who will ultimately be doing reporting on this data insists she needs to use the table directly. In this situation, me making a report and printing it for her wouldn't work. She needs to take the data out of this table and use "someone else's" reporting software.

I'm also running up against the way things have always been done here, and running into a lot of resistance. This company has always entered directly into Access tables, and NEVER used forms. This whole idea of making a end-user style program is totally new to them. I'm making progress though, and finally people are seeing the power of using VBA with forms.

Still, there has to be a way for a query to grab the value of a text field exactly the way it's being displayed to the user's eyes, right?

 

[SonicClang]

I've talked with the people who'll be using this database and we've come to the conclusion that we'll just set the fields all to text and forget about automatically calculating. It's pretty disappointing that Access can't store data to the table exactly as it's being displayed on a form. It's strange that the programmers can't imagine a world where extra zeros after a decimal point are significant. They've obviously never dealt with the FDA.

 

[HiTechCoach]

Still, there has to be a way for a query to grab the value of a text field exactly the way it's being displayed to the user's eyes, right?

You can format the data in a query, but you have to be careful tht you do not convert numeric data into text.

Do you need the data to be a numeric data type or text?

 

[HiTechCoach]

I've talked with the people who'll be using this database and we've come to the conclusion that we'll just set the fields all to text and forget about automatically calculating. It's pretty disappointing that Access can't store data to the table exactly as it's being displayed on a form. It's strange that the programmers can't imagine a world where extra zeros after a decimal point are significant. They've obviously never dealt with the FDA.

What you are describing sounds like more like a spreadsheet (wichi is like an report in databases) and not a table in a database. I find that user that work in tables a lot and not forms/reports get confused and this think that the tables should act like a spreadsheet.

IMHO, it is already handle the best way possible already. I deal with many government agencies at all levels and do EDI between them. I have never had any issues handling the data with Access.

Do you actually know of any RDMS (database) that does what you are asking?

 

[SonicClang]

I totally agree that most of what this company uses Access for, they could be using Excel. No one here has any idea what a relational database is.

In the case I'm talking about right now though, using a database is the correct way to do it. I don't have time now to describe how I've put this together. But I am using multiple tables and a spreadsheet wouldn't be able to do what I'm doing.

The only reason I bring up the FDA is because I see people say that zeros after the decimal point don't matter, therefore shouldn't be stored. In fact, they are significant if a scale reads 1.000 kg. Writing down "1" is not the same at all as "1.000".

 

[HiTechCoach]

I totally agree that most of what this company uses Access for, they could be using Excel. No one here has any idea what a relational database is.

In the case I'm talking about right now though, using a database is the correct way to do it. I don't have time now to describe how I've put this together. But I am using multiple tables and a spreadsheet wouldn't be able to do what I'm doing.

The only reason I bring up the FDA is because I see people say that zeros after the decimal point don't matter, therefore shouldn't be stored. In fact, they are significant if a scale reads 1.000 kg. Writing down "1" is not the same at all as "1.000".

I was not trying to suggest that you use a spreadsheet.

I was trying to pointing out that users get confused with the way Access displays a table. Since it looks a lot like a spreadsheet, users may get the idea that it would work like Excel, when it is really nothing like it. Unfortunately they are visually very close, which I think is what causes the confusion.