Ooh...I like that...

Post #91,927 by bepatient 3/26/03 4:15:29 PM Reply	Ooh...I like that... ...gonna have to use it :-) If you push something hard enough, it will fall over. _{Fudd's First Law of Opposition} [link\|mailto:bepatient@aol.com\|BePatient]
Post #91,942 by jbrabeck 3/26/03 4:42:38 PM Reply	Guess I aint as bad as I thought! and you're both welcome. Post any Access/Paradox questions. Need the challenges to keep my on my toes. [link\|mailto:jbrabeck@attbi.com\|Joe] Paradox/Access/Basic(Not VB)/Cobol/???
Post #91,981 by mmoffitt 3/26/03 7:56:15 PM Reply	Been a while since I used Access, but. Isn't there a "CASE" statement equivalent that could be used? (The work is left as an exercise ;-) bcnu, Mikem Osama bin Laden's brother could fly in US airspace 9/15/01, but I had to wait for FBI and CIA background checks, 'nuff said?
Post #91,997 by orion 3/26/03 8:31:18 PM 3/26/03 8:32:46 PM Reply	Case statement You'd have to create your own function for it: Function ReturnCase(IntNum as Integer) as String Dim StrText as String StrText = "" Select IntNum Case 1 StrText = "Yes" Case 2 StrText = "No" Case 9 StrText = "Unknown" End Select Return StrText End Function Pardon the text formatting, I forgot the codes to use to tell the forum it is source code. [link\|http://msdn.microsoft.com/library/en-us/vbcn7/html/vaconUsingSelectCase.asp\|http://msdn.microsof...ingSelectCase.asp] "Bill gates cannot guarantee Windows, so how are you going to guarantee my safety?" -John Crichton to the Emperor of the Scarrans on [link\|http://www.farscape.com\|FarScape] Edited by orion March 26, 2003, 08:32:46 PM EST Expand All History
Post #91,999 by mmoffitt 3/26/03 8:35:00 PM Reply	Not that one. It's like CASE(fldname, value1, "return1", value2, "return2","<default>") or something like that ;-) bcnu, Mikem Osama bin Laden's brother could fly in US airspace 9/15/01, but I had to wait for FBI and CIA background checks, 'nuff said?
Post #92,025 by ChrisR 3/26/03 9:03:39 PM Reply	SQL construct Access has some SQL, but the specific statement is an embedded formula - not a query per se. The SQL construct would be something like \n(CASE ([Field Name]) \n WHEN 1 THEN 'Yes'\n WHEN 2 THEN 'No'\n ELSE 'Unknown'\n END) MS used to have a standard function of Iif that did what the Switch statement in the previous post did. Not sure when they changed the name, nor why they changed it?
Post #92,026 by tseliot 3/26/03 9:06:14 PM Reply	Still there but only for two options Many fears are born of stupidity and ignorance - Which you should be feeding with rumour and generalisation. BOfH, 2002 "Episode" 10
Post #92,028 by mmoffitt 3/26/03 9:06:36 PM Reply	Yeah, I was thinking Access had a TSQL-like Case statement. I'm almost sure they did, but the last time I wrote an Access report was 1998 with Access 97. I don't have a copy installed (still doesn't run on Linux :-) but I'm almost sure there used to be a CASE access statement. And you're right, worked similar to Switch. Could be Switch (like in C) that I was thinking about, but I could swear it was CASE. Okay, now I'm gonna have to look it up. bcnu, Mikem Osama bin Laden's brother could fly in US airspace 9/15/01, but I had to wait for FBI and CIA background checks, 'nuff said?
Post #92,042 by ChrisR 3/26/03 9:16:09 PM Reply	IIRC the queries do support it. But the Switch() function above is a VBScript function.
Post #92,043 by jbrabeck 3/26/03 9:16:46 PM Reply	Yes Access has a Case statement I'm at home now and don't have Access loaded here. Work requirement so I taught myself how to get around in it last summer. I've used the Case statement on events on forms. Paradox still has the IIF([field]=x,true,false). To make it work as requested would have been IIF([Field]=1,"Yes",IIF(Field]=2,"No",IIF([Field]=9,"Unknown","Bad Value"))) I may have seen the IIF still in Access, but I'm not sure. The online help isn't a very good way to learn. Knowing OPAL helped cuz I knew that it could be done... Oh well, still have my Paradox clients to program in a decent language. [link\|mailto:jbrabeck@attbi.com\|Joe]
Post #92,050 by mmoffitt 3/26/03 9:25:51 PM Reply	Yeah, I remember IIF in Access. But it gets hard as hell to read if you're doing more than 2 or 3 checks. IIRC, that's when I looked up the CASE Access statement. bcnu, Mikem Osama bin Laden's brother could fly in US airspace 9/15/01, but I had to wait for FBI and CIA background checks, 'nuff said?
Post #92,119 by Meerkat 3/27/03 3:00:36 AM Reply	DECODE would do it, if Access has it, that is. I'm posting from home, not work, so I can't check. I've used Decode on Oracle, and RedBrick, so it's not some freaky single-vendor proprietary thing. Maybe it's some freaky two-vendor proprietary thing :) John. Busy lad.
Post #92,136 by jbrabeck 3/27/03 8:20:11 AM Reply	IIF, Case and Switch. No Decode Back in the office. Just checked. [link\|mailto:jbrabeck@attbi.com\|Joe]
Post #92,031 by tseliot 3/26/03 9:08:02 PM Reply	Yes you are. Just kidding. But I would put those values in a table for fear that I would need to change them someday. Much quicker to change table entries than a soon-to-be-massive switch statement. Many fears are born of stupidity and ignorance - Which you should be feeding with rumour and generalisation. BOfH, 2002 "Episode" 10
Post #92,048 by jbrabeck 3/26/03 9:21:29 PM Reply	Agree, but... The way I understood the problem is that there are multiple fields that needed to be converted. Would have had to load the with many aliases to get the proper linking. I have a table of individuals. In the positions table I have, currently only, 10 different positions. To list the "Who's responsible" list, I have to link the positions table to the names table 10 times. Can't have multiple links to the same table. And if I understood correctly, field1, field2, ... fieldn all have values to be converted to Y/N/U. [link\|mailto:jbrabeck@attbi.com\|Joe]
Post #92,556 by tseliot 3/28/03 4:34:34 PM Reply	Then you have one of four choices If there are multiple fields per query, you have four choices: 1) Use Switch. This works until you get too many fields, or too many values, or change values often. Any one of those conditions makes maintenance a bear. 2) Use subqueries in the field list. This works if the number of fields stays small; the number of values can be huge and change often. 3) Store the value as text instead of a number. This increases DB size but lowers query processing time. Why have a lookup table if it's not normalizing anything? 4) Write a VBA function and call it within the SQL. Not portable. Pick one based on your environment. Many fears are born of stupidity and ignorance - Which you should be feeding with rumour and generalisation. BOfH, 2002 "Episode" 10

Welcome to IWETHEY!