Use of IF then Else syntax in Access

[Guest]

I write a lot of Crystal reports but needed an actual dbase to consolidate
some data. I now have a database that I'm using to calculate sales
commisions. I have to test the sell amount according to the sales line codes
and adjust the cost accordingly. Here is an example of the syntax from
Crystal:

If {OP_SLSADETL.Processing_Code}="D1" or
{OP_SLSADETL.Processing_Code}= "MP" or
{OP_SLSADETL.Processing_Code}= "O1" then {@formTotalSellbyLine}*.01

else if {OP_SLSADETL.Processing_Code}= "LG" or
{OP_SLSADETL.Processing_Code}= "N1" or
{OP_SLSADETL.Processing_Code}= "NR" or
{OP_SLSADETL.Processing_Code}= "NW" or
{OP_SLSADETL.Processing_Code}= "I" then {@formTotalSellbyLine}*.2

this expression goes on to test and apply a few more cost factors. How do I
best accomplish this in Access? I am not VB comfortable, so wanted to try to
just use the If/then/else syntax and adjust the rest of the statements as
needed? Can I do that, should I fiddle with nested IIF's or should I do
something else? Thanks!

 

[Guest]

Nested iif()'s, may be useable depending on how many cases you actually have.
Or, the switch() function may be slightly better (less ()'s to keep track
of). If you haven't used it, switch is a list of expressions and values,
such as:

Switch(expr1, val1, expr2, val2, etc)

The function returns the value corresponding to the first expression that
evaluates to true. Of course, each value can be a calculation.

In either case, from looking at your sample it looks like you may be able to
make great use of In(), such as:

