If-And-Then?

CluelessInSeattle

New member
Local time
Today, 10:09
Joined
Apr 5, 2020
Messages
4
Hello all,

I am trying to create a query that shows the "first name" and "last name" of the choir member only if the check out "date out" is not null -and- the check out "date in" is null. Is there a way I can do this?

1586125276721.png
 
It's simple: type Is Not Null for your criteria in DateOut, and Is Null in the criteria for DateIn.
 
Hi. Maybe something like:

SELECT FirstName, LastName
FROM TableName
WHERE DateOut Is Not Null AND DateIn Is Null
 
Hi Zeroaccess, thank you for your reply. It is not quite that simple, although what I actually need might be, I do not know! I need the query to return as it does below, but I need the "First Name" and "Last Name" columns for the three tenor copies to be blank since the "DateIn" column for those three is not null. If I simply type "Is Not Null" for DateOut and Is Null for DateIn, then the only record that will return is the one for Soprano Copy 1.

1586126987797.png
 
But that would be the correct result, right? Or are you saying you prefer this to look more like a spreadsheet?
 
hi Clueless,

Yes, theDBguy gave you SQL -- but apparently that isn't what you want either. Try putting this expression on the grid of the query in the Field cell:

Code:
FirstName: iif( (Not IsNull(DateOut) ) AND ( IsNull(DateIn) ), [First Name], "")

iif is a special form of IF called immediate If. The first part is the condition to test. The second part is the value if true. The third part is the value if false.
 
Last edited:
ps, Clueless

If the expression I gave you works the way you want, use the same logic for Last name. What comes before the colon is the name of the calculated field. Note that it must be different than any field name in the record source. Since your first name has a space (which isn't a good idea to use), I simply took it out.
 
Hi Crystal, thank you for your reply - that is exactly what I needed and I was able to do it for Last Name as well as you said. Thank you so much!!
 
you're welcome, Clueless ~ happy to help

Once you're here longer, you'll have to change your username since you won't be clueless anymore!
 

Users who are viewing this thread

Back
Top Bottom