>Here's a good one.... I have a table from Access that I want to get records from that are not present in an equivalent VFoxPro .DBF. Both tables have a field called SSN with a length of 11. The SSNs include the dashes.
>
>
>SELECT distinct jet.last_name,jet.first_name,jet.ssn;
>FROM jet;
>WHERE jet.ssn NOT in (SELECT ALLTRIM(ssn) FROM person)
>
>
>If I don't ALLTRIM the field on the VFP side....I get a ton of "false positives". Can somebody tell me why that two character fields, of the same length, with the same data in each, should not be equivalent between Jet and VFP?
>
>It also turns out that I can use LTRIM or RTRIM....any version of the TRIM command appears to be effective in allowing this to work correctly.
What is the result of that query:
<pre>
SELECT jet.last_name,jet.first_name,jet.ssn;
FROM jet;
LEFT JOIN Person ON jet.ssn == Person.Ssn;
WHERE Person.ssn IS NULL
WAIT WINDOWS TRANSFORM(LEN(jet.ssn))+[ - ]+TRANSFORM(LEN(Person.ssn))
WAIT WINDOWS TYPE([jet.ssn])+[ - ]+TYPE([Person.ssn])
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.