English to Metric Coversion in two seperate fields

[sonofroy]

Hi I have a table with this in it.

Feet Meters
1 456.324
2 102.456
3 233.167
4 678.090

I would like to create an update query that will automatically fill the
empty cells by using the conversion factor from feet to meters and vice
versa. Can someone lead me into the right direction here?

Thanks

 

[Douglas J Steele]

UPDATE MyTable
SET Feet = Nz([Feet], [Meters] * 3.2808399),
Meters = Nz([Meters], [Feet] * 0.3049)

 

[sonofroy]

It appears to have worked. What is the Nz function? I have never used it?

Thanks a bunch

UPDATE MyTable
SET Feet = Nz([Feet], [Meters] * 3.2808399),
Meters = Nz([Meters], [Feet] * 0.3049)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi I have a table with this in it.

Feet Meters
1 456.324
2 102.456
3 233.167
4 678.090

I would like to create an update query that will automatically fill the
empty cells by using the conversion factor from feet to meters and vice
versa. Can someone lead me into the right direction here?

Thanks

 

[Luke Dalessandro]

Not Zero: basically returns te first argument if it's not zero, and the
second if the first is zero (or nothing/null). It's an IIf function
shortcut.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaac11/html/acfctNZ_HV05186465.asp

It appears to have worked. What is the Nz function? I have never used it?

Thanks a bunch

:

UPDATE MyTable
SET Feet = Nz([Feet], [Meters] * 3.2808399),
Meters = Nz([Meters], [Feet] * 0.3049)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi I have a table with this in it.

Feet Meters
1 456.324
2 102.456
3 233.167
4 678.090

I would like to create an update query that will automatically fill the
empty cells by using the conversion factor from feet to meters and vice
versa. Can someone lead me into the right direction here?

Thanks

 

[Dirk Goldgar]

Not Zero: basically returns te first argument if it's not zero, and
the second if the first is zero (or nothing/null). It's an IIf
function shortcut.

Actually, a better gloss would be "Null to Zero", because it's commonly
used to transform a Null value to 0. The function returns the first
argument, as is, if it is not Null. If the first argument is Null, the
second argument is returned; but if no second argument is supplied, the
function returns either 0 or a zero-length string (""), depending on the
data type of the first argument.

 

[Luke Dalessandro]

Absolutely true

Actually, a better gloss would be "Null to Zero", because it's commonly
used to transform a Null value to 0. The function returns the first
argument, as is, if it is not Null. If the first argument is Null, the
second argument is returned; but if no second argument is supplied, the
function returns either 0 or a zero-length string (""), depending on the
data type of the first argument.

 

[Douglas J Steele]

Hi I have a table with this in it.

Feet Meters
1 456.324
2 102.456
3 233.167
4 678.090

I would like to create an update query that will automatically fill
the empty cells by using the conversion factor from feet to meters
and vice versa. Can someone lead me into the right direction here?

Thanks

UPDATE MyTable
SET Feet = Nz([Feet], [Meters] * 3.2808399),
Meters = Nz([Meters], [Feet] * 0.3049)

I would suggest that you consider only have one field meters or feet.
Allow the option of entering either on the form and if then enter the
non-stored type convert it and store it. Then display both if needed
converting them as you display them. No need to store both and Access likes
to compute better than it likes to store.

Good point, Joseph. I should have given that advice, as opposed to simply
answering the specific question.