Data type mismatch in criteria expression for Query but not when doing a Select ... Into

[MrMom]

I have a Location database containing Street_Addr_1 and Street_Addr_2 columns. Some of the instances of Street_Addr_2 contain the same information as Street_Addr_1 and then add other information. Here's an example:
Street_Addr_1: "1944 WILLISTON RD" and Street_Addr_2: "1944 WILLISTON RD FLR 1ST STE 3"
What I need to accomplish is remove the redundant information from Street_Addr_2.


I can use "Left(Street_Addr_2, Len(Street_Addr_1) )" to give me the Left_Part_of_Street_Addr_2 and "Trim(Right(Street_Addr_2, Len(Street_Addr_2) - Len(Street_Addr_1)))" to give me the Right_Part_of_Street_Addr_2.
I have the following SQL:

SELECT Street_addr_1, Len_1, Len_2, Street_addr_2,
Left_Part_of_Street_Addr_2, Right_Part_of_Street_Addr_2,
City, State, Country
From
(

SELECT Street_addr_1, len(Street_Addr_1) as Len_1, len(Street_Addr_2) as Len_2,
Street_addr_2, Left(Street_Addr_2, Len(Street_Addr_1) ) as Left_Part_of_Street_Addr_2,
Trim(Right(Street_Addr_2, Len(Street_Addr_2) - Len(Street_Addr_1))) as Right_Part_of_Street_Addr_2,
City, State, Country
FROM Working_Location
where
Street_addr_1 is not null
and Street_addr_2 is not null
and Len(Street_Addr_1) > 0
and Len(Street_Addr_2) > 0
and Len(Street_Addr_1) + 1 < Len(Street_Addr_2)

) as wl1
Where Street_addr_1 = Left_Part_of_Street_Addr_2
Order by State, City, Street_Addr_2
;

The Where clause produces the Data Type Mismatch error. Without that condition, the query works fine and correctly identifies the two parts of Street_Addr_2.
I tried wrapping both sides of the condition with CStr() and that got rid of the Data Type Mismatch error but gave me an Invalid Use of Null error. There should be no null values because the inner select should eliminate them. Next I tried CStr(NZ( )) to replace any nulls with a 0 and then convert it to a string -- Where CStr(NZ(Street_addr_1)) = CStr(NZ(Left_Part_of_Street_Addr_2 )) -- and that brought back the Data Type Mismatch error.

Now, if I add the line "Into Temp1" just above the From line for the outer Select in the query above without using the Where clause for the outer select, it creates Temp1 with data from all rows containing a value for Street_Addr_2.

Next, I can run the following SQL:
SELECT Street_addr_1, Len_1, Len_2, Street_addr_2,
Left_Part_of_Street_Addr_2, Right_Part_of_Street_Addr_2,
City, State, Country
From temp1
Where Street_addr_1 = Left_Part_of_Street_Addr_2
Order by State, City, Street_Addr_2
;

Notice, this is identical to the original SQL replacing the inner select with temp1 as the data source.
Now, I have the follow data "1944 WILLISTON RD" populated into the Left_Part_of_Street_Addr_2 column and "FLR 1ST STE 3" populated into the Right_Part_of_Street_Addr_2.

Question, why does the original query fail but using an intermediate table succeed?
Thank you.

[orange]

You seem to have gone to a lot of work trying to parse one sample record.
Can I ask a few questions to help put your post into context, and perhaps get some facts that may get some more focused responses to help with your project?

What exactly are you trying to accomplish? --in plain English; simple terms
What is the source of the original data?
How consistent is the format of the incoming data?
What do you intend to do with the output/revised data?

Lots of times when "cleansing data, you look for patterns. Records with empty address2, or ZIP missing...or various spellings of State eg NY, N.Y., N Y, New York

[MrMom]

