DECODE equivalent

  • Thread starter Thread starter patricia
  • Start date Start date
P

patricia

I am using Microsoft Access 2000 and want to do a group
by, but need to consolidate values in the column.
e.g. 'A' means STATE = NY or NJ; 'B' means STATE =
CT; 'C' for all other states.

Is there a function similar to Oracle's DECODE? e.g.,
SELECT DECODE(STATE, 'NY', 'A', 'NJ', 'A', 'CT', '2', '3')

Where could I find a list of valid FUNCTIONS (SQL, not
WORKSHEET). Just fishing around I find ones that work
(like STR), but cannot find any details in Microsoft
Access Help. Thank You!
 
The closest built-in Access function for your problem would be a bunch of
nested IIF calls.

For a more readable/maintainable solution, write a VBA function that returns
your letter code, given the state, and use that function in your query.

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

The Switch() function works similarily:

SELECT Switch(State In ("NY","NJ"), "A", State="CT", "B", State Not In
("NY", "NJ", "CT"), "C")

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


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

iQA/AwUBQJFoZIechKqOuFEgEQIJwwCgzgGVkUaHgueINySQcBL1LQdEyqEAoJzI
WOOY3iJ2muzeqAK2fTNPOn/5
=jkNF
-----END PGP SIGNATURE-----
 
Back
Top