Need to convert this code to a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have this code and I need to convert this to a query. The reason it is a
new field and the current records being entered will do this but I also need
to update the old records also. So can anyone help guide me in making a
Update query for this code?

icount = 0
Me.PHQ_Depress_Diag.Value = 0 '"No Depression Disorder"
If Me.PHQ_01.Value > 1 Or Me.PHQ_02.Value > 1 Then
If Me.PHQ_01.Value > 1 Then icount = icount + 1
If Me.PHQ_02.Value > 1 Then icount = icount + 1
If Me.PHQ_03.Value > 1 Then icount = icount + 1
If Me.PHQ_04.Value > 1 Then icount = icount + 1
If Me.PHQ_05.Value > 1 Then icount = icount + 1
If Me.PHQ_06.Value > 1 Then icount = icount + 1
If Me.PHQ_07.Value > 1 Then icount = icount + 1
If Me.PHQ_08.Value > 1 Then icount = icount + 1
If Me.PHQ_09.Value > 1 Then icount = icount + 1
If icount >= 5 Then
Me.PHQ_Depress_Diag.Value = 2 '"Major Depressive Disorder"
Else
If icount > 1 And icount < 5 Then
Me.PHQ_Depress_Diag.Value = 1 '"Other Depressive Disorder"
End If
End If
End If
 
Pokdbz,

I think this will do it...

UPDATE YourTable SET PHQ_Depress_Diag = ([PHQ_01]>1 Or
[PHQ_02]>1)*IIf(Abs(([PHQ_01]>1)+([PHQ_02]>1)+([PHQ_03]>1)+([PHQ_04]>1)+([PHQ_05]>1)+([PHQ_06]>1)+([PHQ_07]>1)+([PHQ_08]>1)+([PHQ_09]>1))>=5,-2,-1)
 
What does the -2 and -1 represent?


Steve Schapel said:
Pokdbz,

I think this will do it...

UPDATE YourTable SET PHQ_Depress_Diag = ([PHQ_01]>1 Or
[PHQ_02]>1)*IIf(Abs(([PHQ_01]>1)+([PHQ_02]>1)+([PHQ_03]>1)+([PHQ_04]>1)+([PHQ_05]>1)+([PHQ_06]>1)+([PHQ_07]>1)+([PHQ_08]>1)+([PHQ_09]>1))>=5,-2,-1)

--
Steve Schapel, Microsoft Access MVP

I have this code and I need to convert this to a query. The reason it is a
new field and the current records being entered will do this but I also need
to update the old records also. So can anyone help guide me in making a
Update query for this code?

icount = 0
Me.PHQ_Depress_Diag.Value = 0 '"No Depression Disorder"
If Me.PHQ_01.Value > 1 Or Me.PHQ_02.Value > 1 Then
If Me.PHQ_01.Value > 1 Then icount = icount + 1
If Me.PHQ_02.Value > 1 Then icount = icount + 1
If Me.PHQ_03.Value > 1 Then icount = icount + 1
If Me.PHQ_04.Value > 1 Then icount = icount + 1
If Me.PHQ_05.Value > 1 Then icount = icount + 1
If Me.PHQ_06.Value > 1 Then icount = icount + 1
If Me.PHQ_07.Value > 1 Then icount = icount + 1
If Me.PHQ_08.Value > 1 Then icount = icount + 1
If Me.PHQ_09.Value > 1 Then icount = icount + 1
If icount >= 5 Then
Me.PHQ_Depress_Diag.Value = 2 '"Major Depressive Disorder"
Else
If icount > 1 And icount < 5 Then
Me.PHQ_Depress_Diag.Value = 1 '"Other Depressive Disorder"
End If
End If
End If
Click to expand...
Click to expand...
 
Is this in there
Else
If icount > 1 And icount < 5 Then
Me.PHQ_Depress_Diag.Value = 1 '"Other Depressive Disorder"
End If
How do you put this in there if icount > 1 and < 5
Thanks for the help

Steve Schapel said:
Pokdbz,

I think this will do it...

UPDATE YourTable SET PHQ_Depress_Diag = ([PHQ_01]>1 Or
[PHQ_02]>1)*IIf(Abs(([PHQ_01]>1)+([PHQ_02]>1)+([PHQ_03]>1)+([PHQ_04]>1)+([PHQ_05]>1)+([PHQ_06]>1)+([PHQ_07]>1)+([PHQ_08]>1)+([PHQ_09]>1))>=5,-2,-1)

--
Steve Schapel, Microsoft Access MVP

I have this code and I need to convert this to a query. The reason it is a
new field and the current records being entered will do this but I also need
to update the old records also. So can anyone help guide me in making a
Update query for this code?

icount = 0
Me.PHQ_Depress_Diag.Value = 0 '"No Depression Disorder"
If Me.PHQ_01.Value > 1 Or Me.PHQ_02.Value > 1 Then
If Me.PHQ_01.Value > 1 Then icount = icount + 1
If Me.PHQ_02.Value > 1 Then icount = icount + 1
If Me.PHQ_03.Value > 1 Then icount = icount + 1
If Me.PHQ_04.Value > 1 Then icount = icount + 1
If Me.PHQ_05.Value > 1 Then icount = icount + 1
If Me.PHQ_06.Value > 1 Then icount = icount + 1
If Me.PHQ_07.Value > 1 Then icount = icount + 1
If Me.PHQ_08.Value > 1 Then icount = icount + 1
If Me.PHQ_09.Value > 1 Then icount = icount + 1
If icount >= 5 Then
Me.PHQ_Depress_Diag.Value = 2 '"Major Depressive Disorder"
Else
If icount > 1 And icount < 5 Then
Me.PHQ_Depress_Diag.Value = 1 '"Other Depressive Disorder"
End If
End If
End If
Click to expand...
Click to expand...
 
