Try a literal text find as in " " ". Or maybe you can substitute the characters with "".

HTH

Old Advance Man

You can do a replace to get rid of the ". I often import data that where numbers with a "," are imported they are imported as "10,000", I then do a replace to change all to regular numbers 10000.

Abs ( numberField )

thatl may not have been clear

Replace with Abs ( numberField )

the literal text find " " " gets me a response of - no valid criteria in this request. when i tried that before i thought it may be related to the field being defined as numberic. also - the nasty symbol is always butted up against a number because the person was saying xx inches.

i don't want to use a substituiton because then the common unit for the numbers without the symbol (xx items we are counting normally) would get confused with this xx inches thing. i have to convert the xx inches to item number some time latter. then i'll be ready for the substitution. at this point i just wanted to find the records then export them. thanks for the help. sorry it may not have been clear before. hopefully it is now.

we have a field (currently defined as numeric) that sometimes has something like 23" plugged in instead of a straight number. i'd like to find those nasty inch abbreviations but all attempts have been futile. there must be some way - what is it? i've looked all over for the answer. obviously not in the right place yet. thanks.

You can't search for non-numeric characters in a field defined as a number field. FileMaker ignores them completely. If you change the field to a text field, you won't lose any data, but you will be able to search for non-numeric characters. You can search for fields containing quotes (") by entering them like you would for a calculation: """" (four consecutive quotes). When you're done you can safely change the field back to a number field.

we are closer - if i type in """" i get - no records match this request. if i type =18"""" it finds the one record that has that entry in the (now) text field. no other combination of ", blanks, @, or * seem to get me the fields that have this inch sign thing.

also if i type ="""" or =@@"""" i get a response of records found that have either a blank field and a field with a ? in it.

if i type @@"""" i get all fields with 2 digits including the ones with the inch sign.

Odd. When I tested it, the " could be anywhere in the field. Searching for """" would find all records with at least one " in them. Under storage options for that field make sure the language is set to English, not ASCII.

If that fails, there is always brute force. Make a calc field:

hasQuote(calc,number) = Position(numberField,"""",1,1)

Search for hasQuote > 0

well i learned a lot. thanks so much. i still can't do it but that's ok; there are not that many records - the ultimate brute force is to go through them one by the each.

thom you're right. i created a new file and the """" thing worked fine. the problem (and i still don't understand this part) is that the information was imported and somehow this causes the inch sign (aka ") to not be seen as a quote sign by filemaker. i tried erasing the field contents and typing the exact number i had back in. now it ("""") finds it (the string with the quote) right away. the import is convoluted - from a mac file maker file (i think version 2) to a windows (version 5) file to this new file (version 5.0).

quote:

---

thom you're right. i created a new file and the """" thing worked fine. the problem (and i still don't understand this part) is that the information was imported and somehow this causes the inch sign (aka ") to not be seen as a quote sign by filemaker.

---

My guess is that Use Smart Quotes is On in the Document Preferences.

One more thing to try:

badNumber(calc,number) = TextToNum( numberField ) <> numberField

numberField should still be a text field. Search badNumber for 1.

To eliminate this problem from your data in the future, look at this thread: http://www.fmforums.com/cgi-bin/ultimatebb.cgi?ubb=get_topic&f=16&t=000596

I think it could help you. Using this method, you could then perform your finds on the calculation, which can be whatever "standardized" format you want.

thanks for all the hints. the database is rockin' now and goes into production today. this has been my first relational database. it is replacing hundreds of spreadsheets and a couple of flat file databases. i've been amazed how tricky you have to be and how careful in the construction. you guys have been a huge help.