RE: Use of IF then Else syntax in Access

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

"krexroth" wrote:

> 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))))
>
>
>
> "Ted Allen" wrote:
>
> > 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
> > "krexroth" wrote:
> >
> > > 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))))
> > >
> > > "Ted Allen" wrote:
> > >
> > > > 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
> > > >
> > > > "krexroth" wrote:
> > > >
> > > > > 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!
.

Relevant Pages

  • RE: EXISTS reserved word in FROM clause
    ... we make a good pair because I know Access SQL ... from what you described the query syntax that I ... >The subquery should return the records: ...
    (microsoft.public.access.queries)
  • Re: access cmd to sql server "grant insert on.."
    ... You need to create a SPT query. ... This uses the syntax of the SERVER. ... Ditto for SQL Server. ...
    (microsoft.public.access.externaldata)
  • Re: SSAS: Processing Cube hangs
    ... Your LEFT JOIN query should not be that much faster than the query that ... syntax that is effectively be equivalent to using inner joins. ... The query optimizer in SQL Server knows how to work with both syntaxes ... Dimension User: about 200 records ...
    (microsoft.public.sqlserver.olap)
  • RE: EXISTS reserved word in FROM clause
    ... we make a good pair because I know Access SQL ... from what you described the query syntax that I ... > from a set of segments bearing the same name. ...
    (microsoft.public.access.queries)
  • Re: Need to display results of Stored Proceedure with Criteria
    ... I was able to now get the SP to have dbo as the owner, ... doesn't show the create proceedure part in the sql part of the query builder ... don't forget to refresh the query list ... Sylvain Lafontaine, ing. ...
    (microsoft.public.access.adp.sqlserver)