Trying a complex database calculation

[HargyG]

I am new to ms access and I am struggling through teaching myself some of the basics and I think I'm getting the hang of it. I did NOT pick an easy project to start with and am in need of some assistance.
I am conducting some fact gathering and have put together a database for collected pain audit information. One of the data points I am wanting is a calculated score for the Opioid Risk Tool (easily google search.) Based on answers to historical information and patient interview information, I want the table to calculate the risk score form the answers given. Here is what I've got so far:

= SUM (IIF «EXPR» [PAIN AUDIT MASTER DATABASE]![SEX]”MALE” AND «EXPR» [PAIN AUDIT MASTER DATABASE]![ALCOHOL ABUSE FH] >0)+3, (IIF «EXPR» [PAIN AUDIT MASTER DATABASE]![SEX] “FEMALE” AND «EXPR» [PAIN AUDIT MASTER DATABASE]![ALCOHOL ABUSE FH] >0)+1, (IIF «EXPR» [PAIN AUDIT MASTER DATABASE]![SEX] “MALE” AND «EXPR» [PAIN AUDIT MASTER DATABASE]![ILLICIT DRUG ABUSE FH] >0)+3, (IIF «EXPR» [PAIN AUDIT MASTER DATABASE]![SEX] “FEMALE” AND «EXPR» [PAIN AUDIT MASTER DATABASE]![ILLICIT DRUG ABUSE FH] >0)+2, (IIF «EXPR» [PAIN AUDIT MASTER DATABASE]![PRESCRIPTION ABUSE FH] >0)+4, (IIF «EXPR» [PAIN AUDIT MASTER DATABASE]![ALCOHOL ABUSE MH] >0)+3, (IIF «EXPR» [PAIN AUDIT MASTER DATABASE]![EVER TAKE MORE (EXTRA) DOSES]>0 OR [PAIN AUDIT MASTER DATABASE]![EVER BORROW PAIN MED] >0)+5, (IIF «EXPR» [PAIN AUDIT MASTER DATABASE]![EVER BUY STREET] >0)+4, (IIF «EXPR» [PAIN AUDIT MASTER DATABASE]![AGE] >= 16 AND <= 45)+1, (IIF «EXPR» [PAIN AUDIT MASTER DATABASE]![SEX] "FEMALE" AND «EXPR» [PAIN AUDIT MASTER DATABASE]![PREADOLESCENT SEXUAL ABUSE] >0)+3, (IIF «EXPR» [PAIN AUDIT MASTER DATABASE]![ADD] >0 , OR «EXPR» [PAIN AUDIT MASTER DATABASE]![OCD] >0 , OR «EXPR» [PAIN AUDIT MASTER DATABASE]![BIPOLAR] >0, OR «EXPR» [PAIN AUDIT MASTER DATABASE]![SCHITZOPHRENIA] >0)+2, (IIF «EXPR» [PAIN AUDIT MASTER DATABASE]![DEPRESSION] >0)+1

UGLY, I KNOW. How can I simplify this?

 

[CJ_London]

Looks like you have been using the expression builder. At the moment your code is meaningless - you need to remove all the '«EXPR»' since these are just a prompt for you to enter something.

Also IIf needs to be followed with a bracket and you are missing = signs so at the beginning you have

= SUM (IIF «EXPR» [PAIN AUDIT MASTER DATABASE]![SEX]”MALE”.......

and it should be

= SUM (IIF [COLOR=red]([/COLOR][PAIN AUDIT MASTER DATABASE]![SEX][COLOR=red]=[/COLOR]”MALE”.....

It is also not clear how the calculation is supposed to work.

Your code is way too difficult to decipher at the moment and you should use the code tags to highlight it (go to advanced editor, highlight the code and then click the code button (the #)

Suggest you first edit the code to put in brackets,remove the «EXPR», add the = etc then repost using the code tag.

You also need to explain where you are using this calculation - is it in a form report, query or vba? and what is your source -[PAIN AUDIT MASTER DATABASE]![SEX] looks like it is a table but would not necessarily be referenced in this way.

Finally

I want the table to calculate the risk score

Do you mean the query/report/form? Tables don't do calculations - I accept there is a calculated field option for later versions of Access but you won't find anyone who recommends using them and you will get very little help when you have problems - other than to recommend not using them.

And a last tip - avoid using spaces in table and field names - it significantly increases the chance of making an error and you will find Access substitutes a space with an underscore - all of which will waste your time trying to find the problem