I am battling with a poorly organised database. My current problem relates to the address records (UK kind).

The old database describes people from several countries, Europe and further afield, and has fields for

House Name
Street
Village
Address
County
PostCode or Country
Country

Commonly these fields contain nothing with the Address all in the Address field.

I am attempting to convert to my preferred format

Address1
Address2
Address3
PostalTown
Postal Code
County
Country

I am using update queries to achieve this. My process is to write a query to produce a calculated field in the style I want to check my logic. When that is OK, I use it is an update rule.

So I have put some Address entries into Address1. Because of the variable usage of the fields in the old database I then need to use something like

IIf
(StrComp([_Addresses]![Address1],[_Addresses]![Address],1) And Len([_Addresses]![Address2])<2 And Not IsNull([_Addresses]![Address])
,[_Addresses]![Address]
,"N")

which does not get the desired result. The problem lies in the string comparison. To check it out I simplify things like

IIf
(StrComp([_Addresses]![Address1],[_Addresses]![Address],1)
,[_Addresses]![Address]
,"N")

which still does not get the desired result. But variably. For example the string

1 Montpelier Row

copied from Address to Address1 is correctly identified as being the same in both fields but these below are not.

1 Church Farm Cottages
23 The Spinney
4 Thellusson Lodge

I have tried variations of StrComp, e.g. <> and Not = but with no impact on the result
The compared strings are always both the same length. The compared strings are always one created by an Update action from the other.

There are 33 of these problem records in about 6500 records.

All help gratefully received from a frustrated fiddler.

Thanks

StrComp() does NOT return a Boolean ... it returns a -1, 0 , or 1.
-1: means string1 is less than string2
0: means string1 is equal to string2
1: means string1 is greater than string2

So ... if you use it in place of a boolean, anything other than 0 will be views as if its TRUE ... so with your sample of:

IIf
(StrComp([_Addresses]![Address1],[_Addresses]![Address],1)
,[_Addresses]![Address]
,"N")

The value of "N" will be returned IF the fields are EQUAL, since StrComp() will return a 0 if the fields are equal, and a 0 is interpreted as False. As a side note, in situations like this, I will wrap my arguments with a Trim()

So ... it seem you can re-arrange your IIF() arguments a bit ...

IIf
(StrComp(Trim([_Addresses]![Address1]),Trim([_Addresses]![Address]),1)
,"N"
,Trim([_Addresses]![Address]))

Or ... explicitly compare StrComp() to 0.

IIf
(StrComp(Trim([_Addresses]![Address1]),Trim([_Addresses]![Address]),1) = 0
,Trim([_Addresses]![Address])
,"N")

Thank you Brent.

My problem is that when the two fields are one copied from another they do not appear to be equal. But for only 33 of my records. Some 150 that are supposed from my test to give a different result do so correctly (well the 15 I checked did) and the others of the 6000 give N since they are equal. But I have 33 which do not give N when they should.

I tried adding Trim but it made no difference to the overall result. When I use Len(A)=Len(B) they all show as being the same length although the direct comparison says they are different strings.

Could you shed any further light on the problem?

Thank you again

Can you post a sample database with the data in question? ... My initial thought, was that you were using StrComp() incorrectly, but your subsequent reply seems to indicate otherwise .... so ... maybe you have something like a non-breaking space in one string, but not the other? ... or something like that.

May I ask what you are using StrComp() instead of just testing the equality of the fields? ... seems like a needless call, especially since letter case is not used in the comparison...

I shall try to post a sample.

The sample I have constructed contains the Address table and a Query which runs on it. If you open the Query the first line which is incorrect is 'Elm Cottage"

Why use StrComp()? In desperation really. I had tried all kinds of other things to achieve that goal, so I wondered if Access had a special way of dealing with this. Not in my previous experience that I copy 5500 fields into other fields and when I compare them just a few do not match. Hence frustration.

Thanks for your help

Anyone got any suggestions?

The slice of my database shows all the problems.

The steps are

1. Copy data from Address to Address1 (only if Address1 does not already contain something)
2. Compare Address and Address1 for equality.
2.1 Mostly the correct result is obtained. N if there is equality, or a String if something needs to be copied.
2.2 Some show the value of Address when it is the same as Address1. THIS IS THE ERROR

Why are two entries being seen as different when one is a copy of the other?

Thank you for your help

To experiment I made a copy of the table causing problems and worked on that.

The expression causing the problems was copied as an updtate instruction and modified so that the N did not overwrite existing values. The resulting table was filtered to identify those records which had two identical fields. The 33 wrong records were identified accurately. So they alone were updated to "" which produces the correct outcome but a very long way round.

If anyone can explain the problematic comparison it would be appreciated.

Thanks

It seems that if you increase the row height of the row set returned by your query, you will see that the values between Address and Address1 are indeed different ...

For Example:

Address1:
Merddyn Hywel
Glan y Coed Park
Dwygyfylchi
Penm

Address:
Merddyn Hywel
Glan y Coed Park
Dwygyfylchi
Penmaenmawr

Upon initial examination of the returned results with a single line row height you would think the two are the same, but they are not and that is revealed with a row height change.

If this is not it, then please tell me an address1/address combination that is producing the suspected problem.

Thank you.

As I have often told my pupils, it is amazing what users can get to put into databases. And my problem is of my own causing since I have reduced the size of the Address fields on the grounds that no one would need 255 characters.

Thanks again.

You are most welcome! ...

----------------

If you Click Here you will be linked to a post that has some code in it that I will often use to remove unwanted white space from field values. The post has a couple functions in it, but the one that helps me the most in situations like this is RemSpecial().



Will strip out all carriage returns, line feeds, and other non-printable characters, yet leave a space between words. I use it frequently for long string comparisons. It may help you out with your task...

See ya on UA!

Thank you again

That has made life much easier than the Cut and Paste task it looked like!