Syntax error converting an excel formula to access.

K

KLKNOX

I have created a formula in Excel to allow for testing. I want to convert
that to a formula in access but some of the syntax is wrong. Can someone
please help. I have highlighted some of my concerns. Here is the statement:



IF(OR([M_WK_1]=0,SUM([P_WK_1]:[P_WK_4])=0),"",IF((AND([M_WK_1]>[P_WK_1],(([M_WK_1]-[P_WK_1])/[P_WK_1])<0.05)),1,IF((AND(([M_WK_1]+[M_WK_2])>([P_WK_1]+[P_WK_2]),(([M_WK_1]+[M_WK_2])-([P_WK_1]+[P_WK_2])/([P_WK_1]+[P_WK_2]))<0.05)),1,IF((AND(([M_WK_1]+[M_WK_2]+[M_WK_3])>([P_WK_1]+[P_WK_2]+[P_WK_3]),(([M_WK_1]+[M_WK_2]+[M_WK_3])-([P_WK_1]+[P_WK_2]+[P_WK_3])/([P_WK_1]+[P_WK_2]+[P_WK_3]))<0.05)),1,IF((AND(([M_WK_1]+[M_WK_2]+[M_WK_3]+[M_WK_4])>([P_WK_1]+[P_WK_2]+[P_WK_3]+[P_WK_4]),(([M_WK_1]+[M_WK_2]+[M_WK_3]+[M_WK_4])-([P_WK_1]+[P_WK_2]+[P_WK_3]+[P_WK_4])/([P_WK_1]+[P_WK_2]+[P_WK_3]+[P_WK_4]))<0.05)),1,0)))))
 
T

Txori

I made this modification. It runs but I get a #ERROR on some lines.

NEW:IIF(([M_WK_1]=0 OR
([P_WK_1]+[P_WK_2]+[P_WK_3]+[P_WK_4])=0),"",IIF((([M_WK_1]>[P_WK_1] AND
(([M_WK_1]-[P_WK_1])/[P_WK_1])<0.05)),1,IIF(((([M_WK_1]+[M_WK_2])>([P_WK_1]+[P_WK_2])
AND(([M_WK_1]+[M_WK_2])-([P_WK_1]+[P_WK_2])/([P_WK_1]+[P_WK_2]))<0.05)),1,IIF(((([M_WK_1]+[M_WK_2]+[M_WK_3])>([P_WK_1]+[P_WK_2]+[P_WK_3])
AND
(([M_WK_1]+[M_WK_2]+[M_WK_3])-([P_WK_1]+[P_WK_2]+[P_WK_3])/([P_WK_1]+[P_WK_2]+[P_WK_3]))<0.05)),1,IIF(((([M_WK_1]+[M_WK_2]+[M_WK_3]+[M_WK_4])>([P_WK_1]+[P_WK_2]+[P_WK_3]+[P_WK_4])
AND
(([M_WK_1]+[M_WK_2]+[M_WK_3]+[M_WK_4])-([P_WK_1]+[P_WK_2]+[P_WK_3]+[P_WK_4])/([P_WK_1]+[P_WK_2]+[P_WK_3]+[P_WK_4]))<0.05)),1,0)))))

Thoughts?
 
T

Txori

Changes have been made. Still have one issue. I am still having problems
where P_WK_1 =0. Can you have IIF (x=1 and y=2 and r>4), " ", whatver. See
below in 1st nested IIF.

NEW: IIf(([M_WK_1]=0 Or
([P_WK_1]+[P_WK_2]+[P_WK_3]+[P_WK_4])=0),"",IIf((([P_WK_1]>0 And
[M_WK_1]>[P_WK_1] And
(([M_WK_1]-[P_WK_1])/[P_WK_1])<0.05)),1,IIf(((([M_WK_1]+[M_WK_2])>([P_WK_1]+[P_WK_2])
And
(([M_WK_1]+[M_WK_2])-([P_WK_1]+[P_WK_2])/([P_WK_1]+[P_WK_2]))<0.05)),1,IIf(((([M_WK_1]+[M_WK_2]+[M_WK_3])>([P_WK_1]+[P_WK_2]+[P_WK_3])
And
(([M_WK_1]+[M_WK_2]+[M_WK_3])-([P_WK_1]+[P_WK_2]+[P_WK_3])/([P_WK_1]+[P_WK_2]+[P_WK_3]))<0.05)),1,IIf(((([M_WK_1]+[M_WK_2]+[M_WK_3]+[M_WK_4])>([P_WK_1]+[P_WK_2]+[P_WK_3]+[P_WK_4])
And
(([M_WK_1]+[M_WK_2]+[M_WK_3]+[M_WK_4])-([P_WK_1]+[P_WK_2]+[P_WK_3]+[P_WK_4])/([P_WK_1]+[P_WK_2]+[P_WK_3]+[P_WK_4]))<0.05)),1,0)))))

