[AccessD] Update query syntax

Charlotte Foust cfoust at infostatsystems.com
Wed Jun 25 11:53:16 CDT 2003


You're still using Mid$, Kathryn, but mixing in Instr arguments.  Change
it to Instr.  You don't want Mid at all except when you want to extract
the value.  In that case, Mid would come into the <truepart> expression
but not the IIf condition test.

 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 8:43 AM
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
> 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: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
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com 
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


More information about the AccessD mailing list