Switch is generating the wrong results

**ADezii** · May 29 '13, 07:35 PM

A single Query with a Calculated Field can solve the problem. Pass to a Public Function the Received and Completed Dates which will then be evaluted and a Priority Value returned based upon the Input.

SQL Statement:

Code:

SELECT DISTINCT Table1.[Project Received], Table1.[Construction Complete], 
fCalcDaysOutstanding([Project Received],[Construction Complete]) AS Priority
FROM Table1
ORDER BY fCalcDaysOutstanding([Project Received],[Construction Complete]);

Table Values:

Code:

PK	Field1	Project Received	Construction Complete
1	 100.664	5/9/2013	        7/1/2013
4	 98.891	 5/4/2013	        6/14/2013
5	 102.344	5/12/2013	       7/27/2013
6	 101.527	5/1/2013	
7	 103.135	5/21/2013	       5/29/2013
21                                   5/17/2013
22		       12/12/2012	      4/30/2013

Results:

Code:

Project Received	Construction Complete	Priority
5/21/2013	       5/29/2013	                1
5/4/2013	        6/14/2013	                2
5/9/2013	        7/1/2013	                 2
5/12/2013	       7/27/2013	                3
12/12/2012	      4/30/2013	                4
                    5/17/2013                   99
5/1/2013		                                99

Function Definition:

Code:

Public Function fCalcDaysOutstanding(varReceived As Variant, varCompleted As Variant)
Dim intDaysDiff As Integer

'If either the Received or Completed Date is NULL then return a pre-determined Value
If IsNull(varReceived) Or IsNull(varCompleted) Then
  fCalcDaysOutstanding = 99
    Exit Function
End If

intDaysDiff = DateDiff("d", varReceived, varCompleted)

Select Case intDaysDiff
  Case Is < 30
    fCalcDaysOutstanding = 1
  Case Is < 60
    fCalcDaysOutstanding = 2
  Case Is < 90
    fCalcDaysOutstanding = 3
  Case Else
    fCalcDaysOutstanding = 4
End Select
End Function

**lonelykeyboard** · May 29 '13, 07:36 PM

Both codes do no produce a value in the priority upon receiving column of the table.

This is perhaps because the table is bound to another table?

The values for this column can only be 1,2,3 or 4

**lonelykeyboard** · May 29 '13, 07:40 PM

I will give this a try tomorrow Adezii.

Oralloy comment number 17 was for you.

Thank you both for you help today, I look forward to trying this code tomorrow.

Lonely

**Oralloy** · May 29 '13, 07:47 PM

@ADezii - Thanks for jumping in.

@ADezii - I agree, using a single update would have been the cleanest solution to the entire task.

@lonelykeyboard - So even though there was an SQL error, we didn't see it. I don't have the DoCmd documentation immediately handy, so you have something to add to your punch list - namely error detection and then recovery, control, and/or display.

@lonelykeyboard - Can you change the forced values in my SQL modifications to distinct, acceptable values (say 1 and 4, respectively)- that way you can see the code work. Also, if you update the queries, you should be able to run them directly, saving some time.

--Oralloy

**Oralloy** · May 29 '13, 07:51 PM

@lonelykeyboard - If you open the table [Project Received], are you able to manually change the [Priority Upon Receiving] field?

**lonelykeyboard** · May 30 '13, 11:29 AM

@ADezii - Could you please clarify briefly, is table1 a new table to be created or is it a reference holder as you cannot see my full code?

Also, Project Received is the table, the values I am trying to extract are...

Todays current date - construction complete date.

Todays date as Date() - [Construction Complete] (a table value)

I can understand the coding but I am confused as to where you are placing the public function. It seems that (and please correct me if I am wrong) you are creating a new table (table1) and you are placing the SQL in a query for that new table to update and in the table you are placing several columns:
Project Received, Construction Complete And Priority.

However where would you place the public function? I do not believe I have used this coding method yet.

Thanks for clarification and for "holding me hand" sort of speak through the code you entered.

@Oralloy -

The code you gave to force values is not working. Well let me rephrase. It works, just like ym switch. When I substituted the value 1 for -666 it was placing 2 in the table and when I exchanged the value 4 for the 777 value, it was placing a null value into the table.

Manually, the table works. I can select the options 1,2,3 or 4 from the combobox in the table.

**lonelykeyboard** · May 30 '13, 01:23 PM

Thank you both again for your help!

**Oralloy** · May 30 '13, 01:48 PM

lonelykeyboard,

It's interesting that the database is setting the [Priority Upon Receiving] value to one more than was actually specified in the UPDATE statement. I think that you were using Switch correctly, but that the database is giving you a little bit of grief.

Is the field a selection list with string values "1", "2", "3", and "4"? If so, that would explain the numeric offset of one between the numeric assignment and the display values - the strings are an arbitrary enumeration, with the underlieing numeric representation being a zero-based unsigned integer.

If so, I think we can rewrite the call to Switch as follows to assign the string values:

Code:

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

To test, try using a quick bit of SQL:

Code:

UPDATE [Project Received]
  SET [Priority Upon Receiving] = "2";

Alternately, you could do this, and assign the numeric values:

Code:

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

I have to step out for about an hour. After that we can continue our conversation.

Cheers,
Oralloy

**lonelykeyboard** · May 30 '13, 01:58 PM

The SQL you gave literally does the same thing.

It pushes a value of 3 into the tables,
just as 1 pushes 2 and 4 pushes null

It is bizarre.

Code:

Is the field a selection list with string values "1", "2", "3", and "4"?

The answer is yes it is, it is set as a record source table and is in list form.

**ADezii** · May 30 '13, 01:59 PM

I am afraid that I am confusing the matter with my approach, and I do apologize for that. I have attached a simple Demo that I created that will clearly illustrate what I am saying.

Attached Files

Demo.zip (20.9 KB, 80 views)

**lonelykeyboard** · May 30 '13, 02:02 PM

Aha! I got it...

I went to the table opened the design tab and went to the column which was giving me the issue. Here I went into the lookup tab and I had the bound column set incorrectly. I had it set on the ID instead of on the Priority Level.

If it was not for your help Oralloy it would have taken forever to fix!

Thank you a thousand times over! I hope you enjoy the rest of your day!

Lonelykeyboard

**lonelykeyboard** · May 30 '13, 02:03 PM

Thank you as well Adezii!

**ADezii** · May 30 '13, 02:13 PM

No problem, Oralloy deserves most of the credit, so kindly Flag his as the Best Answer.

**Oralloy** · May 30 '13, 03:00 PM

lonelykeyboard,

I'm glad you found the root problem. That is ever so much more satisfying than a stupid subtract-one patch would have been.

Have a great day!
Oralloy

Switch is generating the wrong results

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment