Kathryn Bassett
kathryn at bassett.net
Wed Jun 25 12:11:05 CDT 2003
I tried a copy/paste of yours, but it didn't work, saying the were too many closing parameters. Since there were quotes around the true and false with the builder, I copy/pasted "<truepart>","<falsepart>" over <truepart>, <falsepart> so that I ended up with: IIf(InStr([Muir63]![attend],"35=2")>0,"<truepart>","<falsepart>") Now it doesn't give me any errors, but it also says I will now update zero rows - which I know is wrong as there are a bunch with 35=2 - now what? Kathryn > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Charlotte > Foust > Sent: 25 Jun 2003 9:53:AM > To: accessd at databaseadvisors.com > Subject: RE: [AccessD] Update query syntax > > > 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 > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com