Adding a decimal place to an interger field

[BrianS]

Hello, I was hoping someone might know if this is possible. I'm a PLC guy who was successful in loading an Access database with data from the PLC's memory. Unfortunately, I'm only able to transfer Integer values. In the database I'd like to show one of the fields as a real number by formatting the Integer value to add a decimal place. Example: 2505 to 250.5
I'm very new at using Access, so any help would be appreciated.

[JoeM]

Integers, by definition, do not have a decimal component. If you are going to use the Integer datatype, you cannot override that.
How come you are only able to transfer Integer values? What exactly is limiting you?

Here are some possible workarounds, you may want to consider, if you are unable to use Single, Double, or Decimal Data Types.

1. Depending upon how many decimal places you need to track, use "implied decimals".
For example, if you need two decimals, multiply every number by 100. So "250.57" is stored as "25057".

2. Split your number into two fields, one for the Integer portion, and one for the decimal portion.
So, "250.57" would have "250" in the first field, and "57" in the second field.

3. Transfer the value over as Text instead of a number type.

[IrogSinta]

In the table's design view, select the field with the Number data type, then look at its Field Size property; if it is set to Integer or Long Integer, change this to Single or Double.

Ron

[JoeM]

In the table's design view, select the field with the Number data type, then look at its Field Size property; if it is set to Integer or Long Integer, change this to Single or Double.

That certainly would be the preferrable thing to do, but it sounds like that isn't an option:

Unfortunately, I'm only able to transfer Integer values.

I am not entirely clear why this is. It sounds like it may be an issue from the program they are creating the data file in?

[BrianS]

Hi JoeM, thank you for replying. The PLC I'm using is the Host Engineering's Do-more and the software (server) to transfer the data is the BizwareDirect PLC Pro. The software is setup to transfer a block of V-memory. The PLC defines V-mem as integer values of 0-65535 and R-mem for real values. The server software isn't capable of mixing and matching. So, I was going to convert my real value to an integer and try to add back the decimal place in the spread sheet. I have also transferred over text as well, but it is such a memory hog and I'm limited to how much I can send over in one packet.

[JoeM]

Do options 1 or 2 I presented in my original reply work for you?

[IrogSinta]

It sounds like you were able to transfer them into an Access table as integers. So now you can go into the table's design and change the field size to Single or Double. Then run an update query to convert your integers back to real. For 1 decimal place you would do something like this:

Update NameOfTable Set NameOfField = NameOfField/10

[BrianS]

Hi Joe & Irog,
I changed the field to a double and had no problem bringing in 2501. But I was hoping there was some kind of formatting I could do to change it to 250.1
The software is very limited as it hasn't come fully up to speed with the Do-more. They do have another version coming out in a couple of months and are increasing the size of the memory block that can be transferred over. I'll see if they have any plans on handling real numbers.

Thanks for helping.
Brian

[JoeM]

But I was hoping there was some kind of formatting I could do to change it to 250.1

Not formatting. Formatting would only change how it looks anyway, and not change the actual value in that field.
Use an Update Query, like IrogSinta suggested, where you divide the values by 10 after importing them.

[BrianS]

I haven't done much with queries yet, but let me play with it and see how I make out. This sounds like what I was looking for. Thanks.

[JoeM]

That's a good idea. Queries are one of the most userful, powerful, and important reasons to use Access.

Here is a link on Update Queries here: https://support.office.com/en-us/art...9-35e5ee1e0514
There are a ton more tutorials and YouTube videos on it, if you do a Google search on "Access Update Query".

[BrianS]

Thanks guys, the query worked great. Definitely what I was looking to do. You have been a big help.