If-And-Then? (1 Viewer)

CluelessInSeattle

New member
Local time
Today, 18:02
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
 

zeroaccess

Active member
Local time
Today, 17:02
Joined
Jan 30, 2020
Messages
671
It's simple: type Is Not Null for your criteria in DateOut, and Is Null in the criteria for DateIn.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:02
Joined
Oct 29, 2018
Messages
21,358
Hi. Maybe something like:

SELECT FirstName, LastName
FROM TableName
WHERE DateOut Is Not Null AND DateIn Is Null
 

CluelessInSeattle

New member
Local time
Today, 18:02
Joined
Apr 5, 2020
Messages
4
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
 

zeroaccess

Active member
Local time
Today, 17:02
Joined
Jan 30, 2020
Messages
671
But that would be the correct result, right? Or are you saying you prefer this to look more like a spreadsheet?
 

strive4peace

AWF VIP
Local time
Today, 17:02
Joined
Apr 3, 2020
Messages
1,003
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:

strive4peace

AWF VIP
Local time
Today, 17:02
Joined
Apr 3, 2020
Messages
1,003
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.
 

CluelessInSeattle

New member
Local time
Today, 18:02
Joined
Apr 5, 2020
Messages
4
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!!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:02
Joined
Oct 29, 2018
Messages
21,358
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!!
Hi. Glad to hear you got it sorted out. Good luck with your project.
 

strive4peace

AWF VIP
Local time
Today, 17:02
Joined
Apr 3, 2020
Messages
1,003
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

Top Bottom