Case statement equivalent

  • Thread starter Thread starter ricky
  • Start date Start date
R

ricky

Hi

I need to write an equivalent statement of a multi-line case statement, but
I understand there is no real eqivalent of CASE, so IIF is used.

What I have, in pseudo code is:

IIf([TBL_Project_GLDetails.T2]=49734600 then 4900, 45734600 then 4500,
82731000 then 8200, 85731000 then 8500, 86731000 then 8600, 91731000 then
9100) AS T1,

How would I do that in Access?

Kind Regards

Ricky
 
Why not just make it simple if your data all holds true:

T1: Left([TBL_Project_GLDetails.T2],2) * 100

You could create a Case function in a module and call on it in the query:
Function fTiers(strTiers As Variant) As String
Dim TheTier As String
Select Case strTiers
Case 49734600
TheTier = 4900
Case 45734600
TheTier = 4500
Case Is 82731000
TheTier = 8200
Case Is 85731000
TheTier = 8500
Case Is 86731000
TheTier = 8600
Case Is 91731000
TheTier = 9100
Case Else ' Other values.
TheTier = 0
End Select
fTiers = TheTier
End Function
 
Hi Jerry,
Thanks for your reply, is there anyway, this can be done in a query, without
using VBA?

Kind Regards

Rikesh
Jerry Whittle said:
Why not just make it simple if your data all holds true:

T1: Left([TBL_Project_GLDetails.T2],2) * 100

You could create a Case function in a module and call on it in the query:
Function fTiers(strTiers As Variant) As String
Dim TheTier As String
Select Case strTiers
Case 49734600
TheTier = 4900
Case 45734600
TheTier = 4500
Case Is 82731000
TheTier = 8200
Case Is 85731000
TheTier = 8500
Case Is 86731000
TheTier = 8600
Case Is 91731000
TheTier = 9100
Case Else ' Other values.
TheTier = 0
End Select
fTiers = TheTier
End Function


--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


ricky said:
Hi

I need to write an equivalent statement of a multi-line case statement, but
I understand there is no real eqivalent of CASE, so IIF is used.

What I have, in pseudo code is:

IIf([TBL_Project_GLDetails.T2]=49734600 then 4900, 45734600 then 4500,
82731000 then 8200, 85731000 then 8500, 86731000 then 8600, 91731000 then
9100) AS T1,

How would I do that in Access?

Kind Regards

Ricky
Click to expand...
Click to expand...
 
You will need to use embedded IIF's

IIf([TBL_Project_GLDetails.T2]=49734600, 4900,
IIf([TBL_Project_GLDetails.T2]=45734600, 4500,
IIf([TBL_Project_GLDetails.T2]=82731000, 8200,
IIf([TBL_Project_GLDetails.T2]=85731000, 8500,
IIf([TBL_Project_GLDetails.T2]=86731000, 8600,
IIf([TBL_Project_GLDetails.T2]=91731000, 9100, 0)))))) AS T1
 
I would create a lookup table of values. Maintaining these value
relationships in an expression seems to be too much to maintain.
 
Have you looked at the SWITCH function.

SWITCH([TBL_Project_GLDetails.T2]=49734600,4900,[TBL_Project_GLDetails.T2]=
45734600,4500, ...)

That is matched pairs.

If your data all follows the same pattern, then you could probably use this alternative.
IIF([TBL_Project_GLDetails.T2] in (49734600,45734600,...),
CLng(Left(Cstr([TBL_Project_GLDetails.T2],2)) & "00"),Null)
 
Thanks Darren, will give it try....

Cheers

Ricky

Darren said:
You will need to use embedded IIF's

IIf([TBL_Project_GLDetails.T2]=49734600, 4900,
IIf([TBL_Project_GLDetails.T2]=45734600, 4500,
IIf([TBL_Project_GLDetails.T2]=82731000, 8200,
IIf([TBL_Project_GLDetails.T2]=85731000, 8500,
IIf([TBL_Project_GLDetails.T2]=86731000, 8600,
IIf([TBL_Project_GLDetails.T2]=91731000, 9100, 0)))))) AS T1



