Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 340
  • Last Modified:

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.
0
nplanek
Asked:
nplanek
  • 3
  • 2
  • 2
  • +1
1 Solution
 
JAMcDoCommented:
Create a query based on the following SQL statement.

SELECT TblTrades.ID, TblTrades.Laborer, IIf([Laborer]=True,"Laborer"," ") AS txtLaborer, TblTrades.Carpenter, IIf([Carpenter]=True,"Carpenter"," ") AS txtCarpenter, TblTrades.Other
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.
0
 
Rey Obrero (Capricorn1)Commented:


select IIf([laborer]=-1,"Laborer",iif([carpenter]=-1,"Carpenter",[Other])) as Trade
from  TableX
0
 
Andrew_WebsterCommented:
Cap's nearly got it, but the parentheses look wonky.  Here's my attempt!

SELECT ((Iif([laborer]=True, "Laborer", Null) & vbCrLf) & (Iif([carpenter]=True,"Carpenter", Null) & vbCrLf) & Nz([Other],"") As Trade 
FROM tblMyTableName

Open in new window


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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
nplanekAuthor Commented:
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,"Carpenter", Null) & vbCrLf) & Nz([Other],"") As Trade,
Information.Community, Information.Local, Information.Comments
FROM Information;

It's telling me my syntax is incorrect.
0
 
Rey Obrero (Capricorn1)Commented:
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,Chr(13) & Chr(10) & [Other]) AS Trade,
Information.Community, Information.Local, Information.Comments
FROM Information;
0
 
Andrew_WebsterCommented:
Here you go.  It needed two tweaks: 1. a missing bracket before "As Trade" and 2. I was thinking in VBA so used vbCrLf instead of Chr$(10) & Chr$(13) - they both mean "new line".

SELECT 
    Information.FirstName,  
    Information.LastName,  
    Information.AppDate,  
    Information.Address,  
    Information.Ward,  
    Information.City,  
    Information.State,  
    Information.Zip,  
    Information.Phone,  
    ((IIf([laborer]=True,"Laborer",Null) & Chr$(10) & Chr$(13)) & (IIf([carpenter]=True,"Carpenter",Null) & Chr$(10) & Chr$(13)) & Nz([Other],"")) AS Trade,  
    Information.Community,  
    Information.Local,  
    Information.Comments
FROM  
    Information;

Open in new window

0
 
nplanekAuthor Commented:
Love you experts!  Thanks!
0
 
Rey Obrero (Capricorn1)Commented:
nplanek,

looks like you ignored the earlier posted comment at http:#a35037823
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now