Switch function giving #Error in query

[Isaac]

Hello, I have a column in a query sourced as follows:

BU Completion Status: Switch(IsNull([qryID_and_BU_CompleteStatus].[BU])=True,"N/A - Submission Not Extracted from SP Yet",IsNull([qryID_and_BU_CompleteStatus].[BU])=False,[qryID_and_BU_CompleteStatus].[Complete])

I swear this was working a few minutes ago! The only thing I CHANGED ... is, So as you can see in my screenshot, there is a source 'table' SharepointIDs_to_BusinessUnits, which is left joined to qryID_and_BU_CompleteStatus. In my Switch statement, I wanted to evaluate whether:

- nothing was returned at all from qryID_and_BU_CompleteStatus (null)
- otherwise, return a specific column from qryID_and_BU_CompleteStatus

Previously, I had SharepointIDs_to_BusinessUnits left joined to qryID_and_BU_CompleteStatus on SharepointID > SP_ID (and was testing if THAT was null on right side)
Now, I changed it to left join SharepointIDs_to_BusinessUnits to qryID_and_BU_CompleteStatus on BusinessUnitID > BU (now testing if THIS is null on right side).

I realize there's probably 3 different ways to do this, but I'm fond of Switch. Why the heck is my Switch statement having a problem with this??

PS - it's giving an #Error ONLY on the records where the switch statement should return "N/A - Submission Not Extracted from SP Yet".

 

[theDBguy]

I realize there's probably 3 different ways to do this, but I'm fond of Switch. Why the heck is my Switch statement having a problem with this??

Hi. I have no idea why your Switch() expression is not working; but you're correct, there are other ways to get the same result. For example, if you used an IIf() expression would be a bit shorter than the Switch() one.

Also, if you didn't really need to see the "N/A..." part, you may not even need an expression at all. Just select the [Complete] column, and the Null ones would be empty. If you must see the "N/A..." part, then you could try using the Nz() function.

 

[Isaac]

I'll guess I'll bite the bullet and use IIF. But, IIF anyone has any other thoughts on Switch, by all means share!
Thanks dbGuy. I do need the N/A part b/c in this case nobody (except me) would realize why it was null, that being, that I haven't extracted the detail file data from SP yet, and therefore don't know if that detail file is complete.

 

[NauticalGent]

I never have been a fan of the Swith() function. Mainly because it returns the first variant that is true and that would require me to think...

 

[CJ_London]

not sure if it is your presentation but you are saying your join is

BusinessUnitID > BU

which is a non standard join - is this a typo or what you really have?

could also try

Switch([qryID_and_BU_CompleteStatus].[BU] is null,"N/A - Submission Not Extracted from SP Yet".......

 

[Isaac]

No, sorry for any confusion. That little right facing carrot was just my way of saying joining one thing to another, sorry it was kind of confusing. It was just a standard left join