Link to home
Create AccountLog in
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
ASKER CERTIFIED SOLUTION
Avatar of als315
als315
Flag of Russian Federation image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Misghina Tekle
Misghina Tekle

ASKER

Dear sir/madam can you give me a hint ...