ricky said:
Hi

I need to write an equivalent statement of a multi-line case statement,
but
I understand there is no real eqivalent of CASE, so IIF is used.

What I have, in pseudo code is:

IIf([TBL_Project_GLDetails.T2]=49734600 then 4900, 45734600 then 4500,
82731000 then 8200, 85731000 then 8500, 86731000 then 8600, 91731000 then
9100) AS T1,

How would I do that in Access?

Kind Regards

Ricky
Click to expand...
Click to expand...
 
Hi John

Didn't know this function existed, never come across this before. Will
explore this method too.

Although, Duane is right in saying that, I should maybe place these values
in a Lookup table?

Kind Regards

Ricky

John Spencer said:
Have you looked at the SWITCH function.

SWITCH([TBL_Project_GLDetails.T2]=49734600,4900,[TBL_Project_GLDetails.T2]=
45734600,4500, ...)

That is matched pairs.

If your data all follows the same pattern, then you could probably use this alternative.
IIF([TBL_Project_GLDetails.T2] in (49734600,45734600,...),
CLng(Left(Cstr([TBL_Project_GLDetails.T2],2)) & "00"),Null)
Hi

I need to write an equivalent statement of a multi-line case statement, but
I understand there is no real eqivalent of CASE, so IIF is used.

What I have, in pseudo code is:

IIf([TBL_Project_GLDetails.T2]=49734600 then 4900, 45734600 then 4500,
82731000 then 8200, 85731000 then 8500, 86731000 then 8600, 91731000 then
9100) AS T1,

How would I do that in Access?

Kind Regards

Ricky
Click to expand...
Click to expand...
 
Duane

That is good point, perhaps I should make this scalable, I suupose I just
haven't thought rationally about this.

Kind Regards

Ricky

Duane Hookom said:
I would create a lookup table of values. Maintaining these value
relationships in an expression seems to be too much to maintain.

--
Duane Hookom
MS Access MVP
--

ricky said:
Hi

I need to write an equivalent statement of a multi-line case statement,
but
I understand there is no real eqivalent of CASE, so IIF is used.

What I have, in pseudo code is:

IIf([TBL_Project_GLDetails.T2]=49734600 then 4900, 45734600 then 4500,
82731000 then 8200, 85731000 then 8500, 86731000 then 8600, 91731000 then
9100) AS T1,

How would I do that in Access?

Kind Regards

Ricky
Click to expand...
Click to expand...
 
Yes, Duane's proposed solution is much better if you can implement the table.
That is usually the way I would solve this type of situation. I know that in
some situations that is very difficult. Sometimes I get channeled into
answering the specific question and forget to analyze and solve the problem.
Hi John

Didn't know this function existed, never come across this before. Will
explore this method too.

Although, Duane is right in saying that, I should maybe place these values
in a Lookup table?

Kind Regards

Ricky

John Spencer said:
Have you looked at the SWITCH function.

SWITCH([TBL_Project_GLDetails.T2]=49734600,4900,[TBL_Project_GLDetails.T2]=
45734600,4500, ...)

That is matched pairs.

If your data all follows the same pattern, then you could probably use this alternative.
IIF([TBL_Project_GLDetails.T2] in (49734600,45734600,...),
CLng(Left(Cstr([TBL_Project_GLDetails.T2],2)) & "00"),Null)
Hi

I need to write an equivalent statement of a multi-line case statement, but
I understand there is no real eqivalent of CASE, so IIF is used.

What I have, in pseudo code is:

IIf([TBL_Project_GLDetails.T2]=49734600 then 4900, 45734600 then 4500,
82731000 then 8200, 85731000 then 8500, 86731000 then 8600, 91731000 then
9100) AS T1,

How would I do that in Access?

Kind Regards

Ricky
Click to expand...
Click to expand...
Click to expand...
 
Back
Top