find " as in the abbreviation for inches?

[Steven H. Blackwell]

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

HTH

Old Advance Man

[bobsmith]

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 )

[bobsmith]

thatl may not have been clear

Replace with Abs ( numberField )

[fireflyrsmr]

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.

[fireflyrsmr]

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.

[Thom]

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.

[fireflyrsmr]

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.

[Thom]

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

[fireflyrsmr]

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).

[The Bridge]

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.

[Thom]

One more thing to try:

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

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

[DykstrL]

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.

[fireflyrsmr]

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.