IWETHEY v. 0.3.0 | TODO
1,095 registered users | 0 active users | 0 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

New '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.
New 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]
New 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]
New Perfect! Thanks!
New 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]
New 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/???
New 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?
New 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]
Expand Edited by orion March 26, 2003, 08:32:46 PM EST
New 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?
New 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?
New 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
New 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?
New IIRC the queries do support it.
But the Switch() function above is a VBScript function.
New 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]
New 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?
New 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.
New IIF, Case and Switch. No Decode
Back in the office. Just checked.
[link|mailto:jbrabeck@attbi.com|Joe]
New 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
New 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]
New 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
New 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]


New ="A" & Chr(13) & Chr(10) & "B"
Be sure to set the "Can Grow" attribute
New 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.
New 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]
     'nother Access question. - (acagle) - (23)
         Tools->Relationships - (bepatient)
         Try this - (jbrabeck) - (17)
             Perfect! Thanks! -NT - (acagle)
             Ooh...I like that... - (bepatient) - (15)
                 Guess I aint as bad as I thought! - (jbrabeck) - (14)
                     Been a while since I used Access, but. - (mmoffitt) - (10)
                         Case statement - (orion) - (9)
                             Not that one. - (mmoffitt) - (8)
                                 SQL construct - (ChrisR) - (7)
                                     Still there but only for two options -NT - (tseliot)
                                     Yeah, I was thinking Access had a TSQL-like Case statement. - (mmoffitt) - (5)
                                         IIRC the queries do support it. - (ChrisR)
                                         Yes Access has a Case statement - (jbrabeck) - (3)
                                             Yeah, I remember IIF in Access. - (mmoffitt) - (2)
                                                 DECODE would do it, if Access has it, that is. - (Meerkat) - (1)
                                                     IIF, Case and Switch. No Decode - (jbrabeck)
                     Yes you are. - (tseliot) - (2)
                         Agree, but... - (jbrabeck) - (1)
                             Then you have one of four choices - (tseliot)
         Okay, here's another one: - (acagle) - (1)
             ="A" & Chr(13) & Chr(10) & "B" - (ChrisR)
         Does the Tab key have any special meaning to it? - (acagle) - (1)
             Check the properties - (jbrabeck)

Unintended consequences.
81 ms