Calculated Field

[Guest]

I need to see if I can produce this field for each record in my database:

If PriceC is equal to zero, take PriceD

But if PriceC is not equal to zero but is less than .15 take PriceC

But if PriceB is not equal to zero and PriceC is not equal to zero, take the
median of PriceB and PriceC. Rounddown this calculation to the nearest tenth.

Finally, if none of these conditions can be satisfied, record a zero.

Thank you in advance.

 

[John Vinson]

I need to see if I can produce this field for each record in my database:

If PriceC is equal to zero, take PriceD

But if PriceC is not equal to zero but is less than .15 take PriceC

But if PriceB is not equal to zero and PriceC is not equal to zero, take the
median of PriceB and PriceC. Rounddown this calculation to the nearest tenth.

Finally, if none of these conditions can be satisfied, record a zero.

Thank you in advance.

The Switch() function should work for you here. It takes arguments in
pairs, and reads them left to right. When it first encounters a TRUE
value in the first memeber of a pair, it returns the second of that
pair and quits. So:

ThePrice: Switch([PriceC] = 0, [PriceD],
[PriceC] < 0.15, [PriceC],
[PriceB] <> 0 AND [PriceC] <> 0, MyMedian([PriceB], [PriceC]),
True, 0)

You'll need to write a VBA function to return the median - I have no
idea what the median of two values might be, or what it is that you
want to median!

John W. Vinson[MVP]

 

[MGFoster]

I need to see if I can produce this field for each record in my database:

If PriceC is equal to zero, take PriceD

But if PriceC is not equal to zero but is less than .15 take PriceC

But if PriceB is not equal to zero and PriceC is not equal to zero, take the
median of PriceB and PriceC. Rounddown this calculation to the nearest tenth.

Finally, if none of these conditions can be satisfied, record a zero.

Thank you in advance.

The Switch() function should work for you here. It takes arguments in
pairs, and reads them left to right. When it first encounters a TRUE
value in the first memeber of a pair, it returns the second of that
pair and quits. So:

ThePrice: Switch([PriceC] = 0, [PriceD],
[PriceC] < 0.15, [PriceC],
[PriceB] <> 0 AND [PriceC] <> 0, MyMedian([PriceB], [PriceC]),
True, 0)

You'll need to write a VBA function to return the median - I have no
idea what the median of two values might be, or what it is that you
want to median!

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The median can be found in SQL like this (a copy from the net), just
change Splunge and Blat to your column and table names, respectively:

-- From: http://www.aspfaq.com/show.asp?id=2506

-- Find the median value

-- For odd Count(*)

SELECT TOP 1 splunge FROM
(
SELECT TOP 50 PERCENT splunge
FROM blat ORDER BY splunge
) sub
ORDER BY splunge DESC



-- For even Count(*). Can be used for odd Count(*) also.

SELECT AVG(splunge) FROM
(
SELECT splunge FROM (
SELECT TOP 1 splunge = splunge * 1.0 FROM
(
SELECT TOP 50 PERCENT splunge
FROM blat ORDER BY splunge
) sub_a
ORDER BY 1 DESC
) sub_1
UNION ALL
SELECT splunge FROM (
SELECT TOP 1 splunge = splunge * 1.0 FROM
(
SELECT TOP 50 PERCENT splunge
FROM blat ORDER BY splunge DESC
) sub_b
ORDER BY 1
) sub_2
) median

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRFvuXYechKqOuFEgEQIpdACgrB4HestzXXFwqJj6nB2mW5zUvn0Amwcs
HNmgSVCtrgZhNHmsIL7k0RLa
=4SfX
-----END PGP SIGNATURE-----