Update query with calculation trouble

[NightWalker]

I am trying to make an update query that will multiply a field [PrePressureReading] times a number based upon the string in another field [PrePressureUnits] and put the result into field [PreReading]. There are 3 options in the units field. (G^, M^, or ^) The ^ is the omega symbol. Here is what I have so far but I am not getting any results so far.

IIf([Copy Of tbl_343sTested]![PrePressureUnits]="G?",[Copy Of tbl_343sTested]![PreReading]=([Copy Of tbl_343sTested]![PrePressureReading]*100000000),"")

Please point me in the right direction. Thank you.

[Micron]

I could be wrong, not knowing what the business is about, but - generally you don't store calculations. Unless you have an unconventional need for this, the right direction is to not store calculations. They should be performed in a query, form or report. Again, generally speaking...

If you do have a valid case for this, then Copy Of tbl_343sTested]![PrePressureUnits]="G?" might work if the table contained one row. Otherwise, which value from the field would Access choose? I also think that the second part of IIF cannot perform a calculation/update, rather it can only provide a value if the first parameter is true.

[NightWalker]

The purpose here is not to store calculations but to better store the data in format that is easier to manipulate. After I do this update query the two fields where the original information came from will be removed from the table. I didn't realize how much trouble it would cause me to have the omega symbol stored in a field. I am trying to rectify that with this query. Thank you Micron for your time. This is my first update query and I am finding it to be a little challenging.

[Bulzie]

UPDATE [Copy Of tbl_343sTested] SET [Copy Of tbl_343sTested].PreReading = IIf([Copy Of tbl_343sTested]![PrePressureUnits]="G^",[Copy Of tbl_343sTested]![PrePressureReading]*100000000,IIf([Copy Of tbl_343sTested]![PrePressureUnits]="M^",[Copy Of tbl_343sTested]![PrePressureReading]*200000000,IIf([Copy Of tbl_343sTested]![PrePressureUnits]="^",[Copy Of tbl_343sTested]![PrePressureReading]*300000000,"")));

[Micron]

and put the result into field [PreReading]. That sure reads like a stored calculation.
First, lets ensure we are talking update here. You do have a record where the field value you want to affect is either already there or is null? If there is no record, then you cannot update a record that doesn't exist - you need to append. Secondly, as noted you cannot update a field from within an IIF statement. You can use the result of the calculation in the update, or the alternative ("" in your case) if the IIF test returns false. Something like

IIf([Copy Of tbl_343sTested]![PrePressureUnits]="G?",[Copy Of tbl_343sTested]![PrePressureReading]*100000000),"")

I'm not sure the caret character has any bearing on this.

[NightWalker]

Micron,

I don't think I am explaining myself correctly.
I am trying to delete 2 fields from the table and store just a number in a single field instead of a number and a unit field. I have already changed the form code to convert the 2 text boxes where the reading and the unit to a number. It will store this information in the new field that is being updated in this query. I need to convert the 2 fields to 1 on the original table so the new form will store the data properly.

I am trying to fix a flaw in my original design.

[NightWalker]

Ok I got it figured out with part of Bulzie's code. here is what I have now but I am having a problem with some of the results it is giving me.

IIf(Mid([PrePressureUnits],1,1)="G",[PrePressureReading]*1000000000,IIf(Mid([PrePressureUnits],1,1)="M",[PrePressureReading]*1000000,[PrePressureReading]))

In the table I had to change the field size from long integer to double. the challenge I have now is all the results where the original number has an even decimal the results are not whole numbers.

[Bulzie]

Round Function?

IIf(Mid([PrePressureUnits],1,1)="G",Round([PrePressureReading]*1000000000),IIf(Mid([PrePressureUnits],1,1)="M",Round([PrePressureReading]*1000000),[PrePressureReading]))

[NightWalker]

Bulzie,

I have been messing with this all morning and found that the first number is stored in the table as a single and the one I am converting it to is a double. For some reason the single when it gets stored adds some strange digits to the end of it. Do you know why this happens?

I figured out the round and it works for the final result and that is all that matters. I was just wondering why it does that.

Is there a way to store a number with one decimal place as a long integer?

[John_G]

Is there a way to store a number with one decimal place as a long integer?

Just multiply it by 10 first.

The reason you see strange digits past the decimal place is that with only a few exceptions, a computer, being a binary machine, cannot store decimals exactly. for example, we know what 1/10 is = it is 0.1, and we can represent that exactly. A computer can't (what it shows you on a display is not what it has internally - rounding looks after it).

So, if you are able to see your data with no formatting or rounding, you see the representation of numbers as accurately as the computer can give them to you.

[NightWalker]

John,

Great idea on the x10. I will have to remember that for future use would save space in my DB also instead of having all my numbers as doubles.
Thank you for the quick explanation of the errors of the storing of the decimals.

Thank you all very much!