Switch help

[GoodLife22]

Error 2: Switch([Test_MR]<#1/1/2010#,'FAIL')

How can I change this to say:

Switch([Test_MR]<#1/1/2010# OR is Null,'FAIL')

I cannot find the correct way to add the OR IS NULL clause into the loop.

 

[SOS]

Switch(IsNull([Test_MR]) OR [Test_MR]<#1/1/2010#,'FAIL')

 

[SteveSchapel]

Alternatively...
Switch(Nz([Test_MR],0)<#1/1/2010#,"FAIL")

 

[GoodLife22]

OK what if I want to ask the same thing about about two columns? I want to show "FAIL" if

[Test_MR] is either null or older than 1/1/2010
~OR~
[Test_MO] is either null or older than 1/1/2010

Is this possible? Right now I made two different columns for this which which looks a little sloppy.

 

[SOS]

OK what if I want to ask the same thing about about two columns? I want to show "FAIL" if

[Test_MR] is either null or older than 1/1/2010
~OR~
[Test_MO] is either null or older than 1/1/2010

Is this possible? Right now I made two different columns for this which which looks a little sloppy.

Either of these should work.

Switch(Nz([Test_MR],0)<#1/1/2010#,"FAIL", Nz([Text_MO],0)<#1/1/2010#, "FAIL")

or

Switch((Nz([Test_MR],0)<#1/1/2010#) OR (Nz([Test_MO],0)<#1/1/2010#) ,"FAIL")

 

[GoodLife22]

That worked great. Thank you Sir. YOu have been a big help SOS

 

[SOS]

Glad that worked for you.

 

[GoodLife22]

OK need help one more time.

Status: Switch(Nz([HireDate],0)<#1/1/2009#,"F",Nz([ReHireDate],0)<#1/1/2009#,"F")

Right now this code will show me an "F" in the status field if the employee [HireDate] or [ReHireDate] is null or older than 1/1/2009.

I want to make a few changes that will make this even more hard to code. I am not sure what to do.

1.) I want Status to show a "P" for anyone who does NOT get the "F" listed above.

2.) Instead of saying "<#1/1/2009#" I really want to say "anything older than the first day of the previous year"

If I leave the query the way it is now I would have to go update the query at the beginning of next year. I am sure I would forget to do that.

 

[GoodLife22]

Would an IIf statement be a better option?

Test4: IIf((Nz([HireDate],0)<#1/1/2009#) And (Nz([ReHireDate],0)<#1/1/2009#),"FAIL","PASS")

 

[boblarson]

You can still use your Switch function:

Status: Switch(Nz([HireDate],0) < DateSerial(Year(Date())-1, 1, 1),"F",Nz([ReHireDate],0) < DateSerial(Year(Date())-1, 1, 1),"F", Nz([HireDate],0) >= DateSerial(Year(Date())-1, 1, 1),"P",Nz([ReHireDate],0) >= DateSerial(Year(Date())-1, 1, 1),"P" )

 

[GoodLife22]

I copied exactly as you have it and get an error

"the expression you entered has a function containing the wrong number of arguments"

when I hit OK it will highlight the last character bracket in this string:

Status: Switch2(Nz([HireDate],0) < DateSerial(Year(Date()-1, 1, 1)

 

[boblarson]

You may have grabbed that before I could make my corrections. I missed all of the ending parens after:

Year(Date() - 1

should be

Year(Date()) - 1

 

[GoodLife22]

O I see. I am a little too quick. sorry man.

OK I tried the new code and it worked great. thanks. It is perfect. I will do a full audit right now. Thanks. again.

 

[boblarson]

Glad to be able to help.