Mark A Matte
markamatte at hotmail.com
Mon Jan 5 15:12:25 CST 2009
I apologize...I'll try 1 more time. replace 'WTF1' with the "less than" symbol...and replace 'WTF2' with the "greater than" symbol ***Begin*** SELECT tblTestData.NBR, tblTestData.test_data, IIf(Len([test_data])-InStr([test_data],"time")WTF1 4,"Time",Trim(Mid([test_data],InStr([test_data],"time"),4) & IIf(Asc(Mid([test_data],InStr([test_data],"time")+4,1)) Between 65 And 90,"x",IIf(Asc(Mid([test_data],InStr([test_data],"time")+4,1)) Between 97 And 122,"x"," ")))) AS TestField FROM tblTestData WHERE (((IIf(Len([test_data])-InStr([test_data],"time")WTF1 4,"Time",Trim(Mid([test_data],InStr([test_data],"time"),4) & IIf(Asc(Mid([test_data],InStr([test_data],"time")+4,1)) Between 65 And 90,"x",IIf(Asc(Mid([test_data],InStr([test_data],"time")+4,1)) Between 97 And 122,"x"," "))))) Like "time") AND ((InStr([test_data],"time"))WTF2 0)); ***END*** ---------------------------------------- > Date: Mon, 5 Jan 2009 12:00:01 -0800 > From: iggy at nanaimo.ark.com > To: accessd at databaseadvisors.com > Subject: Re: [AccessD] Query Grid - Exact Match > > Hey Mark > I will try your code tomorrow and let you know what I find. > I am one of the West Coast whiners, more snow last night. Everyone is > saying "Snow - No More No More". Been digging out the driveway again > and "Ah jeez" wouldn't you know it, there goes the snow plow, back out > to dig out the entrance to the driveway. > Let you know in the morning. > > Thanks very much. > > > > Mark A Matte wrote: > >>Tony, >> >>Gets everything except for #9...even gets Drew's example... >> >> >>Let me know...Thanks, Mark >> >>*********BEGIN SQL*********** >>SELECT tblTestData.NBR, tblTestData.test_data, IIf(Len([test_data])-InStr([test_data],"time")0) AND ((IIf(Len([test_data])-InStr([test_data],"time") Date: Mon, 5 Jan 2009 11:10:54 -0600 >> >> >>>From: DWUTKA at marlow.com >>>To: accessd at databaseadvisors.com >>>Subject: Re: [AccessD] Query Grid - Exact Match >>> >>>Like '* Time' OR Like '* Time *' or like 'Time *' >>> >>>This would not catch 'Now is the right time.' Though....so you may want >>>an OR Like '* Time.' >>> >>>Drew >>> >>>-----Original Message----- >>>From: accessd-bounces at databaseadvisors.com >>>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte >>>Sent: Monday, January 05, 2009 10:59 AM >>>To: accessd at databaseadvisors.com >>>Subject: Re: [AccessD] Query Grid - Exact Match >>> >>> >>>Tony, >>> >>>Your examples below almost seem like you are trying to account for >>>typo's? >>> >>>...or did I miss something? >>> >>> >>>Mark >>> >>>---------------------------------------- >>> >>> >>>>Date: Mon, 5 Jan 2009 08:39:30 -0800 >>>>From: iggy at nanaimo.ark.com >>>>To: accessd at databaseadvisors.com >>>>Subject: [AccessD] Query Grid - Exact Match >>>> >>>>Hey All >>>>Just wondering if there is an easy way using the query grid to find >>>> >>>> >>>the >>> >>> >>>>exact word only in a string in a text field. I have done it, but I am >>>>using instr(), ASC() tests, etc. to pull an exact match. Just thinking >>>>there must be an easier way. >>>>Example find exact word Time >>>>Text >>>>1. The time is 9 o'clock >>>>2. The timer has been set >>>>3. Please enter Time/Day/Month >>>>4. Please enter Time&Day&Month >>>>5. Time is on your side >>>>6. All timesheets required by 4 oclock >>>>7. Please enter Day/Month/Time >>>>8. Last recorder time9 PM >>>>9. Please enter TimeandDay >>>>10. Please set the timertoff >>>> >>>>Would like only records 1,3,4,5,7 and 8 to be displayed and my query >>>>currently does that. 9 (like to get) and 10(would be punted) can be >>>>possibly be done with some tweaking, but kind of messy. >>>> >>>>-- >>>>AccessD mailing list >>>>AccessD at databaseadvisors.com >>>>http://databaseadvisors.com/mailman/listinfo/accessd >>>>Website: http://www.databaseadvisors.com >>>> >>>> >>>_________________________________________________________________ >>>Send e-mail anywhere. No map, no compass. >>>http://windowslive.com/oneline/hotmail?ocid=TXT_TAGLM_WL_hotmail_acq_any >>>where_122008 >>>-- >>>AccessD mailing list >>>AccessD at databaseadvisors.com >>>http://databaseadvisors.com/mailman/listinfo/accessd >>>Website: http://www.databaseadvisors.com >>>The information contained in this transmission is intended only for the person or entity to which it is addressed and may contain II-VI Proprietary and/or II-VI Business Sensitive material. If you are not the intended recipient, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy. You are notified that any review, retransmission, copying, disclosure, dissemination, or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. >>> >>> >>>-- >>>AccessD mailing list >>>AccessD at databaseadvisors.com >>>http://databaseadvisors.com/mailman/listinfo/accessd >>>Website: http://www.databaseadvisors.com >>> >>> >>_________________________________________________________________ >>It’s the same Hotmail®. If by “same” you mean up to 70% faster. >>http://windowslive.com/online/hotmail?ocid=TXT_TAGLM_WL_hotmail_acq_broad1_122008 >> >> > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com _________________________________________________________________ It’s the same Hotmail®. If by “same” you mean up to 70% faster. http://windowslive.com/online/hotmail?ocid=TXT_TAGLM_WL_hotmail_acq_broad1_122008