SQL CASE WHEN STMT in VB behind a BUTTON

[Rahul79]

Hello All,

I am in a dilemma. I have written this long CASE statement to work in VB for a specific Task.

I have searched and searched everywhere. I am getting a lot of mixed responses, however, one that seems to be common is that I am unable to use CASE statement in Access VBA.

Anyways, below is the CASE Statement. If all the statements are run Individually, as update statements, they run perfectly fine. However, I have around 50 of these conditional stmts and I need to speed up the performance of this Update query.

Any help or ideas are much appreciated.

Here is the code:

strSQL1 = "UPDATE ATBTemp SET Aging = " & _
“CASE “ & _
"WHEN [Age 2] < 31 THEN '0-30'" & _
"WHEN [Age 2] between 31 and 60 THEN '31-60'" & _
"WHEN [Age 2] between 61 and 90 THEN '61-90'" & _
"WHEN [Age 2] between 91 and 120 THEN '91-120'" & _
"WHEN [Age 2] between 121 and 150 THEN '121-150'" & _
"WHEN [Age 2] between 151 and 180 THEN '151-180'" & _
"WHEN [Age 2] between 181 and 210 THEN '181-210'" & _
"WHEN [Age 2] between 211 and 240 THEN '211-240'" & _
"WHEN [Age 2] > 240 THEN '> 240'" & _
“END”

I have 3 of such case statements, with the other 2 combining for over 55 WHEN stmts.


Thanks in Advance.

 

[Poppa Smurf]

You can use Case statement but it must be in the format

Select Case Name
Case 1

Case 2

Case x

End select

In your case do the following
Dim aging_result as string

Select Case [Age2]
Case Is < 31
aging_result = "0-30"

Case 31 To 60
aging_result = "31-60"

etc...
Case Is > 240
aging_result = "> 240"

End Select

You will need to get the correct syntax for your strSQL1 regarding " and ' etc.
strSQL1 = "UPDATE ATBTemp SET Aging = "& aging_result

 

[namliam]

I do not want to be a pain, but age is a calculatable value... calculatable values should never be stored, since they need regular updates. this is a calculated value from age.... which makes it worse.

your case statement here can be made much more simple.... by either
1) using something like:

format(int((YourAge-1)/30)*30, "000") & "-" & format(int((YourAge-1)/30+1)*30, "000")

This obviously will only work with fixed 30 year brackets

2) creating a reference table
Either a list of

Age bracket
1   000-030
2   000-030
...
74  060-090

which you can simply join to your age

Or have a table like

Min Max bracket
0   30  000-030
31  60  031-060

Which you can also join quite easily, though in the second case you dont really need the brackets and you can simply make the format for it yourself.

 

[Rahul79]

namliam and PoppaSmurf,

I thank you for the great insight in this code by providing your answer.

Namliam, I understand where you are coming from, however, the sole purpose of this dB is to create a Trending Dashboard with several conditional updates to an AR file dump from a Siemens IT system called Soarian. It creates Crystal Reports however, has the ability to also do dumps in Excel.

I do like the 3rd option. The age that is calculated is the Days Aging on the AR.

PoppaSmurf: I was confused about the Select CASE statement, and our post helps. I do plan to look in to it more and see how to use it for efficiency. Thanks.

Normally, I would have about 15k lines of data going as deep as Column AZ (52).

I ignored the code I posted, and went on to create an individual Table where all of probably 70 Update SQL statements ran on 2 import files in 2 temp tables, which then got transfered to the Main table and generated about 8 reports which are presented as a sub report on the final report.

Having that many SQL Update queries, I was getting around 15 minute runtime with no progress bar in the corner leaving me to just wait.

However, once I only focused it to the Temp Table, I am getting under 5 minute performance, and less than 20 sec on the Append Query to main Table.


Though, I will say, the insight you both provided to me is much appreciated and I intend to use probably both in different instances.

Thanks much for taking the time to be very specific and explaining it in detail.

MODS: You can close this thread.

Thanks,

RP

 

[namliam]

Doing updates and temp tables, really is a bad solution, but as long as it makes things work and you are happy about it...