Charlotte Foust
cfoust at infostatsystems.com
Wed Jun 25 12:30:54 CDT 2003
I'd suggest you do a select query on the table and see what gets returned using an expression Instr([Muir63]![attend],"35=2") in a calcuated field and setting the criteria line to >0 and see what records and values you get. Then look at the values in that field in the individual records and see what's going on. If you aren't finding any records, then tell us more about that field so we can begin to guess what's happening. Charlotte Foust -----Original Message----- From: Kathryn Bassett [mailto:kathryn at bassett.net] Sent: Wednesday, June 25, 2003 9:11 AM To: accessd at databaseadvisors.com Subject: RE: [AccessD] Update query syntax 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 _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com