iif(OP_SLSADETL.Processing_Code IN("D1", "MP", "O1"), ...

or,

switch(OP_SLSADETL.Processing_Code IN("D1", "MP", "O1"), ...

HTH, Ted Allen

 

[Guest]

Ted, thanks for the advice. I'm implementing the nested IIF statement below
and am being told I have a syntax problem. Can you review to see where the
problem may lie?

Overhead:
IIf([dbo_OP_SLSADETL]![Processing_Code] In
("D1","MP","O1"),[Sales1Cost]*0.01,
(IIf([dbo_OP_SLSADETL]![Processing_Code] In
("LG","N1","NR","NW","I"),[Sales1Cost]*0.2,
(IIf([dbo_OP_SLSADETL]![Processing_Code] In
("DH","ND","WG","J"),[Sales1Cost]*0.05,
IIf([dbo_OP_SLSADETL]![Processing_Code] In
(“NS","OM","PS","S5","S9","SO","W1","W2","W8","U","D"),[Sales1Cost]*0.04),0))))

 

[Guest]

Hi Ken,

Is this being used in a query? If so, the !'s (bangs) in
[dbo_OP_SLSADETL]![Processing_Code] need to be changed to .'s (periods) if
this is referring to a field in the query's data source. If is is referring
to a value on a form control, it needs to be
Forms![YourFormName]![YourControlName], but I am guessing it is a field in
the data source.

The other thing that caught my eye is that there is an opening ( before each
of the nested iif's which are not necessary. The nested iif would normally
look like:

iif(cond1,trueexpr,iif(cond2, trueexpr, iif(cond3, ... etc

of course, the extra paren wouldn't hurt anything if it is closed at the
proper point, but it didn't appear to be at first glance.

One other thing that I'll mention, it appears that this is likely a SQL
Server linked table (since it starts with dbo). If that is the case, you
have another option that you may want to explore if the Access query is too
slow or messy, which is to use a pass thru query to SQL Server. Of course,
that means using the SQL Server T-SQL syntax, but it's not much different.
The main reason that I mention it is that T-SQL allows for select case
statements and IF/Else statements which Access/Jet do not support. You can
also add comments to the sql text for future reference if you desire.

In some cases, the performance difference is not that great from using
Access against linked tables vs pass-thru queries. But, in cases where the
queries are more complex or have a number of joined tables, I have seen
dramatic differences (such as going from 4-5 minutes to under a second).

Post back if neither of the syntax suggestions above solve your problem, or
if you have questions about pass-thru's.

-Ted Allen

Ted, thanks for the advice. I'm implementing the nested IIF statement below
and am being told I have a syntax problem. Can you review to see where the
problem may lie?

Overhead:
IIf([dbo_OP_SLSADETL]![Processing_Code] In
("D1","MP","O1"),[Sales1Cost]*0.01,
(IIf([dbo_OP_SLSADETL]![Processing_Code] In
("LG","N1","NR","NW","I"),[Sales1Cost]*0.2,
(IIf([dbo_OP_SLSADETL]![Processing_Code] In
("DH","ND","WG","J"),[Sales1Cost]*0.05,
IIf([dbo_OP_SLSADETL]![Processing_Code] In
(“NS","OM","PS","S5","S9","SO","W1","W2","W8","U","D"),[Sales1Cost]*0.04),0))))

Nested iif()'s, may be useable depending on how many cases you actually have.
Or, the switch() function may be slightly better (less ()'s to keep track
of). If you haven't used it, switch is a list of expressions and values,
such as:

Switch(expr1, val1, expr2, val2, etc)

The function returns the value corresponding to the first expression that
evaluates to true. Of course, each value can be a calculation.

In either case, from looking at your sample it looks like you may be able to
make great use of In(), such as:

iif(OP_SLSADETL.Processing_Code IN("D1", "MP", "O1"), ...

or,

switch(OP_SLSADETL.Processing_Code IN("D1", "MP", "O1"), ...

HTH, Ted Allen

 

[Guest]

Oops, sorry for addressing you as Ken in my earlier message (unless that
happens to be your name in which case it was a lucky accident), I had another
post with someone named Ken, and got the name mixed up.

-Ted

Ted, thanks for the advice. I'm implementing the nested IIF statement below
and am being told I have a syntax problem. Can you review to see where the
problem may lie?

Overhead:
IIf([dbo_OP_SLSADETL]![Processing_Code] In
("D1","MP","O1"),[Sales1Cost]*0.01,
(IIf([dbo_OP_SLSADETL]![Processing_Code] In
("LG","N1","NR","NW","I"),[Sales1Cost]*0.2,
(IIf([dbo_OP_SLSADETL]![Processing_Code] In
("DH","ND","WG","J"),[Sales1Cost]*0.05,
IIf([dbo_OP_SLSADETL]![Processing_Code] In
(“NS","OM","PS","S5","S9","SO","W1","W2","W8","U","D"),[Sales1Cost]*0.04),0))))

Nested iif()'s, may be useable depending on how many cases you actually have.
Or, the switch() function may be slightly better (less ()'s to keep track
of). If you haven't used it, switch is a list of expressions and values,
such as:

Switch(expr1, val1, expr2, val2, etc)

The function returns the value corresponding to the first expression that
evaluates to true. Of course, each value can be a calculation.

In either case, from looking at your sample it looks like you may be able to
make great use of In(), such as:

iif(OP_SLSADETL.Processing_Code IN("D1", "MP", "O1"), ...

or,

switch(OP_SLSADETL.Processing_Code IN("D1", "MP", "O1"), ...

HTH, Ted Allen

 

[Guest]

Ted, thanks again for your replies. I appreciate your persistence. I've
attached a revised statement below which Access still protests with the
generic "expression contains invalid syntax" message. I replaced the bangs
with periods (though the !'s were inserted automatically during the field
selection process in the Expression Builder). The only clue Access gives is
that it highlights/stops at the comma between the NS and the OM value on the
last line. The expression doesn't seem overly long at about 390 characters (
I thought the expression length limit was 2048). Do I still have bad nesting
syntax? I can seem to get the first and second test on hte processing code
to complete successfully. It's when I add the 3rd that there is trouble?
I'm completely open to the pass thru query, but would need corresponding help
with that. Thanks again Ted. Dave Miller. Concidentally, the krexroth
moniker is short for the American poet Kenneth Rexroth, so you weren't far
off with your use of Ken.

Overhead: IIf([dbo_OP_SLSADETL].[Processing_Code] In
("D1","MP","O1"),[Sales1Cost]*0.01,IIf([dbo_OP_SLSADETL].[Processing_Code] In
("LG","N1","NR","NW","I"),[Sales1Cost]*0.2,IIf([dbo_OP_SLSADETL].[Processing_Code]
In ("DH","ND","WG","J"),[Sales1Cost]*0.05,
IIf([dbo_OP_SLSADETL].[Processing_Code] In
(“NS","OM","PS","S5","S9","SO","W1","W2","W8","U","D"),[Sales1Cost]*0.04),0))))

Hi Ken,

Is this being used in a query? If so, the !'s (bangs) in
[dbo_OP_SLSADETL]![Processing_Code] need to be changed to .'s (periods) if
this is referring to a field in the query's data source. If is is referring
to a value on a form control, it needs to be
Forms![YourFormName]![YourControlName], but I am guessing it is a field in
the data source.

The other thing that caught my eye is that there is an opening ( before each
of the nested iif's which are not necessary. The nested iif would normally
look like:

iif(cond1,trueexpr,iif(cond2, trueexpr, iif(cond3, ... etc

of course, the extra paren wouldn't hurt anything if it is closed at the
proper point, but it didn't appear to be at first glance.

One other thing that I'll mention, it appears that this is likely a SQL
Server linked table (since it starts with dbo). If that is the case, you
have another option that you may want to explore if the Access query is too
slow or messy, which is to use a pass thru query to SQL Server. Of course,
that means using the SQL Server T-SQL syntax, but it's not much different.
The main reason that I mention it is that T-SQL allows for select case
statements and IF/Else statements which Access/Jet do not support. You can
also add comments to the sql text for future reference if you desire.

In some cases, the performance difference is not that great from using
Access against linked tables vs pass-thru queries. But, in cases where the
queries are more complex or have a number of joined tables, I have seen
dramatic differences (such as going from 4-5 minutes to under a second).

Post back if neither of the syntax suggestions above solve your problem, or
if you have questions about pass-thru's.

-Ted Allen

Ted, thanks for the advice. I'm implementing the nested IIF statement below
and am being told I have a syntax problem. Can you review to see where the
problem may lie?

Overhead:
IIf([dbo_OP_SLSADETL]![Processing_Code] In
("D1","MP","O1"),[Sales1Cost]*0.01,
(IIf([dbo_OP_SLSADETL]![Processing_Code] In
("LG","N1","NR","NW","I"),[Sales1Cost]*0.2,
(IIf([dbo_OP_SLSADETL]![Processing_Code] In
("DH","ND","WG","J"),[Sales1Cost]*0.05,
IIf([dbo_OP_SLSADETL]![Processing_Code] In
(“NS","OM","PS","S5","S9","SO","W1","W2","W8","U","D"),[Sales1Cost]*0.04),0))))

Nested iif()'s, may be useable depending on how many cases you actually have.
Or, the switch() function may be slightly better (less ()'s to keep track
of). If you haven't used it, switch is a list of expressions and values,
such as:

Switch(expr1, val1, expr2, val2, etc)

The function returns the value corresponding to the first expression that
evaluates to true. Of course, each value can be a calculation.

In either case, from looking at your sample it looks like you may be able to
make great use of In(), such as:

iif(OP_SLSADETL.Processing_Code IN("D1", "MP", "O1"), ...

or,

switch(OP_SLSADETL.Processing_Code IN("D1", "MP", "O1"), ...

HTH, Ted Allen

:

I write a lot of Crystal reports but needed an actual dbase to consolidate
some data. I now have a database that I'm using to calculate sales
commisions. I have to test the sell amount according to the sales line codes
and adjust the cost accordingly. Here is an example of the syntax from
Crystal:

If {OP_SLSADETL.Processing_Code}="D1" or
{OP_SLSADETL.Processing_Code}= "MP" or
{OP_SLSADETL.Processing_Code}= "O1" then {@formTotalSellbyLine}*.01

else if {OP_SLSADETL.Processing_Code}= "LG" or
{OP_SLSADETL.Processing_Code}= "N1" or
{OP_SLSADETL.Processing_Code}= "NR" or
{OP_SLSADETL.Processing_Code}= "NW" or
{OP_SLSADETL.Processing_Code}= "I" then {@formTotalSellbyLine}*.2

this expression goes on to test and apply a few more cost factors. How do I
best accomplish this in Access? I am not VB comfortable, so wanted to try to
just use the If/then/else syntax and adjust the rest of the statements as
needed? Can I do that, should I fiddle with nested IIF's or should I do
something else? Thanks!

 

[Guest]

Hi Dave,

That's pretty funny about the moniker.

I posted the expression into notepad, and it looks like somehow the quote in
front of NS is different than all of the rest. I would try deleting that and
retyping it to see if that is the problem.

If you still get an error, maybe try changing the dots back to bangs. My
version of access uses dots (2002), but maybe others used the bangs.

I'll post a sample of a pass-thru query that I use in a separate message.
It should give you an idea of the syntax differences. The main difference is
that the dbo_ before the table name gets changed to dbo. in the FROM
statement, and all other references to the table within the query don't use
the dbo. at all.

To create a pass-thru, create a new query, cancel out of the table selector,
go to query on the menu and choose sql specific, and then pass thru. That
will put you in a bland sql view. Then, choose view properties, and go to
the ODBC Connection property. if you know your connection string, paste it
there. If not, click to the right of that field to bring up the ODBC
connection wizard and it will take you through the same process of building
the connection that you use when connecting to a table.

Then, just type your query sql. I often create the query in Access, then
copy/paste the text in Notepad, manually change the dbo_ to dbo. in the from
clause, then use find/replace to replace the remaining dbo_ with nothing.
Then, I tweak it manually from there.

Once you've created one pass-thru, you can skip the ODBC construction
process by just copying the query and revising the sql text, or by just
copying/pasting the ODBC string into a new query.

Post back if that weird quote wasn't the problem and I'll look at the
expression a little closer.

-Ted Allen

Ted, thanks again for your replies. I appreciate your persistence. I've
attached a revised statement below which Access still protests with the
generic "expression contains invalid syntax" message. I replaced the bangs
with periods (though the !'s were inserted automatically during the field
selection process in the Expression Builder). The only clue Access gives is
that it highlights/stops at the comma between the NS and the OM value on the
last line. The expression doesn't seem overly long at about 390 characters (
I thought the expression length limit was 2048). Do I still have bad nesting
syntax? I can seem to get the first and second test on hte processing code
to complete successfully. It's when I add the 3rd that there is trouble?
I'm completely open to the pass thru query, but would need corresponding help
with that. Thanks again Ted. Dave Miller. Concidentally, the krexroth
moniker is short for the American poet Kenneth Rexroth, so you weren't far
off with your use of Ken.

Overhead: IIf([dbo_OP_SLSADETL].[Processing_Code] In
("D1","MP","O1"),[Sales1Cost]*0.01,IIf([dbo_OP_SLSADETL].[Processing_Code] In
("LG","N1","NR","NW","I"),[Sales1Cost]*0.2,IIf([dbo_OP_SLSADETL].[Processing_Code]
In ("DH","ND","WG","J"),[Sales1Cost]*0.05,
IIf([dbo_OP_SLSADETL].[Processing_Code] In
(“NS","OM","PS","S5","S9","SO","W1","W2","W8","U","D"),[Sales1Cost]*0.04),0))))

Hi Ken,

Is this being used in a query? If so, the !'s (bangs) in
[dbo_OP_SLSADETL]![Processing_Code] need to be changed to .'s (periods) if
this is referring to a field in the query's data source. If is is referring
to a value on a form control, it needs to be
Forms![YourFormName]![YourControlName], but I am guessing it is a field in
the data source.

The other thing that caught my eye is that there is an opening ( before each
of the nested iif's which are not necessary. The nested iif would normally
look like:

iif(cond1,trueexpr,iif(cond2, trueexpr, iif(cond3, ... etc

of course, the extra paren wouldn't hurt anything if it is closed at the
proper point, but it didn't appear to be at first glance.

One other thing that I'll mention, it appears that this is likely a SQL
Server linked table (since it starts with dbo). If that is the case, you
have another option that you may want to explore if the Access query is too
slow or messy, which is to use a pass thru query to SQL Server. Of course,
that means using the SQL Server T-SQL syntax, but it's not much different.
The main reason that I mention it is that T-SQL allows for select case
statements and IF/Else statements which Access/Jet do not support. You can
also add comments to the sql text for future reference if you desire.

In some cases, the performance difference is not that great from using
Access against linked tables vs pass-thru queries. But, in cases where the
queries are more complex or have a number of joined tables, I have seen
dramatic differences (such as going from 4-5 minutes to under a second).

Post back if neither of the syntax suggestions above solve your problem, or
if you have questions about pass-thru's.

-Ted Allen

Ted, thanks for the advice. I'm implementing the nested IIF statement below
and am being told I have a syntax problem. Can you review to see where the
problem may lie?

Overhead:
IIf([dbo_OP_SLSADETL]![Processing_Code] In
("D1","MP","O1"),[Sales1Cost]*0.01,
(IIf([dbo_OP_SLSADETL]![Processing_Code] In
("LG","N1","NR","NW","I"),[Sales1Cost]*0.2,
(IIf([dbo_OP_SLSADETL]![Processing_Code] In
("DH","ND","WG","J"),[Sales1Cost]*0.05,
IIf([dbo_OP_SLSADETL]![Processing_Code] In
(“NS","OM","PS","S5","S9","SO","W1","W2","W8","U","D"),[Sales1Cost]*0.04),0))))

:

Nested iif()'s, may be useable depending on how many cases you actually have.
Or, the switch() function may be slightly better (less ()'s to keep track
of). If you haven't used it, switch is a list of expressions and values,
such as:

Switch(expr1, val1, expr2, val2, etc)

The function returns the value corresponding to the first expression that
evaluates to true. Of course, each value can be a calculation.

In either case, from looking at your sample it looks like you may be able to
make great use of In(), such as:

iif(OP_SLSADETL.Processing_Code IN("D1", "MP", "O1"), ...

or,

switch(OP_SLSADETL.Processing_Code IN("D1", "MP", "O1"), ...

HTH, Ted Allen

:

I write a lot of Crystal reports but needed an actual dbase to consolidate
some data. I now have a database that I'm using to calculate sales
commisions. I have to test the sell amount according to the sales line codes
and adjust the cost accordingly. Here is an example of the syntax from
Crystal:

If {OP_SLSADETL.Processing_Code}="D1" or
{OP_SLSADETL.Processing_Code}= "MP" or
{OP_SLSADETL.Processing_Code}= "O1" then {@formTotalSellbyLine}*.01

else if {OP_SLSADETL.Processing_Code}= "LG" or
{OP_SLSADETL.Processing_Code}= "N1" or
{OP_SLSADETL.Processing_Code}= "NR" or
{OP_SLSADETL.Processing_Code}= "NW" or
{OP_SLSADETL.Processing_Code}= "I" then {@formTotalSellbyLine}*.2

this expression goes on to test and apply a few more cost factors. How do I
best accomplish this in Access? I am not VB comfortable, so wanted to try to
just use the If/then/else syntax and adjust the rest of the statements as
needed? Can I do that, should I fiddle with nested IIF's or should I do
something else? Thanks!

 

[Guest]

Hi Dave,

Here is the sql from a simple pass-thru to SQL Server which uses the CASE
selection.

SELECT tProject_Info.ProjectID, tProjectPhase.PhaseCode,
tProjectPhase.StartDate, tProjectPhase.EndDate,
tProjectPhase.PercentComplete, tProjectPhase.Perc100Date,
PhaseName = CASE tProjectPhase.PhaseCode
WHEN 1 THEN 'PreDesign'
WHEN 2 THEN 'Design'
WHEN 3 THEN 'ROW'
WHEN 4 THEN 'BidAward'
WHEN 5 THEN 'Construction'
WHEN 6 THEN 'PostConst'
ELSE 'Other'
END
FROM dbo.tProject_Info INNER JOIN dbo.tProjectPhase ON
tProject_Info.ProjectID = tProjectPhase.ProjectID
WHERE tProject_Info.ProjectID=2052;

Hopefully that will help. Of course, in your case you would replace the
string values being assigned with the appropriate expressions. I definitely
recommend experimenting with pass-thru's, as they can come in very handy.

-Ted Allen

Ted, thanks again for your replies. I appreciate your persistence. I've
attached a revised statement below which Access still protests with the
generic "expression contains invalid syntax" message. I replaced the bangs
with periods (though the !'s were inserted automatically during the field
selection process in the Expression Builder). The only clue Access gives is
that it highlights/stops at the comma between the NS and the OM value on the
last line. The expression doesn't seem overly long at about 390 characters (
I thought the expression length limit was 2048). Do I still have bad nesting
syntax? I can seem to get the first and second test on hte processing code
to complete successfully. It's when I add the 3rd that there is trouble?
I'm completely open to the pass thru query, but would need corresponding help
with that. Thanks again Ted. Dave Miller. Concidentally, the krexroth
moniker is short for the American poet Kenneth Rexroth, so you weren't far
off with your use of Ken.

Overhead: IIf([dbo_OP_SLSADETL].[Processing_Code] In
("D1","MP","O1"),[Sales1Cost]*0.01,IIf([dbo_OP_SLSADETL].[Processing_Code] In
("LG","N1","NR","NW","I"),[Sales1Cost]*0.2,IIf([dbo_OP_SLSADETL].[Processing_Code]
In ("DH","ND","WG","J"),[Sales1Cost]*0.05,
IIf([dbo_OP_SLSADETL].[Processing_Code] In
(“NS","OM","PS","S5","S9","SO","W1","W2","W8","U","D"),[Sales1Cost]*0.04),0))))

Hi Ken,

Is this being used in a query? If so, the !'s (bangs) in
[dbo_OP_SLSADETL]![Processing_Code] need to be changed to .'s (periods) if
this is referring to a field in the query's data source. If is is referring
to a value on a form control, it needs to be
Forms![YourFormName]![YourControlName], but I am guessing it is a field in
the data source.

The other thing that caught my eye is that there is an opening ( before each
of the nested iif's which are not necessary. The nested iif would normally
look like:

iif(cond1,trueexpr,iif(cond2, trueexpr, iif(cond3, ... etc

of course, the extra paren wouldn't hurt anything if it is closed at the
proper point, but it didn't appear to be at first glance.

One other thing that I'll mention, it appears that this is likely a SQL
Server linked table (since it starts with dbo). If that is the case, you
have another option that you may want to explore if the Access query is too
slow or messy, which is to use a pass thru query to SQL Server. Of course,
that means using the SQL Server T-SQL syntax, but it's not much different.
The main reason that I mention it is that T-SQL allows for select case
statements and IF/Else statements which Access/Jet do not support. You can
also add comments to the sql text for future reference if you desire.

In some cases, the performance difference is not that great from using
Access against linked tables vs pass-thru queries. But, in cases where the
queries are more complex or have a number of joined tables, I have seen
dramatic differences (such as going from 4-5 minutes to under a second).

Post back if neither of the syntax suggestions above solve your problem, or
if you have questions about pass-thru's.

-Ted Allen

Ted, thanks for the advice. I'm implementing the nested IIF statement below
and am being told I have a syntax problem. Can you review to see where the
problem may lie?

Overhead:
IIf([dbo_OP_SLSADETL]![Processing_Code] In
("D1","MP","O1"),[Sales1Cost]*0.01,
(IIf([dbo_OP_SLSADETL]![Processing_Code] In
("LG","N1","NR","NW","I"),[Sales1Cost]*0.2,
(IIf([dbo_OP_SLSADETL]![Processing_Code] In
("DH","ND","WG","J"),[Sales1Cost]*0.05,
IIf([dbo_OP_SLSADETL]![Processing_Code] In
(“NS","OM","PS","S5","S9","SO","W1","W2","W8","U","D"),[Sales1Cost]*0.04),0))))

:

Nested iif()'s, may be useable depending on how many cases you actually have.
Or, the switch() function may be slightly better (less ()'s to keep track
of). If you haven't used it, switch is a list of expressions and values,
such as:

Switch(expr1, val1, expr2, val2, etc)

The function returns the value corresponding to the first expression that
evaluates to true. Of course, each value can be a calculation.

In either case, from looking at your sample it looks like you may be able to
make great use of In(), such as:

iif(OP_SLSADETL.Processing_Code IN("D1", "MP", "O1"), ...

or,

switch(OP_SLSADETL.Processing_Code IN("D1", "MP", "O1"), ...

HTH, Ted Allen

:

I write a lot of Crystal reports but needed an actual dbase to consolidate
some data. I now have a database that I'm using to calculate sales
commisions. I have to test the sell amount according to the sales line codes
and adjust the cost accordingly. Here is an example of the syntax from
Crystal:

If {OP_SLSADETL.Processing_Code}="D1" or
{OP_SLSADETL.Processing_Code}= "MP" or
{OP_SLSADETL.Processing_Code}= "O1" then {@formTotalSellbyLine}*.01

else if {OP_SLSADETL.Processing_Code}= "LG" or
{OP_SLSADETL.Processing_Code}= "N1" or
{OP_SLSADETL.Processing_Code}= "NR" or
{OP_SLSADETL.Processing_Code}= "NW" or
{OP_SLSADETL.Processing_Code}= "I" then {@formTotalSellbyLine}*.2

this expression goes on to test and apply a few more cost factors. How do I
best accomplish this in Access? I am not VB comfortable, so wanted to try to
just use the If/then/else syntax and adjust the rest of the statements as
needed? Can I do that, should I fiddle with nested IIF's or should I do
something else? Thanks!

 

[Guest]

Hey Ted, good catch on the different " character. I found that as well while
I was awaiting your next timely reply. That seems to have done the trick,
but I need to review results tomorrow. Can I contact you via this post
again? In other words, are you getting my replies via email, or are you just
watching this post?

Also, I'd like to begin using VB or sql query language so that I can start
using IF/Then/Else syntax. Should I just pick up one of the Que monster
volumes on Access and begin digging in or do you have a better source?
Thanks again Ted!

Hi Dave,

That's pretty funny about the moniker.

I posted the expression into notepad, and it looks like somehow the quote in
front of NS is different than all of the rest. I would try deleting that and
retyping it to see if that is the problem.

If you still get an error, maybe try changing the dots back to bangs. My
version of access uses dots (2002), but maybe others used the bangs.

I'll post a sample of a pass-thru query that I use in a separate message.
It should give you an idea of the syntax differences. The main difference is
that the dbo_ before the table name gets changed to dbo. in the FROM
statement, and all other references to the table within the query don't use
the dbo. at all.

To create a pass-thru, create a new query, cancel out of the table selector,
go to query on the menu and choose sql specific, and then pass thru. That
will put you in a bland sql view. Then, choose view properties, and go to
the ODBC Connection property. if you know your connection string, paste it
there. If not, click to the right of that field to bring up the ODBC
connection wizard and it will take you through the same process of building
the connection that you use when connecting to a table.

Then, just type your query sql. I often create the query in Access, then
copy/paste the text in Notepad, manually change the dbo_ to dbo. in the from
clause, then use find/replace to replace the remaining dbo_ with nothing.
Then, I tweak it manually from there.

Once you've created one pass-thru, you can skip the ODBC construction
process by just copying the query and revising the sql text, or by just
copying/pasting the ODBC string into a new query.

Post back if that weird quote wasn't the problem and I'll look at the
expression a little closer.

-Ted Allen

Ted, thanks again for your replies. I appreciate your persistence. I've
attached a revised statement below which Access still protests with the
generic "expression contains invalid syntax" message. I replaced the bangs
with periods (though the !'s were inserted automatically during the field
selection process in the Expression Builder). The only clue Access gives is
that it highlights/stops at the comma between the NS and the OM value on the
last line. The expression doesn't seem overly long at about 390 characters (
I thought the expression length limit was 2048). Do I still have bad nesting
syntax? I can seem to get the first and second test on hte processing code
to complete successfully. It's when I add the 3rd that there is trouble?
I'm completely open to the pass thru query, but would need corresponding help
with that. Thanks again Ted. Dave Miller. Concidentally, the krexroth
moniker is short for the American poet Kenneth Rexroth, so you weren't far
off with your use of Ken.

Overhead: IIf([dbo_OP_SLSADETL].[Processing_Code] In
("D1","MP","O1"),[Sales1Cost]*0.01,IIf([dbo_OP_SLSADETL].[Processing_Code] In
("LG","N1","NR","NW","I"),[Sales1Cost]*0.2,IIf([dbo_OP_SLSADETL].[Processing_Code]
In ("DH","ND","WG","J"),[Sales1Cost]*0.05,
IIf([dbo_OP_SLSADETL].[Processing_Code] In
(“NS","OM","PS","S5","S9","SO","W1","W2","W8","U","D"),[Sales1Cost]*0.04),0))))

Hi Ken,

Is this being used in a query? If so, the !'s (bangs) in
[dbo_OP_SLSADETL]![Processing_Code] need to be changed to .'s (periods) if
this is referring to a field in the query's data source. If is is referring
to a value on a form control, it needs to be
Forms![YourFormName]![YourControlName], but I am guessing it is a field in
the data source.

The other thing that caught my eye is that there is an opening ( before each
of the nested iif's which are not necessary. The nested iif would normally
look like:

iif(cond1,trueexpr,iif(cond2, trueexpr, iif(cond3, ... etc

of course, the extra paren wouldn't hurt anything if it is closed at the
proper point, but it didn't appear to be at first glance.

One other thing that I'll mention, it appears that this is likely a SQL
Server linked table (since it starts with dbo). If that is the case, you
have another option that you may want to explore if the Access query is too
slow or messy, which is to use a pass thru query to SQL Server. Of course,
that means using the SQL Server T-SQL syntax, but it's not much different.
The main reason that I mention it is that T-SQL allows for select case
statements and IF/Else statements which Access/Jet do not support. You can
also add comments to the sql text for future reference if you desire.

In some cases, the performance difference is not that great from using
Access against linked tables vs pass-thru queries. But, in cases where the
queries are more complex or have a number of joined tables, I have seen
dramatic differences (such as going from 4-5 minutes to under a second).

Post back if neither of the syntax suggestions above solve your problem, or
if you have questions about pass-thru's.

-Ted Allen
:

Ted, thanks for the advice. I'm implementing the nested IIF statement below
and am being told I have a syntax problem. Can you review to see where the
problem may lie?

Overhead:
IIf([dbo_OP_SLSADETL]![Processing_Code] In
("D1","MP","O1"),[Sales1Cost]*0.01,
(IIf([dbo_OP_SLSADETL]![Processing_Code] In
("LG","N1","NR","NW","I"),[Sales1Cost]*0.2,
(IIf([dbo_OP_SLSADETL]![Processing_Code] In
("DH","ND","WG","J"),[Sales1Cost]*0.05,
IIf([dbo_OP_SLSADETL]![Processing_Code] In
(“NS","OM","PS","S5","S9","SO","W1","W2","W8","U","D"),[Sales1Cost]*0.04),0))))

:

Nested iif()'s, may be useable depending on how many cases you actually have.
Or, the switch() function may be slightly better (less ()'s to keep track
of). If you haven't used it, switch is a list of expressions and values,
such as:

Switch(expr1, val1, expr2, val2, etc)

The function returns the value corresponding to the first expression that
evaluates to true. Of course, each value can be a calculation.

In either case, from looking at your sample it looks like you may be able to
make great use of In(), such as:

iif(OP_SLSADETL.Processing_Code IN("D1", "MP", "O1"), ...

or,

switch(OP_SLSADETL.Processing_Code IN("D1", "MP", "O1"), ...

HTH, Ted Allen

:

I write a lot of Crystal reports but needed an actual dbase to consolidate
some data. I now have a database that I'm using to calculate sales
commisions. I have to test the sell amount according to the sales line codes
and adjust the cost accordingly. Here is an example of the syntax from
Crystal:

If {OP_SLSADETL.Processing_Code}="D1" or
{OP_SLSADETL.Processing_Code}= "MP" or
{OP_SLSADETL.Processing_Code}= "O1" then {@formTotalSellbyLine}*.01

else if {OP_SLSADETL.Processing_Code}= "LG" or
{OP_SLSADETL.Processing_Code}= "N1" or
{OP_SLSADETL.Processing_Code}= "NR" or
{OP_SLSADETL.Processing_Code}= "NW" or
{OP_SLSADETL.Processing_Code}= "I" then {@formTotalSellbyLine}*.2

this expression goes on to test and apply a few more cost factors. How do I
best accomplish this in Access? I am not VB comfortable, so wanted to try to
just use the If/then/else syntax and adjust the rest of the statements as
needed? Can I do that, should I fiddle with nested IIF's or should I do
something else? Thanks!

 

[Guest]

Glad you got it working Dave. Hopefully it will check out properly.

I use the Microsoft newsreader to read and respond to posts. I also
generally search google for recent posts containing my name each day (which
is why I generally sign with my full name), so I will find your post if you
post to this thread later. But, tomorrow is my day off so I likely won't see
it until Mon morning.

By the way, speaking of google, if you haven't tried it already for
searching the newsgroup it is really great. You can try it at the link:

http://groups-beta.google.com/groups?q=microsoft.public.access.*

I also use google for questions about Excel and Word. It is much more
effective than the search tool built into the Microsoft Newsreader, although
it can take a few hours before you see new posts.

Best of Luck, hopefully the query will be working properly.

-Ted Allen

Hey Ted, good catch on the different " character. I found that as well while
I was awaiting your next timely reply. That seems to have done the trick,
but I need to review results tomorrow. Can I contact you via this post
again? In other words, are you getting my replies via email, or are you just
watching this post?

Also, I'd like to begin using VB or sql query language so that I can start
using IF/Then/Else syntax. Should I just pick up one of the Que monster
volumes on Access and begin digging in or do you have a better source?
Thanks again Ted!

Hi Dave,

That's pretty funny about the moniker.

I posted the expression into notepad, and it looks like somehow the quote in
front of NS is different than all of the rest. I would try deleting that and
retyping it to see if that is the problem.

If you still get an error, maybe try changing the dots back to bangs. My
version of access uses dots (2002), but maybe others used the bangs.

I'll post a sample of a pass-thru query that I use in a separate message.
It should give you an idea of the syntax differences. The main difference is
that the dbo_ before the table name gets changed to dbo. in the FROM
statement, and all other references to the table within the query don't use
the dbo. at all.

To create a pass-thru, create a new query, cancel out of the table selector,
go to query on the menu and choose sql specific, and then pass thru. That
will put you in a bland sql view. Then, choose view properties, and go to
the ODBC Connection property. if you know your connection string, paste it
there. If not, click to the right of that field to bring up the ODBC
connection wizard and it will take you through the same process of building
the connection that you use when connecting to a table.

Then, just type your query sql. I often create the query in Access, then
copy/paste the text in Notepad, manually change the dbo_ to dbo. in the from
clause, then use find/replace to replace the remaining dbo_ with nothing.
Then, I tweak it manually from there.

Once you've created one pass-thru, you can skip the ODBC construction
process by just copying the query and revising the sql text, or by just
copying/pasting the ODBC string into a new query.

Post back if that weird quote wasn't the problem and I'll look at the
expression a little closer.

-Ted Allen

Ted, thanks again for your replies. I appreciate your persistence. I've
attached a revised statement below which Access still protests with the
generic "expression contains invalid syntax" message. I replaced the bangs
with periods (though the !'s were inserted automatically during the field
selection process in the Expression Builder). The only clue Access gives is
that it highlights/stops at the comma between the NS and the OM value on the
last line. The expression doesn't seem overly long at about 390 characters (
I thought the expression length limit was 2048). Do I still have bad nesting
syntax? I can seem to get the first and second test on hte processing code
to complete successfully. It's when I add the 3rd that there is trouble?
I'm completely open to the pass thru query, but would need corresponding help
with that. Thanks again Ted. Dave Miller. Concidentally, the krexroth
moniker is short for the American poet Kenneth Rexroth, so you weren't far
off with your use of Ken.

Overhead: IIf([dbo_OP_SLSADETL].[Processing_Code] In
("D1","MP","O1"),[Sales1Cost]*0.01,IIf([dbo_OP_SLSADETL].[Processing_Code] In
("LG","N1","NR","NW","I"),[Sales1Cost]*0.2,IIf([dbo_OP_SLSADETL].[Processing_Code]
In ("DH","ND","WG","J"),[Sales1Cost]*0.05,
IIf([dbo_OP_SLSADETL].[Processing_Code] In
(“NS","OM","PS","S5","S9","SO","W1","W2","W8","U","D"),[Sales1Cost]*0.04),0))))



:

Hi Ken,

Is this being used in a query? If so, the !'s (bangs) in
[dbo_OP_SLSADETL]![Processing_Code] need to be changed to .'s (periods) if
this is referring to a field in the query's data source. If is is referring
to a value on a form control, it needs to be
Forms![YourFormName]![YourControlName], but I am guessing it is a field in
the data source.

The other thing that caught my eye is that there is an opening ( before each
of the nested iif's which are not necessary. The nested iif would normally
look like:

iif(cond1,trueexpr,iif(cond2, trueexpr, iif(cond3, ... etc

of course, the extra paren wouldn't hurt anything if it is closed at the
proper point, but it didn't appear to be at first glance.

One other thing that I'll mention, it appears that this is likely a SQL
Server linked table (since it starts with dbo). If that is the case, you
have another option that you may want to explore if the Access query is too
slow or messy, which is to use a pass thru query to SQL Server. Of course,
that means using the SQL Server T-SQL syntax, but it's not much different.
The main reason that I mention it is that T-SQL allows for select case
statements and IF/Else statements which Access/Jet do not support. You can
also add comments to the sql text for future reference if you desire.

In some cases, the performance difference is not that great from using
Access against linked tables vs pass-thru queries. But, in cases where the
queries are more complex or have a number of joined tables, I have seen
dramatic differences (such as going from 4-5 minutes to under a second).

Post back if neither of the syntax suggestions above solve your problem, or
if you have questions about pass-thru's.

-Ted Allen
:

Ted, thanks for the advice. I'm implementing the nested IIF statement below
and am being told I have a syntax problem. Can you review to see where the
problem may lie?

Overhead:
IIf([dbo_OP_SLSADETL]![Processing_Code] In
("D1","MP","O1"),[Sales1Cost]*0.01,
(IIf([dbo_OP_SLSADETL]![Processing_Code] In
("LG","N1","NR","NW","I"),[Sales1Cost]*0.2,
(IIf([dbo_OP_SLSADETL]![Processing_Code] In
("DH","ND","WG","J"),[Sales1Cost]*0.05,
IIf([dbo_OP_SLSADETL]![Processing_Code] In
(“NS","OM","PS","S5","S9","SO","W1","W2","W8","U","D"),[Sales1Cost]*0.04),0))))

:

Nested iif()'s, may be useable depending on how many cases you actually have.
Or, the switch() function may be slightly better (less ()'s to keep track
of). If you haven't used it, switch is a list of expressions and values,
such as:

Switch(expr1, val1, expr2, val2, etc)

The function returns the value corresponding to the first expression that
evaluates to true. Of course, each value can be a calculation.

In either case, from looking at your sample it looks like you may be able to
make great use of In(), such as:

iif(OP_SLSADETL.Processing_Code IN("D1", "MP", "O1"), ...

or,

switch(OP_SLSADETL.Processing_Code IN("D1", "MP", "O1"), ...

HTH, Ted Allen

:

I write a lot of Crystal reports but needed an actual dbase to consolidate
some data. I now have a database that I'm using to calculate sales
commisions. I have to test the sell amount according to the sales line codes
and adjust the cost accordingly. Here is an example of the syntax from
Crystal:

If {OP_SLSADETL.Processing_Code}="D1" or
{OP_SLSADETL.Processing_Code}= "MP" or
{OP_SLSADETL.Processing_Code}= "O1" then {@formTotalSellbyLine}*.01

else if {OP_SLSADETL.Processing_Code}= "LG" or
{OP_SLSADETL.Processing_Code}= "N1" or
{OP_SLSADETL.Processing_Code}= "NR" or
{OP_SLSADETL.Processing_Code}= "NW" or
{OP_SLSADETL.Processing_Code}= "I" then {@formTotalSellbyLine}*.2

this expression goes on to test and apply a few more cost factors. How do I
best accomplish this in Access? I am not VB comfortable, so wanted to try to
just use the If/then/else syntax and adjust the rest of the statements as
needed? Can I do that, should I fiddle with nested IIF's or should I do
something else? Thanks!