Switch is generating the wrong results

[lonelykeyboard]

I try using this below mentioned switch in code and for whatever reason, the switch operates but generates the next answer in the switch instead of the correct one.



For instance:

[days oustanding] = 21 is generating 2
instead of the desired 1

and

[days oustanding] =168 is generating nothing
instead of the desired 4



I do not believe I have the operators incorrect and the context I'm using this in is an update query.

Thank you for any help in advance,


Code:

Switch([days outstanding]>-1 
        And [days outstanding]<30,1,
    [days outstanding]>30
         And [days outstanding]<60,2,
    [days outstanding]>60
         And [days outstanding]<90,3,
    True,4)

[ADezii]

Try:

Switch((intDaysOut > -1 And intDaysOut < 30), 1, (intDaysOut >= 30 And intDaysOut < 60), 2, _
       (intDaysOut >= 60 And intDaysOut < 90), 3, (intDaysOut >= 90), 4)

[lonelykeyboard]

Ohh sorry my apologies I did not specify that

[days outstanding] is a calculated table field which generates the DateDiff of two user entered fields.

[lonelykeyboard]

Also, the code you provided gave a runtime error 3464.

[Oralloy]

lonelykeyboard,

Your Switchlooks like this, correct?

'' formatted for legibility
Switch([days outstanding]>-1 And [days outstanding]<30,1,
       [days outstanding]>30 And [days outstanding]<60,2,
       [days outstanding]>60 And [days outstanding]<90,3,
       True,4)

For simplicity, you might recode as follows, as Switch returns the value associated with the first True expression:

'' formatted for legibility
'' simplified condition cascade
'' returns Null for invalid [days outstanding]
Switch(([days outstanding] <  0), Null,
       ([days outstanding] < 30), 1,
       ([days outstanding] < 60), 2,
       ([days outstanding] < 90), 3,
       True,                      4)

[Oralloy]

lonelykeyboard,

I noticed that you have what is a misspelling in your question:

1) in the examples, you use days oustanding

2) in your switch, you use days outstanding

Is this just a typo, or possibly a bug in your code?

[lonelykeyboard]

It was a type sorry. As you can see my post is riddled with them :(

the correct table property name is days outstanding
[days oustanding]

I'm coding this into a query in order to run an "auto" update when a specific form loads.

The code I have place in the query now in the "Update To:" field is as follows:

Switch ([days outstanding] < 30,1,
[days outstanding]>30 And [days outstanding]<60,2,
[days outstanding]>60 And [days outstanding]<90,3,
True,4)

Hopefully this is a little easier to read

I have slightly revised it (because negative days should not take place in my tables)

[Oralloy]

lonelykeyboard,

In post #7 I see the same typo, I will assume that it was unintentional.

You do not need to make each test a conjunction, it just makes your code unnecessarily complex, and leaves ugly holes in the results.

For example, when [days outstanding] = 30, your code will return 4, not 2.

If you read my comment, you can look here and see that Switch takes the first true expression from the list. This means that the lower-bounds are handled by the "preceeding " options in the list.

Unfortunately I don't have Access on this computer, but please try this:

Switch(([days outstanding] < 30), 1,
       ([days outstanding] < 60), 2,
       ([days outstanding] < 90), 3,
       True,                      4)

Pardon my negative days option in my first suggestion, I tend to be a self defensive coder, which is wasteful.

Regards,
Oralloy

[lonelykeyboard]

Oralloy,

The code you provided does the same thing that my original code does. I have created the following examples:

[days outstanding] = 57 ---> The returned value should be 2,

[days outstanding] = 21 ---> The returned value should be 1

However for the first example I am getting 3 as a returned value and I am getting 2 as a returned value for the second example.

I am confused as to why the code is not performing its work properly.

[lonelykeyboard]

Also, thank you for correctly clearing up my code. I am so accustomed to using the IIF function.

[Oralloy]

lonelykeyboard,

First off, thanks for your patience with my lack of Access here.

Can we try something a little different? We're both smart, and it is obvious that the code is behaving in what appears to be an irrational maner.

Will you please post the code of interest, including the VBA and the embedded SQL. I agree with you that something is not adding up correctly, and we need to take a moment to step back and get a better perspective.

Thanks,
Oralloy

[Oralloy]

lonelykeyboard,

No worries on the clean-up. You can use a similar mechanism under IIF, as well. It just takes a few moments of thought.

--Oralloy

[lonelykeyboard]

I just typed this whole thing out and the backspace button sent me backa page so forgive the timeliness issue.

Let me explain the whole sequence of events I am trying to code.

I have an application which is launched when access opens, we will call is App 1

App 1 has the following code attached to it in the on load event

Private Sub Form_Load()
DoCmd.OpenQuery ("Update Outstanding Project")
DoCmd.OpenQuery ("Update Project Priority")
DoCmd.SetWarnings False
End Sub

So, the first update query --> "Update outstanding Project" is the update query which will compile the [days outstanding] value which I am trying to calculate in the second update query

So, after the "Update outstanding Project" query runs, I will have a current value based on todays date and the date the project was received in order to evaluate its priority.

"Update Outstanding Project" Query

SQL:

UPDATE [Project Received] SET [Project Received].[Days Outstanding] = Date()-[Project Received].[Construction complete];

VBA:

Date()-[Project Received].[Construction complete]

Now this will tabulate the [days outstanding] field and will que my next query to do its work...

"Update Project Priority" Query

SQL:

UPDATE [Project Received] SET [Project Received].[Priority Upon Receiving] = Switch(([days outstanding]<30),1,([days outstanding]<60),2,([days outstanding]<90),3,True,4);

VBA:

Switch(([days outstanding]<30),1,([days outstanding]<60),2,([days outstanding]<90),3,True,4)

I hope this helps clarify everything...

[Oralloy]

lonelykeyboard,

I'm sorry you got screwed out of your work by an inadvertent keypress. To steal a phrase, "I feel your pain..."

And yes, it does help clarify.

That said, I trust that your update query "Update Outstanding Project" works correctly in the absence of the priority calculation.

Is there a reason that you are setting warnings to False? I would think that you want to see them while you are developing.

Just as a sanity test, can we modify your "Update Outstanding Project" to clear out the priority field:

UPDATE [Project Received]
  SET [Days Outstanding] = (Date() - [Construction complete]),
      [Priority Upon Receiving] = -666;

I just want to force a completely invalid set of values at the outset, so that there is no question of stale values at the end of the test.

To test, disable the call to the "Update Project Priority" query and load the form. Then, go in and inspect the table by hand - obviously all of the
[days outstanding] should be correctly computed, and the [Priority Upon Receiving] column should be forced.

Then, just to be silly, let's modify the "Update Project Priority" query to verify that we are actually doing the right thing.

UPDATE [Project Received]
  SET [Priority Upon Receiving] = Switch(True,777);

Test by uncommenting the call to the query. At the end of the test, the column should have all 777 values.

Luck!
Oralloy

[Oralloy]

lonelykeyboard,

Sorry that took so long, I'm being rather pedantic in our discussion, as this has me a bit frustrated, and I want us to be successful.

--Oralloy