Txori said:
I made this modification. It runs but I get a #ERROR on some lines.

NEW:IIF(([M_WK_1]=0 OR
([P_WK_1]+[P_WK_2]+[P_WK_3]+[P_WK_4])=0),"",IIF((([M_WK_1]>[P_WK_1] AND
(([M_WK_1]-[P_WK_1])/[P_WK_1])<0.05)),1,IIF(((([M_WK_1]+[M_WK_2])>([P_WK_1]+[P_WK_2])
AND(([M_WK_1]+[M_WK_2])-([P_WK_1]+[P_WK_2])/([P_WK_1]+[P_WK_2]))<0.05)),1,IIF(((([M_WK_1]+[M_WK_2]+[M_WK_3])>([P_WK_1]+[P_WK_2]+[P_WK_3])
AND
(([M_WK_1]+[M_WK_2]+[M_WK_3])-([P_WK_1]+[P_WK_2]+[P_WK_3])/([P_WK_1]+[P_WK_2]+[P_WK_3]))<0.05)),1,IIF(((([M_WK_1]+[M_WK_2]+[M_WK_3]+[M_WK_4])>([P_WK_1]+[P_WK_2]+[P_WK_3]+[P_WK_4])
AND
(([M_WK_1]+[M_WK_2]+[M_WK_3]+[M_WK_4])-([P_WK_1]+[P_WK_2]+[P_WK_3]+[P_WK_4])/([P_WK_1]+[P_WK_2]+[P_WK_3]+[P_WK_4]))<0.05)),1,0)))))

Thoughts?

KLKNOX said:
I have created a formula in Excel to allow for testing. I want to convert
that to a formula in access but some of the syntax is wrong. Can someone
please help. I have highlighted some of my concerns. Here is the statement:



IF(OR([M_WK_1]=0,SUM([P_WK_1]:[P_WK_4])=0),"",IF((AND([M_WK_1]>[P_WK_1],(([M_WK_1]-[P_WK_1])/[P_WK_1])<0.05)),1,IF((AND(([M_WK_1]+[M_WK_2])>([P_WK_1]+[P_WK_2]),(([M_WK_1]+[M_WK_2])-([P_WK_1]+[P_WK_2])/([P_WK_1]+[P_WK_2]))<0.05)),1,IF((AND(([M_WK_1]+[M_WK_2]+[M_WK_3])>([P_WK_1]+[P_WK_2]+[P_WK_3]),(([M_WK_1]+[M_WK_2]+[M_WK_3])-([P_WK_1]+[P_WK_2]+[P_WK_3])/([P_WK_1]+[P_WK_2]+[P_WK_3]))<0.05)),1,IF((AND(([M_WK_1]+[M_WK_2]+[M_WK_3]+[M_WK_4])>([P_WK_1]+[P_WK_2]+[P_WK_3]+[P_WK_4]),(([M_WK_1]+[M_WK_2]+[M_WK_3]+[M_WK_4])-([P_WK_1]+[P_WK_2]+[P_WK_3]+[P_WK_4])/([P_WK_1]+[P_WK_2]+[P_WK_3]+[P_WK_4]))<0.05)),1,0)))))
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Standard deviation calculation error... 7
Found a Bug or Error in Access 2002-2003 Round Function 3
Is this type of formula possible in Excel? 3
Converting an Excel IF statement to an Access IFF statement 4
defined sheet formula to vb code 2
excel formula to access code 2
converting some t-sql queries to MS ACCESS 5
Aggregate Function Error 0

Top