Not Equal Records

[kitty77]

How can I find records where two fields are NOT equal? The two fields have different values except at the end.
That's where the NOT equal value is and what I'm looking for.

file:///B:\ABC\Records\30453.pdf (field1)
\\SERVER\ABC\Records\Results\30455.pdf (field2)

 

[arnelgp]

Mid(field1, InstrRev(field1, "\")+ 1) <> Mid(field2, InstrRev(field2, "\")+ 1)

 

[kitty77]

Mid(field1, InstrRev(field1, "\")+ 1) <> Mid(field2, InstrRev(field2, "\")+ 1)

In a query? Could you send me an example? Thanks!

 

[arnelgp]

SELECT Table1.ID, Table1.field1, Table1.field2
FROM Table1
WHERE Mid(field1, InstrRev(field1, "\")+ 1) <> Mid(field2, InstrRev(field2, "\")+ 1)

 

[kitty77]

SELECT Table1.ID, Table1.field1, Table1.field2
FROM Table1
WHERE Mid(field1, InstrRev(field1, "\")+ 1) <> Mid(field2, InstrRev(field2, "\")+ 1)

But where do I create this? A query? Can you send me an example? Do I use the dup wizard?

 

[arnelgp]

sample. Query1.

 

[kitty77]

That works! I have some records that don't have a value in field1. So technically, it would not be equal.
How can I include those too? I'm getting an error when I have those records.

Thanks!

 

[arnelgp]

change the query to:

SELECT Table1.ID, Table1.field1, Table1.field2
FROM Table1
WHERE (((Mid("\" & [field1],InStrRev("\" & [field1],"\")+1))<>Mid("\" & [field2],InStrRev("\" & [field2],"\")+1)));

 

[kitty77]

change the query to:

SELECT Table1.ID, Table1.field1, Table1.field2
FROM Table1
WHERE (((Mid("\" & [field1],InStrRev("\" & [field1],"\")+1))<>Mid("\" & [field2],InStrRev("\" & [field2],"\")+1)));

Perfect!!!