Pokdbz,

They represent the values of 2 and 1 that you want to assign to the
PHQ_Depress_Diag field. They are -ve so they will result in the correct
value when they are multiplied by ([PHQ_01]>1 Or [PHQ_02]>1) which will
evaluate to -1 if true.
 
Pokdbz,

Yes, this is included in my suggested solution. Have you tried it yet?
Did it produce the expected results?
 
Yes I tried it the only problem was with > 1 and < 5. If the total is 1 then
it results in 1 but it shouldn't be 1 since it is > 1 not >= 1
 
Pokdbz,

Ah, I see. My mistake. Well, I guess that means it will be a bit more
long-winded! How about like this...
UPDATE YourTable SET PHQ_Depress_Diag = ([PHQ_01]>1 Or
[PHQ_02]>1)*Switch(Abs(([PHQ_01]>1)+([PHQ_02]>1)+([PHQ_03]>1)+([PHQ_04]>1)+([PHQ_05]>1)+([PHQ_06]>1)+([PHQ_07]>1)+([PHQ_08]>1)+([PHQ_09]>1))>=5,-2,Abs(([PHQ_01]>1)+([PHQ_02]>1)+([PHQ_03]>1)+([PHQ_04]>1)+([PHQ_05]>1)+([PHQ_06]>1)+([PHQ_07]>1)+([PHQ_08]>1)+([PHQ_09]>1))>1,-1)
 
That didn't seem to work. Some of the fields were left blank. And I can't
find a pattern why.


Steve Schapel said:
Pokdbz,

Ah, I see. My mistake. Well, I guess that means it will be a bit more
long-winded! How about like this...
UPDATE YourTable SET PHQ_Depress_Diag = ([PHQ_01]>1 Or
[PHQ_02]>1)*Switch(Abs(([PHQ_01]>1)+([PHQ_02]>1)+([PHQ_03]>1)+([PHQ_04]>1)+([PHQ_05]>1)+([PHQ_06]>1)+([PHQ_07]>1)+([PHQ_08]>1)+([PHQ_09]>1))>=5,-2,Abs(([PHQ_01]>1)+([PHQ_02]>1)+([PHQ_03]>1)+([PHQ_04]>1)+([PHQ_05]>1)+([PHQ_06]>1)+([PHQ_07]>1)+([PHQ_08]>1)+([PHQ_09]>1))>1,-1)


--
Steve Schapel, Microsoft Access MVP
Yes I tried it the only problem was with > 1 and < 5. If the total is 1 then
it results in 1 but it shouldn't be 1 since it is > 1 not >= 1

:
Click to expand...
Click to expand...
 
Pokdbz,

Ok, well how about try this...
UPDATE YourTable SET PHQ_Depress_Diag = ([PHQ_01]>1 Or
[PHQ_02]>1)*IIf(Abs(([PHQ_01]>1)+([PHQ_02]>1)+([PHQ_03]>1)+([PHQ_04]>1)+([PHQ_05]>1)+([PHQ_06]>1)+([PHQ_07]>1)+([PHQ_08]>1)+([PHQ_09]>1))>=5,-2,IIf(Abs(([PHQ_01]>1)+([PHQ_02]>1)+([PHQ_03]>1)+([PHQ_04]>1)+([PHQ_05]>1)+([PHQ_06]>1)+([PHQ_07]>1)+([PHQ_08]>1)+([PHQ_09]>1))>1,-1,0))
 
OK, that filled in every field. But it is not calculating the >= 5 right.
Here is the 1-9
3,3,3,3,1,1,0,0,0
and the result is 1 with the query it should be 2


Steve Schapel said:
Pokdbz,

Ok, well how about try this...
UPDATE YourTable SET PHQ_Depress_Diag = ([PHQ_01]>1 Or
[PHQ_02]>1)*IIf(Abs(([PHQ_01]>1)+([PHQ_02]>1)+([PHQ_03]>1)+([PHQ_04]>1)+([PHQ_05]>1)+([PHQ_06]>1)+([PHQ_07]>1)+([PHQ_08]>1)+([PHQ_09]>1))>=5,-2,IIf(Abs(([PHQ_01]>1)+([PHQ_02]>1)+([PHQ_03]>1)+([PHQ_04]>1)+([PHQ_05]>1)+([PHQ_06]>1)+([PHQ_07]>1)+([PHQ_08]>1)+([PHQ_09]>1))>1,-1,0))

--
Steve Schapel, Microsoft Access MVP
That didn't seem to work. Some of the fields were left blank. And I can't
find a pattern why.
Click to expand...
Click to expand...
 
Pokdbz,

My understanding of the requirement is that in your example there are 4
results where the value is >1 so the PHQ_Depress_Diag should be 1
 
My bad you are right. I was adding up the total not adding 1 for each > 1.
Yep everything looks right now.
Thanks for all the help and being so patient.
 
Back
Top