nplanek
asked on
If Statement in MS Access
Using MS Access 97 how do you create a query that would produce a word instead of true or false (I will be using this to populate an Access report. I have three fields (laborer, carpenter, other). Laborer and Carpenter are yes/no fields, and other is text. I'd like to create a report showing a field for TRADE and under that field display Laborer if the laborer field is a yes, or show Carpenter if the carpenter field is a yes, or display what is in the other field if there is something in it. It should also display any combination (example: If Carpenter is a yes and Laborer is a yes and there is something in other display
Carpenter
Laborer
Blah Blah Blah.
Carpenter
Laborer
Blah Blah Blah.
select IIf([laborer]=-1,"Laborer"
from TableX
Cap's nearly got it, but the parentheses look wonky. Here's my attempt!
Each "Iif" statement will return either the word with a carriage return/linefeed or Null. There's a trick called "Null concatenation where "string" + Null returns Null, but "string" & Null returns "string". I've used that to concatenate a newline with each term if it's there. i.e. When [laborer] is True, then Iif([laborer],"Laborer", Null) will return the string "Laborer". Concatenate that with an "&" to vbCrLf gives your newline. But if it's False, then it returns a Null. Concatenate the Null with the next expression using "&" and you just get the next expression.
It's a handy trick for odd conditional concatenation.
SELECT ((Iif([laborer]=True, "Laborer", Null) & vbCrLf) & (Iif([carpenter]=True,"Carpenter", Null) & vbCrLf) & Nz([Other],"") As Trade
FROM tblMyTableName
Each "Iif" statement will return either the word with a carriage return/linefeed or Null. There's a trick called "Null concatenation where "string" + Null returns Null, but "string" & Null returns "string". I've used that to concatenate a newline with each term if it's there. i.e. When [laborer] is True, then Iif([laborer],"Laborer", Null) will return the string "Laborer". Concatenate that with an "&" to vbCrLf gives your newline. But if it's False, then it returns a Null. Concatenate the Null with the next expression using "&" and you just get the next expression.
It's a handy trick for odd conditional concatenation.
ASKER
This is the entire SQL Statement:
SELECT Information.FirstName, Information.LastName, Information.AppDate, Information.Address, Information.Ward, Information.City, Information.State, Information.Zip, Information.Phone,
((Iif([laborer]=True, "Laborer", Null) & vbCrLf) & (Iif([carpenter]=True,"Car penter", Null) & vbCrLf) & Nz([Other],"") As Trade,
Information.Community, Information.Local, Information.Comments
FROM Information;
It's telling me my syntax is incorrect.
SELECT Information.FirstName, Information.LastName, Information.AppDate, Information.Address, Information.Ward, Information.City, Information.State, Information.Zip, Information.Phone,
((Iif([laborer]=True, "Laborer", Null) & vbCrLf) & (Iif([carpenter]=True,"Car
Information.Community, Information.Local, Information.Comments
FROM Information;
It's telling me my syntax is incorrect.
test this
SELECT Information.FirstName, Information.LastName, Information.AppDate, Information.Address, Information.Ward, Information.City, Information.State, Information.Zip, Information.Phone,
IIf([laborer]=-1,"Laborer" ,Null) & IIf([Carpenter]=-1,Chr(13) & Chr(10) & "Carpenter",Null) & IIf(IsNull([Other]),Null,C hr(13) & Chr(10) & [Other]) AS Trade,
Information.Community, Information.Local, Information.Comments
FROM Information;
SELECT Information.FirstName, Information.LastName, Information.AppDate, Information.Address, Information.Ward, Information.City, Information.State, Information.Zip, Information.Phone,
IIf([laborer]=-1,"Laborer"
Information.Community, Information.Local, Information.Comments
FROM Information;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Love you experts! Thanks!
SELECT TblTrades.ID, TblTrades.Laborer, IIf([Laborer]=True,"Labore
FROM TblTrades;
My table is called TblTrades with fields ID (Autonumber), Laborer (Yes/No), Carpenter (Yes/No) and Other (Text).
To do this, open a blank Query in SQL view and paste the SELECT . . . statement (including the semi-colon) into the SQL window.
If your table and/or field names differ from mine, change them in the SQL statement before changing views.
For the report only use the txtLaborer, txtCarpenter and Other fields.
J.