F to C conversion formula?

[don911no]

In Access 2002, I have a field [Temp F] with the temperature in Fahrenheit that I want to convert in separate field in Celsius using an update query. How would I write this formula in the 'Update To" field of a query?



The formula for Fahrenheit to Celsius conversion is either (Temperature in degrees Fahrenheit - 32) divided by 1.8 or (Temperature in degrees Fahrenheit - 32) * 5/9.

I can get the first part [Temp F]-"32" to work, but I can't figure out how to then divide or multiply the result with the values above.

[Welshgasman]

In Access 2002, I have a field [Temp F] with the temperature in Fahrenheit that I want to convert in separate field in Celsius using an update query. How would I write this formula in the 'Update To" field of a query?

The formula for Fahrenheit to Celsius conversion is either (Temperature in degrees Fahrenheit - 32) divided by 1.8 or (Temperature in degrees Fahrenheit - 32) * 5/9.

I can get the first part [Temp F]-"32" to work, but I can't figure out how to then divide or multiply the result with the values above.

Seriously?
What are you struggling with, you have the formula?

tempF = 70
tempC = (tempF-32)*5/9
? tempc
 21.1111111111111

[don911no]

I have the formula but I don't know how to write it correctly in the 'Update To' field. In other fields that I update, that take/duplicte data from other fields, I put the name of the 'from' field within brackets at the start, i.e. [Temp F] in this case. So how do I write the formula if I have to start with the data field name within brackets? Or is there another way to get data from another field without using brackets? Using (Temp F-32)*5/9 gives me an error. But [Temp F]-"32" will at least work to produce the Fahrenheit value minus 32. So I assume I have to start with [Temp F] and go from there?

[moke123]

is there another way to get data from another field without using brackets

Depending on how far along you are in development, using a proper naming convention would work.

[don911no]

Sorry, but I don't work with Access professionally or have a good knowledge about databases in general. I just use MS Access to collect personal daily data that I find interesting, as it works better for this purpose than Excel (which I'm good at ). And I'm just stuck on how to enter this formula properly to make Access do the conversion.

[Welshgasman]

As you can calculate the C from the F, you should not really store it, just store the F, and just calculate each time you need it.

Use a select until you get it correct
SELECT tblTemp.TempF, Round(([TempF]-32)*5/9,2) AS Expr1
FROM tblTemp;

Then if you absolutely have to store it

UPDATE tblTemp SET tblTemp.TempC = Round(([TempF]-32)*5/9,2);

[Micron]

What WGM said. It is seldom a good idea to store the result of calculations in a table. Your issue here is the result of using spaces in names. See
- http://access.mvps.org/access/general/gen0012.htm
- https://www.access-programmers.co.uk...d.php?t=225837

Your solution is probably ([Temp F]-32)*5/9 but I didn't test that.
EDIT - apologies, I overlooked where this was already suggested. You probably don't need round though if decimals are not required and you use this instead: ([Temp F]-32)*5\9

[don911no]

Thank you so much to both of you for your help! Round(([TempF]-32)*5/9,2) worked perfectly since I wanted 2 decimals. Also, before I ran the update query, I noticed that the data type was set to Memo, so I changed it to number as well as removed the spaces in the field names. Perhaps these two issues were the main reason it didn't work initially? Anyway, thanks again!

[Welshgasman]

The data type should likely be decimal as well?

[Micron]

Initially it didn't work for various reasons. You had space in field name so must be enclosed [in brackets]. When you did that, you made 32 a string ( "32" ) so also not good. Now that you have TempF you don't need brackets around it. That's the whole point of not using spaces or special characters in object names (save for perhaps underscore, which I don't usually use) and not starting an object name with a number.

[moke123]

@Micron - Many years ago I read an article stating not to use numbers at the start of an object name (I never have). It gave the reasons why. Since then I've seen many posts with numbers as the beginning of table and form names and I've looked for that article several times with no luck. Any Idea as to the why?

[Micron]

IIRC, starting a name with a number means you must enclose the name in brackets. Other than that it works. It was something uploaded here I think, by someone else so not used by me either.

[don911no]

The data type should likely be decimal as well?

I changed the Field Size from Single to Decimal (with 2 decimals), but got a warning when saving and then the decimals were removed in the table. Also, before that change, the Fahrenheit field was set to Single and 0 decimals and the Celsius field was set to Single with 2 decimals, yet, the Fahrenheit field shows 2 decimals in the table and the Celsius field 3 decimals. Seems weird.

[don911no]

Initially it didn't work for various reasons. You had space in field name so must be enclosed [in brackets]. When you did that, you made 32 a string ( "32" ) so also not good. Now that you have TempF you don't need brackets around it. That's the whole point of not using spaces or special characters in object names (save for perhaps underscore, which I don't usually use) and not starting an object name with a number.

The quotation marks around 32 were added by Access once I clicked save. To learn, I tried to change the now working formula to remove the brackets (TempF-32)*5/9 but that didn't work. I then tried to change it to the second F to C formula instead, but I could not get that to work either. That formula is F-32/1.8 instead of F-32*5/9. But Round(([TempF]-32)*1.8,2) did not work. This is hard! If you have time to explain to me why that is, that would be great. If not, that's OK too since I already have a working formula now.

[Micron]

Sorry, you're confusing me with F-32 vs TempF-32 and "didn't work" tells us nothing. I'm quite literal, and sometimes it's an asset and sometimes it's not. Maybe didn't work" has something to do with the fact that 5/9 (rounded) is .56 and not 1.8 if you're going from F to C.