There's a information link to this ACCESS-L listserve in the body of this message. Lucy Sent via BlackBerry from T-Mobile
-----Original Message----- From: ACCESS-L automatic digest system <[email protected]> Sender: Microsoft Access Database Discussion List <[email protected]> Date: Mon, 5 Sep 2011 23:00:04 To: <[email protected]> Reply-To: Microsoft Access Database Discussion List <[email protected]> Subject: ACCESS-L Digest - 4 Sep 2011 to 5 Sep 2011 (#2011-225) There are 4 messages totaling 359 lines in this issue. Topics of the day: 1. ACCESS-L Digest - 3 Sep 2011 to 4 Sep 2011 (#2011-224) (2) 2. Conversion of text to numbers in Access 2003 (2) -------------------------------------------------------------------------- The ACCESS-L list is hosted on a Windows(R) 2003 Server running L-Soft international's LISTSERV(R) software. For subscription/signoff info and archives, see http://peach.ease.lsoft.com/archives/access-l.html . COPYRIGHT INFO: http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=ACCESS-L ---------------------------------------------------------------------- Date: Sun, 4 Sep 2011 20:18:37 -0700 From: Judy Hopkins <[email protected]> Subject: Re: ACCESS-L Digest - 3 Sep 2011 to 4 Sep 2011 (#2011-224) Kay, Any chance you could get the client to rerun the data with real numbers in all the fields? The "<-0.5" or ">0.5" should be calculated in the query, not in the original data. Good luck, Judy Hopkins >-----Original Message----- >From: Microsoft Access Database Discussion List >[mailto:[email protected]] On Behalf Of Kay >Sent: Saturday, 3 September 2011 2:44 AM >To: [email protected] >Subject: Conversion of text to numbers in Access 2003 > >Hi Listers, > >Could someone offer advise on how to handle criteria and >conditional format on a text field when you need to limit your >records by a range of "numbers". To be more specific, the client >has a text field that holds numbers and/or text. The do not want to >record large numbers, but instead to write in the cell < -0.05 >or >0.5 or any range of "number". Fine, but then they want to query >based on an acceptable range, returning only certain records. >-0.5 >and <0.5. I have tried various options, but cannot get the record >to recognize the range. They also want conditional formatting to >happen based on that same criteria. > >Please, any suggestions would be appreciated. > >Best regards, > >Kay > >-------------------------------------------------------------------------- >The ACCESS-L list is hosted on a Windows(R) 2003 Server running L-Soft >international's LISTSERV(R) software. For subscription/signoff info >and archives, see http://peach.ease.lsoft.com/archives/access-l.html . > COPYRIGHT INFO: >http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=ACCESS-L > >-------------------------------------------------------------------------- >The ACCESS-L list is hosted on a Windows(R) 2003 Server running L-Soft >international's LISTSERV(R) software. For subscription/signoff info >and archives, see http://peach.ease.lsoft.com/archives/access-l.html . > COPYRIGHT INFO: >http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=ACCESS-L > >------------------------------ > >End of ACCESS-L Digest - 3 Sep 2011 to 4 Sep 2011 (#2011-224) >************************************************************* -------------------------------------------------------------------------- The ACCESS-L list is hosted on a Windows(R) 2003 Server running L-Soft international's LISTSERV(R) software. For subscription/signoff info and archives, see http://peach.ease.lsoft.com/archives/access-l.html . COPYRIGHT INFO: http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=ACCESS-L ------------------------------ Date: Mon, 5 Sep 2011 13:32:27 +1000 From: Donkers Family <[email protected]> Subject: Re: Conversion of text to numbers in Access 2003 I haven't been following this, but has anybody suggested a lookup table with the various ranges defined and a linked field in the data table. Easy then to run the search(s) using the lookup table. Better still to store the actual number though. Steve On 05/09/2011 13:18, Judy Hopkins wrote: > Kay, > Any chance you could get the client to rerun the data with real > numbers in all the fields? The "<-0.5" or ">0.5" should be calculated > in the query, not in the original data. > Good luck, > Judy Hopkins > > >> -----Original Message----- >> From: Microsoft Access Database Discussion List >> [mailto:[email protected]] On Behalf Of Kay >> Sent: Saturday, 3 September 2011 2:44 AM >> To: [email protected] >> Subject: Conversion of text to numbers in Access 2003 >> >> Hi Listers, >> >> Could someone offer advise on how to handle criteria and conditional >> format on a text field when you need to limit your records by a range >> of "numbers". To be more specific, the client has a text field that >> holds numbers and/or text. The do not want to record large numbers, >> but instead to write in the cell < -0.05 or >0.5 or any range of >> "number". Fine, but then they want to query based on an acceptable >> range, returning only certain records. >-0.5 and <0.5. I have tried >> various options, but cannot get the record to recognize the range. >> They also want conditional formatting to happen based on that same >> criteria. >> >> Please, any suggestions would be appreciated. >> >> Best regards, >> >> Kay >> >> -------------------------------------------------------------------------- >> >> The ACCESS-L list is hosted on a Windows(R) 2003 Server running L-Soft >> international's LISTSERV(R) software. For subscription/signoff info >> and archives, see http://peach.ease.lsoft.com/archives/access-l.html . >> COPYRIGHT INFO: >> http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=ACCESS-L >> >> -------------------------------------------------------------------------- >> >> The ACCESS-L list is hosted on a Windows(R) 2003 Server running L-Soft >> international's LISTSERV(R) software. For subscription/signoff info >> and archives, see http://peach.ease.lsoft.com/archives/access-l.html . >> COPYRIGHT INFO: >> http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=ACCESS-L >> >> ------------------------------ >> >> End of ACCESS-L Digest - 3 Sep 2011 to 4 Sep 2011 (#2011-224) >> ************************************************************* > > -------------------------------------------------------------------------- > > The ACCESS-L list is hosted on a Windows(R) 2003 Server running L-Soft > international's LISTSERV(R) software. For subscription/signoff info > and archives, see http://peach.ease.lsoft.com/archives/access-l.html . > COPYRIGHT INFO: > http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=ACCESS-L > -------------------------------------------------------------------------- The ACCESS-L list is hosted on a Windows(R) 2003 Server running L-Soft international's LISTSERV(R) software. For subscription/signoff info and archives, see http://peach.ease.lsoft.com/archives/access-l.html . COPYRIGHT INFO: http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=ACCESS-L ------------------------------ Date: Mon, 5 Sep 2011 17:38:52 +1000 From: Allan Murphy <[email protected]> Subject: Re: Conversion of text to numbers in Access 2003 Kay We have a csv file that contains rows we do not use, we use a query to make a table that gives us the required rows one of the fields contains numbers that are in a text format e.g. 73584262 to convert this string in the query we have something like this per_id:[field3]/1. In your case you may have to eliminate the < > etc. Have you tried the VAL function this will give you the numerical value of a text field? Regards Allan Murphy [email protected] -----Original Message----- From: Microsoft Access Database Discussion List [mailto:[email protected]] On Behalf Of Darryl Collins Sent: Monday, 5 September 2011 9:30 AM To: [email protected] Subject: Re: Conversion of text to numbers in Access 2003 Or to put it simply. Any 'number' in a text field is NOT a number as humans understand it, it is a text string, and as a string you cannot do maths on it. Your approach isn't going to work without using some of the other methods offered by other listers. Regards Darryl -----Original Message----- From: Microsoft Access Database Discussion List [mailto:[email protected]] On Behalf Of Kay Sent: Saturday, 3 September 2011 2:44 AM To: [email protected] Subject: Conversion of text to numbers in Access 2003 Hi Listers, Could someone offer advise on how to handle criteria and conditional format on a text field when you need to limit your records by a range of "numbers". To be more specific, the client has a text field that holds numbers and/or text. The do not want to record large numbers, but instead to write in the cell < -0.05 or >0.5 or any range of "number". Fine, but then they want to query based on an acceptable range, returning only certain records. >-0.5 and <0.5. I have tried various options, but cannot get the record to recognize the range. They also want conditional formatting to happen based on that same criteria. Please, any suggestions would be appreciated. Best regards, Kay -------------------------------------------------------------------------- The ACCESS-L list is hosted on a Windows(R) 2003 Server running L-Soft international's LISTSERV(R) software. For subscription/signoff info and archives, see http://peach.ease.lsoft.com/archives/access-l.html . COPYRIGHT INFO: http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=ACCESS-L -------------------------------------------------------------------------- The ACCESS-L list is hosted on a Windows(R) 2003 Server running L-Soft international's LISTSERV(R) software. For subscription/signoff info and archives, see http://peach.ease.lsoft.com/archives/access-l.html . COPYRIGHT INFO: http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=ACCESS-L -------------------------------------------------------------------------- The ACCESS-L list is hosted on a Windows(R) 2003 Server running L-Soft international's LISTSERV(R) software. For subscription/signoff info and archives, see http://peach.ease.lsoft.com/archives/access-l.html . COPYRIGHT INFO: http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=ACCESS-L ------------------------------ Date: Mon, 5 Sep 2011 09:37:03 -0400 From: Kay Fraone <[email protected]> Subject: Re: ACCESS-L Digest - 3 Sep 2011 to 4 Sep 2011 (#2011-224) Hi Listers, Thanks to all for your responses. I guess the question of setting the criteria would be resolved by creating an expression as a field that drops the text values and then converting the field to a number field. As far as the conditional formatting, I am not sure if you can use an expression that formats based on another field. Got to play with that. Happy Labor Day! Kay -----Original Message----- From: Microsoft Access Database Discussion List [mailto:[email protected]] On Behalf Of ACCESS-L automatic digest system Sent: Sunday, September 04, 2011 11:00 PM To: [email protected] Subject: ACCESS-L Digest - 3 Sep 2011 to 4 Sep 2011 (#2011-224) There is 1 message totaling 58 lines in this issue. Topics of the day: 1. Conversion of text to numbers in Access 2003 -------------------------------------------------------------------------- The ACCESS-L list is hosted on a Windows(R) 2003 Server running L-Soft international's LISTSERV(R) software. For subscription/signoff info and archives, see http://peach.ease.lsoft.com/archives/access-l.html . COPYRIGHT INFO: http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=ACCESS-L ---------------------------------------------------------------------- Date: Mon, 5 Sep 2011 09:30:07 +1000 From: Darryl Collins <[email protected]> Subject: Re: Conversion of text to numbers in Access 2003 Or to put it simply. Any 'number' in a text field is NOT a number as humans understand it, it is a text string, and as a string you cannot do maths on it. Your approach isn't going to work without using some of the other methods offered by other listers. Regards Darryl -----Original Message----- From: Microsoft Access Database Discussion List [mailto:[email protected]] On Behalf Of Kay Sent: Saturday, 3 September 2011 2:44 AM To: [email protected] Subject: Conversion of text to numbers in Access 2003 Hi Listers, Could someone offer advise on how to handle criteria and conditional format on a text field when you need to limit your records by a range of "numbers". To be more specific, the client has a text field that holds numbers and/or text. The do not want to record large numbers, but instead to write in the cell < -0.05 or >0.5 or any range of "number". Fine, but then they want to query based on an acceptable range, returning only certain records. >-0.5 and <0.5. I have tried various options, but cannot get the record to recognize the range. They also want conditional formatting to happen based on that same criteria. Please, any suggestions would be appreciated. Best regards, Kay -------------------------------------------------------------------------- The ACCESS-L list is hosted on a Windows(R) 2003 Server running L-Soft international's LISTSERV(R) software. For subscription/signoff info and archives, see http://peach.ease.lsoft.com/archives/access-l.html . COPYRIGHT INFO: http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=ACCESS-L -------------------------------------------------------------------------- The ACCESS-L list is hosted on a Windows(R) 2003 Server running L-Soft international's LISTSERV(R) software. For subscription/signoff info and archives, see http://peach.ease.lsoft.com/archives/access-l.html . COPYRIGHT INFO: http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=ACCESS-L ----------------------------- End of ACCESS-L Digest - 3 Sep 2011 to 4 Sep 2011 (#2011-224) ************************************************************* -------------------------------------------------------------------------- The ACCESS-L list is hosted on a Windows(R) 2003 Server running L-Soft international's LISTSERV(R) software. For subscription/signoff info and archives, see http://peach.ease.lsoft.com/archives/access-l.html . COPYRIGHT INFO: http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=ACCESS-L ------------------------------ End of ACCESS-L Digest - 4 Sep 2011 to 5 Sep 2011 (#2011-225) ************************************************************* _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/1959564963-1315349593-cardhu_decombobulator_blackberry.rim.net-1873473523-@b4.c23.bise6.blackberry ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
