[AccessD] Update query syntax

Kathryn,

Use the Instr() function as shown in Charlotte's e-mail, not the Mid$()
function.  Check the on-line help for the differences.

Susan,

"35=2" is contained in "35=20" so it counts as true, whether you really
want it or not.  When both may be valid values, I would either first
check for the "35=20" value and in the <falsepart> check for the "35=2"
value, or first check for "35=2" and then in the <truepart> check for
"35=20" value.

Charles Wortz
Software Development Division
Texas Education Agency
1701 N. Congress Ave
Austin, TX 78701-1494
512-463-9493
CWortz at tea.state.tx.us



-----Original Message-----
From: Kathryn Bassett [mailto:kathryn at bassett.net] 
Sent: Wednesday 2003 Jun 25 11:43
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Update query syntax

I now have:
http://www.muir63.org/images/updatesyntaxquery.jpg
IIf(Mid$([Muir63]![attend],"35=2",>0),"<truepart>","<falsepart>")
but when I try to run it, I get "data type mismatch in criteria
expression". I assume it has something to do with the true and false
parts. Don't understand what to do there, as if it's true, then I want
to update the other field as shown in the jpg. If it's not true, then
nothing should happen.

Getting there, but... just call me dense today. On the other hand, if I
can ultimately "get" this, I should be able to figure out others in the
future.

Kathryn

> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Wortz, 
> Charles
> Sent: 25 Jun 2003 9:15:AM
> To: accessd at databaseadvisors.com
> Subject: RE: [AccessD] Update query syntax
> 
> 
> If the string was found, then Instr() gives you the position number of

> where it starts, else it returns 0.  So, if that number is >0, you 
> have found what you are looking for and the <truepart> will be 
> executed. Otherwise, you will execute the <falsepart>.  Thus, 
> <truepart> is where you say what to do when the string is found, and 
> <falsepart> is where you say what to do if the string is not found.  
> If either takes more than one line of code, you should encapsulate it 
> in a procedure and call the procedure in <truepart> or <falsepart>.
> 
> Charles Wortz
> -----Original Message-----
> From: Kathryn Bassett [mailto:kathryn at bassett.net]
> Sent: Wednesday 2003 Jun 25 11:01
> To: accessd at databaseadvisors.com
> Subject: RE: [AccessD] Update query syntax
> 
> Problem is that I still don't understand the parts. What is >0 about? 
> What goes in <truepart> and what goes in <falsepart>? Kathryn
> 
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Charlotte
> > Foust
> > Sent: 25 Jun 2003 8:51:AM
> > To: accessd at databaseadvisors.com
> > Subject: RE: [AccessD] Update query syntax
> > 
> > 
> > Kathryn,
> > 
> > Now you're beginning to see why so few of us ever bother with the
> > builder.  It's far easier to use intellisense to create the
expression
> 
> > or look at the object browser.
> > 
> > Try
> > 
> > IIF(Instr([Muir63]![attend],"35=2")>0,<truepart>, <falsepart>)
> > 
> > Charlotte Foust
> > 
> > -----Original Message-----
> > From: Kathryn Bassett [mailto:kathryn at bassett.net]
> > Sent: Wednesday, June 25, 2003 7:37 AM
> > To: accessd at databaseadvisors.com
> > Subject: RE: [AccessD] Update query syntax
> > 
> > 
> > I'm starting to get there, but...
> > 
> > After double clicking on IIF, I get:
> > IIf (<expr>, <truepart>, <falsepart>)
> > Then I highlighted <expr> and clicked Mid$ so I now have: IIf ( Mid$

> > (<stringexpr>, <start>, <length>) , <truepart>,
> <falsepart>)
> > Highlight <stringexpr> and click on tables > Muir63 as the table and

> > attend as the field so I now have: IIf ( Mid$ ( [Muir63]![attend] , 
> > <start>, <length>) , <truepart>, <falsepart>)
> > 
> > >From there, I'm lost again. Since I'm deleting all the thread 
> > >leading to this point, I'll recap. The field I'm selecting FROM is 
> > >attend,
> and
> > here is a typical content:
> > 35=2 30b=2 30a=2 25=2
> > So, for my criteria, I want
> > if [Muir63]![attend] contains 35=2
> > so, which of the <start>, <length>, <truepart>, and <falsepart>) 
> > gets what? It seems to me that there are too many parts.
> > 
> > Keep in mind that 35=2 could be anywhere in the string, not just at
> > the beginning. But there won't be any spaces like 35 = 2, or
anything 
> > like that, always the exact phrase of 35=2.
> > 
> > Thanks for helping,
> > 
> > --
> > Kathryn Rhinehart Bassett (Pasadena CA)
> > "Genealogy is my bag" "GH is my soap"
> > kathryn at bassett.net
> > http://bassett.net