How to write Switch function in VBA - Help

[Gravy]

Am a self taught user and don't know VBA or SQL so have come up on a problem using the Switch functionality. It appears that Switch only allows 15 combinations and I have considerably more that I need,to put in.



I've got this ( shortened version) and it's working well... but I can't add the remainder of the combinations:

Risk: Switch([Consequence] = 1 AND [Likelihood] = 1, "Low",
[Consequence] = 1 AND [Likelihood] = 2, "Medium")

Can anyone tell me how I can fix this and please be very clear as I have absolutely no prior knowledge using VBA

thanks in advance

[andy49]

I'd like to hear more about the number of consequences and likelihoods and what you'd want outcome to be?

[Gravy]

There are 20 possible variants for this particular item
ive also got another similar piece with about 40 variants

So if you pick

Remote from Consequence field and
Minor from Likelihood field
Your risk rating will be Low (returned in third field)

[June7]

I had no problem building Switch with 20 elements.

However, you are probably hitting a limit for number of characters allowed in a single cell of query design grid, which is 1024.

Options:

1. a table of these risk combinations

2. a custom VBA function

[andy49]

Maybe easier to create a table of consequence,likelihood,outcome Unless there's a mathematical link between your ones and zeros and the outcome word.

[Gravy]

Ahhh ok I did get too much text so may have to rename those dields
i have a table,set,up with the three fields and all variations but I don't know how to use the existing data ...is it dlookup?

[andy49]

Wondering if you could link the tables on the two fields you have

[Gravy]

I checked character count, it will only accept 756 with spaces...is there some way I can increase it to the 1024 you mentioned?

the fields are in the one table
I used Switch because I couldn't work out how to refer to that table and say if they pick this item and that item return the result picked from third field...I know it can be done I'm just not sure how ... Switch worked until I had too many possibilities ��

[andy49]

Then dlookup probably easiest.

[Gravy]

Thanks
better start googling how to do dlookup

[Gravy]

So does dlookup capture the result against a record or only shows the result in the form?

[andy49]

Captures result from a table based on a form or other value

[June7]

Domain aggregate functions can be slow performers in query and textbox so a table join is preferable if at all possible. So if you have every possible combination of the 3 values in table, should be able to join on the Consequence and Likelihood fields (compound link) and that will make the Rating field available.

Domain aggregate functions can be used in query, textbox, VBA.

I don't know why your expression fails at 756 characters, should allow 1024. There is no way to modify Access specification limits. http://www.databasedev.co.uk/access_specifications.html