Two fileds, same data, query won't match them

[puforee]

I am working on a form and I want to create a sub-form linking a field from each form. Both fields are stored as short text with the same formatting.

One field is entered manually,on the main form, by the user (Into table Audit_History) and the other field is derived from a large string using a query (called breakout). Both fields show "TII" as the data that I want to link on, and both are exactly 3 characters long. Both the breakout query field and the Audit_History table field show left justified when opened. The actual field names are different from one another but the TII data is the same.

The problem: When I query with the Audit_History table and the breakout query linked on the like fields I do not get any data. I know for a fact that TII is in the Audit_History table and in the breakout query. The breakout field is this: Left([Org],3) where Org is the long string and it returns TII.

I have tried everything I can think of and can't get these two fields to link. I even created a table, with the breakout query and then queried that table with the Audit_History table. No joy.

Is my breakout query not really giving me TII as a short text? When I make a table with it, short text is the data type.

I am so confused...help.

 

[SkipVought]

Hi,

What is your actual SQL code?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

Did you try to compare both texts character by character, with the ASCII values?
Something like:

Dim i As Integer
Dim s As String

s = "TII"

For i = 1 To Len(s)
    Debug.Print Asc(Mid(s, i, 1))
Next i

Obviously they are not equal for some reason...


---- Andy

There is a great need for a sarcasm font.

 

[puforee]

Skip.I
I am testing this in a query to simplify matters.
SQL:

SELECT Audit_History.Airline, Breakout.Cust
FROM Audit_History INNER JOIN Breakout ON Audit_History.Airline = Breakout.Cust;

Andy..not quite sure where to put this code and how to run it. A little help? Do I put it in a stand alone module?

Thanks to both of you.

 

[puforee]

I found the issue. I ran the query again without any joins. This returned everything from the Audit History table and the single item from the breakout query.

So, 32 rows from the first and 32 roles from the second. The breakout showed 32 "TII"s next to the 32 items from the table. One row showed TII next to TII as expected.

I then exported the results to excel. When I looked at the Row that showed TII in column A and TII in column B here is what I saw. TIITII in column A and TII in column B. When I selected the TIITII cell and looked at the formula bar in excel it showed:
TII
TII

So, in Access in the Audit History table it looks like there is a return in that field but I could not see it. So, I deleted the data in the table and retyped TII. This solved the problem. Strange but True.

Thanks for your help.