Avatar of Misghina Tekle
Misghina Tekle
 asked on

How to use switch function in Ms Access query

Hi, I am coding an ms access database solution for an aluminum fabrication company that needs to calculate aluminum profile for a project based on a measurement that is put in the field [Frame H] and it has to kinds of profiles called "DO" and "BO" respectively and they use 4 and 6.1 for calculating but I have like 30 conditions and Values to evaluate and when I use switch function in my query it brings a problem stating that" The string returned by the builder was too long. The result will be truncated".
So here is the list of code that I use...

Shutter H: Switch([ProfID]="DO",
Switch(
[TypeID]="ITM001",[Frame H]-4,
[TypeID]="ITM002",[Frame H]-4,
[TypeID]="ITM003",[Frame H]-30-4,
[TypeID]="ITM004",[Frame H]-30-4,
[TypeID]="ITM005",[Frame H]-4-50,
[TypeID]="ITM006",[Frame H]-4-50,
[TypeID]="ITM007",[Frame H]-30-4-50,
[TypeID]="ITM008",[Frame H]-30-4-50,
[TypeID]="ITM009",[Frame H]-4,
[TypeID]="ITM010",[Frame H]-30-4,
[TypeID]="ITM011",[Frame H]-4,
[TypeID]="ITM012",[Frame H]-30-4,
[TypeID]="ITM013",[Frame H]-4,
[TypeID]="ITM014",[Frame H]-4),
[ProfID]="BO",
Switch(
[TypeID]="ITM001",[Frame H]-6.1,
[TypeID]="ITM002",[Frame H]-6.1,
[TypeID]="ITM003",[Frame H]-30-6.1,
[TypeID]="ITM004",[Frame H]-30-6.1,
[TypeID]="ITM005",[Frame H]-6.1-50,
[TypeID]="ITM006",[Frame H]-6.1-50,
[TypeID]="ITM007",[Frame H]-30-6.1-50,
[TypeID]="ITM008",[Frame H]-30-6.1-50,
[TypeID]="ITM009",[Frame H]-6.1,
[TypeID]="ITM010",[Frame H]-30-6.1,
[TypeID]="ITM011",[Frame H]-6.1,
[TypeID]="ITM012",[Frame H]-30-6.1,
[TypeID]="ITM013",[Frame H]-6.1,
[TypeID]="ITM014",[Frame H]-6.1),)

Thanks for your help.
Misghina
DatabasesMicrosoft AccessSQL* Switch

Avatar of undefined
Last Comment
Misghina Tekle

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
als315

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Misghina Tekle

ASKER
Dear sir/madam can you give me a hint ...
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck