Post #91,836
3/26/03 12:36:51 PM
|
'nother Access question.
Is it possible to format output in, say, a Report, that replaces numeric values with their text equivalents? That is, if FieldA has values 1, 2, or 9 for Yes, No, Unknown, is there a way to display the Yes, No, Unknown in a report instead of 1, 2, 9? I know I can do this with a single field by linking it in the underlying query to a table of equivalents, but that doesn't (I think) work when you have numerous fields all sharing the same values.
|
Post #91,842
3/26/03 12:52:54 PM
|
Tools->Relationships
Connect the lookup table to the other table and then use the field from the lookup table in the report.
If you push something hard enough, it will fall over. Fudd's First Law of Opposition
[link|mailto:bepatient@aol.com|BePatient]
|
Post #91,870
3/26/03 2:03:59 PM
|
Try this
Taken directly from one of my reports
=Switch([Sensitivity]=1,'N',[Sensitivity]=2,'B',[Sensitivity]=3,'S')
For you then
=Switch([Field Name]=1,'Yes',[Field Name]=2,'No',[Field Name]=9,'Unknown')
Works for Access2000
Shoot me email if you need additional help.
[link|mailto:jbrabeck@email.usps.gov|Joe]
|
Post #91,919
3/26/03 3:56:33 PM
|
Perfect! Thanks!
|
Post #91,927
3/26/03 4:15:29 PM
|
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
3/26/03 4:42:38 PM
|
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
3/26/03 7:56:15 PM
|
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
3/26/03 8:31:18 PM
3/26/03 8:32:46 PM
|
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
|
Post #91,999
3/26/03 8:35:00 PM
|
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
3/26/03 9:03:39 PM
|
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
3/26/03 9:06:14 PM
|
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
3/26/03 9:06:36 PM
|
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
3/26/03 9:16:09 PM
|
IIRC the queries do support it.
But the Switch() function above is a VBScript function.
|
Post #92,043
3/26/03 9:16:46 PM
|
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
3/26/03 9:25:51 PM
|
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
3/27/03 3:00:36 AM
|
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
3/27/03 8:20:11 AM
|
IIF, Case and Switch. No Decode
Back in the office. Just checked.
[link|mailto:jbrabeck@attbi.com|Joe]
|
Post #92,031
3/26/03 9:08:02 PM
|
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
3/26/03 9:21:29 PM
|
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
3/28/03 4:34:34 PM
|
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
|
Post #92,204
3/27/03 12:47:38 PM
|
Okay, here's another one:
I want to embed carriage returns in statements with text and field values in them. E.g., I want the output to look like this: # People at location: [Field1]
# Employees: [Field2]
# Visitors: [Field3]
I kind of have to create one field out of three in the query because otherwise there are too many for the report to handle. And the groupings make sense, too. So how might one put CR/LF in: BigField: "# People at location: " & [Field1] & " #Employees: " & [Field2] & "# Visitors: " & [Field3]
|
Post #92,205
3/27/03 12:58:28 PM
|
="A" & Chr(13) & Chr(10) & "B"
Be sure to set the "Can Grow" attribute
|
Post #92,530
3/28/03 3:59:19 PM
|
Does the Tab key have any special meaning to it?
I ask because I have a table with two field acting as the keys (Var1 and Var2). If I start a new record, type a new number into the combo box containing Var1, and then Tab to the next field (Var2 text box,which is empty) I get an error message saying that Var2 cannot contain a Null value. OTOH, if I just use the ol' mouse to click on the Var2 field, it works fine. Tabbing seems to trigger some sort of check, but there's not code telling it to do that.
It's driving me batty.
|
Post #92,781
3/29/03 8:10:39 AM
|
Check the properties
Of those fields (Table/Design) There is a setting to (Dis)allow NULL values. Set it to allow null. Should fix the problem.
Welcome to Access...
[link|mailto:jbrabeck@attbi.com|Joe]
|