How to compare recordsets in access vba

[Dan2kx]

1. If it will be possible to compare Non-Numeric Strings, is the comparison Case-Sensitive, namely:
   

   NeoPa = NEOpa evaluates to TRUE       'Case-Insensitive
   NeoPa = NEOpa evaluates to FALSE      'Case-Sensitive

Just a quick thread hijack, i couldn't see it (if present in the thread) but IS it possible to compare on a case sensitive basis?

i.e.

if me.txtBox = Dlookup("Blah",Blah",....) Then

Thanks, and sorry for the intrusion

Dan

[NeoPa]

but IS it possible to compare on a case sensitive basis?

Yes.

Option Compare Binary

Press F1 when the word Compare is selected and the relevant Help page will give you the full details.

[ADezii]

Just a quick thread hijack, i couldn't see it (if present in the thread) but IS it possible to compare on a case sensitive basis?

i.e.

if me.txtBox = Dlookup("Blah",Blah",....) Then

Thanks, and sorry for the intrusion

Dan

Debug.Print StrComp("Philadelphia","Philadelphia",vbBinaryCompare)

'evaluates to 0 (String1 = String2)

Debug.Print StrComp("Philadelphia","philadelphia",vbBinaryCompare)

'evaluates to -1 (String1 < String2)

Debug.Print StrComp("philadelphia","Philadelphia",vbBinaryCompare)

'evaluates to 1 (String1 > String2)

[ADezii]

Yes.

Option Compare Binary

Press F1 when the word Compare is selected and the relevant Help page will give you the full details.

Just be aware that while this may be default behaviour it is probably not wise to rely on this setting being used universally in your code.

Just for curiosity, I thought that you didn't rely on Option Compare Settings (LOL).

[OldBirdman]

This thread needs to focus on comparing 18K records against a like number, where each record in the first set is compared to every record in the second set. Someone calculated this is "324,000,00 0 iterations would be approximately 8.6 hours". This is 18000^2.=324,00 0,000.
If each table were sorted on the 4 fields, then each might be read sequentially with the lower one read and compared to the other.
Another approach would be to combine the 4 fields into a single field with some algorithm, and then find which had this combined field that was not in the other table, a simple SQL problem, I believe.
Leave the 4th field as text for the moment. Whatever algorithm is developed can take care of this. As long as it is only equal vs. not-equal, then only case sensitivity has to be considered. The field must be standardized (Trim(), remove double blanks, etc).

[Dan2kx]

Debug.Print StrComp("Philadelphia","Philadelphia",vbBinaryCompare)

'evaluates to 0 (String1 = String2)

Debug.Print StrComp("Philadelphia","philadelphia",vbBinaryCompare)

'evaluates to -1 (String1 < String2)

Debug.Print StrComp("philadelphia","Philadelphia",vbBinaryCompare)

'evaluates to 1 (String1 > String2)

Looks good thanks, would be more easy to control i presume than than Option Compare Binary

Thanks again, ill let you get back to solving the OP

[ADezii]

Looks good thanks, would be more easy to control i presume than than Option Compare Binary

Thanks again, ill let you get back to solving the OP

What I like about this approach is that it makes no assumptions whatsoever about the Option Compare Setting on any given Access Database, in fact, it doesn't matter what it is.

[NeoPa]

Just for curiosity, I thought that you didn't rely on Option Compare Settings (LOL).

Indeed not.

However, when asked a question pertaining directly to the Option Compare setting I have no problem answering it. I still wouldn't recommend relying on it generally.

What I like about this approach is that it makes no assumptions whatsoever about the Option Compare Setting on any given Access Database, in fact, it doesn't matter what it is.

I agree unreservedly. This is the method I'd use if I needed to.

[NeoPa]

This thread needs to focus on comparing 18K records against a like number, where each record in the first set is compared to every record in the second set.

If you look back to post 15 you'll see this has already been covered OB. We can only offer advice. Force-feeding is not an option.

Starting from the right SQL will mean that the clever VBA coding is not required.

[atksamy]

Yes, But i doubt sql in access will be able tohandle it. the query will pick out 18000*18000 records and i find that it crashes at some point.

[NeoPa]

You're clearly not talking of the same SQL I am. Did you read post #15 and the preceeding post (FishVal) that it referred to?

[atksamy]

i think we are talking about the sql in post no 10.
When i try it the query runs for more than 8 to 10 hours and then crashes

[atksamy]

Is it possible to do some kind of paralle computing in VBA to reduce the time frame.

[FishVal]

You may try to join the tables in portions, e.g. the whole table1 and 100 first records from table2, then next 100 records and so on appending found matches to a separate table.

[FishVal]

Additionally, you may cache tables data to arrays and perform search on data in memory. This could be faster, however I don't guarantee it.