Query and if null

[robmict]

Hello all, I am trying to build a query that will tell me
1. If my EMT's licenses will expire within the next 2 months
2. If they have all their NIMS completed
3. If any of these items are blank in my database.
I have two tables I am using
1. Contacts
2. NIMS
I have set it up so that if their licenses are expiring in the time frame it tells me yes or no, I have done this by putting the following in the field line of my query in design view.
Need EVOC Renewal: IIf([Contacts]![EVOC Last Date Taken]<(Date()-730),"YES","NO")
This tells me either yes they need evoc (Emergency Vehicle Operators Course, or no they do not, and it works fine but it if the data for the record is not entered I still get "NO" which I will then overlook the fact the EMT may never have had EVOC. I am sorry if my lingo is incorrect, and please let me know if you have any questions that will help you help me. Thank you for your time and response in advance.

[pbaldy]

Try wrapping the field reference in the Nz() function to replace Null with some date old enough to fail your test. More info on the Nz() function in Help.

[robmict]

Could you please give me an example of this, and where to put it in my query while in design view, I can't seem to get it to work even with the help menu.

[pbaldy]

Untested, but try

Need EVOC Renewal: IIf(Nz([Contacts]![EVOC Last Date Taken], #1/1/2000#)<(Date()-730),"YES","NO")

The spaces in your names are going to prove more trouble than they're worth in the long run.

[robmict]

Paul, you are my hero, so far so good, I also now understand what I was doing wrong with my Nz function. Thank you very much!!!

[pbaldy]

Happy to help, and welcome to the site by the way!