I have to compare location data from two different sources: billing records and property records. To compare them, I am 'scrubbing' both sets of addresses, normalizing abbreviations, etc. (For example, ' Road' becomes ' RD' making sure I don't turn 'Broadway' into 'BRDWAY'.) The objective of this step is to update Street_Addr_2 by removing that portion that is redundant of Street_Addr_1, setting it to the value in Right_Part_of_Street_Addr_2. I've already set Street_Addr_2 to Null in those rows where it is equal to Street_Addr_1.

[ssanfu]

I agree with orange - you have gone to a lot of work trying to parse one sample record.

What I need to accomplish is remove the redundant information from Street_Addr_2.

If
Street_Addr_1: "1944 WILLISTON RD"
and
Street_Addr_2: "1944 WILLISTON RD FLR 1ST STE 3"

why not run an Update query - something like: (untested)

UPDATE Working_Location SET Working_Location.Street_Addr_2 = Trim(Replace(Street_Addr_2, Street_Addr_1,""));

Seems a lot simpler to me.... maybe I am missing something????

[MrMom]

I provided the one sample address to illustrate the nature of the problem. For the first phase of this project, I am dealing with over 100,000 addresses. Phase 2 has already been approved and will triple that number. By the time it is complete, it may be near 800,000 distinct addresses.

I had not considered Replace simply because on my visual scan of the data all of the redundancy of Street_Addr_1 occurred at the beginning of Street_Addr_2. This does not mean it couldn't occur elsewhere within the field, though. I cannot lose the remaining portion of Street_Addr_2 so I will look at Replace first thing Monday morning.

Both data sources are external to my organization and I have no control or influence over how they enter the data. But, I do have to be able to identify matching addresses between the two sources. So I am scrubbing the data from each data source to correct a variety of issues; for example, I have encountered eight representations of 'Municipal', one valid and seven misspellings or bad abbreviations. Once I have the data scrubbed, I will need to run the street addresses through a USPS Delivery Point Validation third party application to resolve issues that cannot be handled with scrubbing. For example, while I have "1944 Williston Rd" in some records, others show it as "1944 Williston St". A spot check against Google Maps shows that "Rd" is correct and "St" needs to be updated to reflect "Rd" instead. I cannot spot check 800k addresses manually against Google Maps.

And just for fun, a totally separate issue with the data is that many of the addresses reflect rural locations and one of the sources prefers to use Latitude / Longitude to identify a rural location instead of a street address. So, all of the records with a street address but no latitude / longitude data will need to be processed through yet another third party application to get the latitude / longitude for the street address. Then I will have to perform proximity calculations to find out how 'close' any two locations are in the physical world. I may have to do the proximity calculations outside of Access because the algorithms I have found for that calculation require the ACos (arc cosine) function which exists in Excel, but not Access 2010.

After I test out Replace on Monday, I will post an update. But, I still do not understand why the where clause produces an error in the original query but works successfully when I insert the data into an intermediate table and run the query against that.

Thank you.

[ssanfu]

It could be because you aliased the subquery (as wl1) but didn't use fully qualified field names. There are 2 references to the field names - the main query and the sub query. Access might have gotten confused.

You could try this: save the sub query as "qryLocationSub":

SELECT Street_addr_1, len(Street_Addr_1) as Len_1, len(Street_Addr_2) as Len_2,
Street_addr_2, Left(Street_Addr_2, Len(Street_Addr_1) ) as Left_Part_of_Street_Addr_2,
Trim(Right(Street_Addr_2, Len(Street_Addr_2) - Len(Street_Addr_1))) as Right_Part_of_Street_Addr_2,
City, State, Country
FROM Working_Location
where
Street_addr_1 is not null
and Street_addr_2 is not null
and Len(Street_Addr_1) > 0
and Len(Street_Addr_2) > 0
and Len(Street_Addr_1) + 1 < Len(Street_Addr_2)

Then create another query (the main query) thus:

SELECT Street_addr_1, Len_1, Len_2, Street_addr_2,
Left_Part_of_Street_Addr_2, Right_Part_of_Street_Addr_2,
City, State, Country
From qryLocationSub
Where Street_addr_1 = Left_Part_of_Street_Addr_2
Order by State, City, Street_Addr_2;

UPDATE: I created a table and added 1 record. (Street_Addr_1: "1944 WILLISTON RD" and Street_Addr_2: "1944 WILLISTON RD FLR 1ST STE 3" | City, State and Country is where I live)
I tried your original query and did not have any errors.
I tried using the 2 query method I suggested and did not get any errors.
I tried using fully qualified fields and did not get any errors.

My conclusion is that there is something in your table that is causing the SQL to fail..... the trick is determining what is causing the error.

[MrMom]

I shall try this approach also, along with fully qualified field names. Thanks again. (And I like your quote in your signature block!)