Multiple If Then Query Issues

[sparklegrrl]

Good morning,

I'm having trouble with what I think should be a simple query but I'm pulling my hair out. All I need is employees by month.

January for example. Shouldn't I be able to query employees who have a null term date and hire date prior to 1/31/20?

Thanks in advance!

 

[jdraw]

Shouldn't I be able to query employees who have a null term date and hire date prior to 1/31/20?

Please provide a little more context. What have you tried? Result?

 

[sparklegrrl]

JAN20: IIf([TERMDATE] Is Null And [HIREDATE]<1/31/2020,1,0)

Every line is coming up 0?

 

[June7]

Try using # delimiters:

JAN20: IIf([TERMDATE] Is Null And [HIREDATE]<#1/31/2020#,1,0)

 

[jdraw]

Try
JAN20: IIf(IsNull([TERMDATE]) AND [HIREDATE]<#1/31/2020#,1,0)

 

[sparklegrrl]

Seems to be working! TY!

 

[June7]

IsNull() is a VBA function and adds a little processing overhead to SQL. IS NULL is SQL and doesn't require call to VBA. Review http://allenbrowne.com/QueryPerfIssue.html

 

[plog]

Seems to be working! TY!

Is it? Put some test data in and see if someone who was hired 1/1/2020 and terminated 3/3/2020 shows up correctly.

 

[plog]

Thinking about it more, your entire approach is probably incorrect. This hard coding dates method is fine if you want to run this report just once. My guess is it will be run monthly and will be run in future years as well. If so, you need to make it so that this thing works any date you run it without having to edit all the calculations.

Will this be run in 2021?

 

[The_Doc_Man]

Shouldn't I be able to query employees who have a null term date and hire date prior to 1/31/20?

IIF shouldn't even be a part of this.

SELECT * FROM employees WHERE ( NZ(TermDate,0) = 0 ) AND ( HireDate < #31-Jan-2020# ) ;

 

[June7]

Good point. But won't address possible logic flaw identified by Plog. OP needs to clarify what they want to do with terminations of Jan hires. And for that matter, any month. Is goal to list employees by their status in January or current status?

And again, Nz() is a VBA function and doesn't really change what Is Null was doing.

 

[The_Doc_Man]

The question (which isn't clarified by what I saw) is whether the value was truly null or whether it was 0. It would depend on how the date had been set up when the record was created, so for me the NZ is a belt-and-suspenders approach. But I agree that IsNull() works in that context.

 

[June7]

Not IsNull(), that's still a VBA function, Is Null is SQL.

 

[The_Doc_Man]

Read the other part of my same comment. If the date value was 0 because of the way the empty value was processed, IS NULL won't work.