Select Statement

jmss66 · September 9th, 2009, 03:14 PM

I have two tables. The first table(table1) has addresses (duplicates too) which I am reading to write into the second table(table2). What I am doing is after reading the next record for table1 I check table2 and see if the address already exists. I am doing this by reading table2 and checking if it is there and if it is then I go to the next record on table1.

My problem is in my select statement. Seems like it is not seeing the duplicates so it goes on and writes the record to table2.

Can someone please help me figure out what I am doing wrong. Here is a snipet of my code.

Code:

         Set rsTempRecord = New ADODB.Recordset
         mySQL = "SELECT * FROM ADDRESS_MULTI WHERE " & strIdentifierSSN & " = " & intPersonSSN & " AND ad_address1 = '" & rsAddress!Address_1 & "' AND ad_address2 = '" & rsAddress!Address_2 & "' AND ad_city = '" & rsAddress!City & "' AND ad_zip = " & Str(rsAddress!Zip)
         rsTempRecord.CursorLocation = adUseClient
         rsTempRecord.CursorType = adOpenDynamic
         rsTempRecord.LockType = adLockOptimistic
         rsTempRecord.Open mySQL, adoSQLConnection
 
         If Not rsTempRecord.EOF Then
            blnDuplicate = True
         End If
 
        if not blnduplicate then
          'writing record to table2
         .....
        end if

Is there a better way to handle this logic?

Thanks

gbianchi · September 9th, 2009, 03:33 PM

Can you post and output of the specified query..
this:
strIdentifierSSN & " = " & intPersonSSN,
sounds very strange for a query...

jmss66 · September 9th, 2009, 04:10 PM

?mysql
SELECT * FROM CT_ADDRESS_MULTI WHERE SSN1 = 466923781 AND ad_address1 = '20011 Doherty Circle' AND ad_address2 = '' AND ad_city = 'Katy' AND ad_zip = 774490000

Old Pedant · September 9th, 2009, 05:45 PM

And you really used NUMERIC fields for SSN1 and ad_zip??

Not VARCHAR?

Perhaps ad_address2 is NULL instead of a blank string. NULL and a blank string are *NOT* the same thing, to any SQL query.

jmss66 · September 9th, 2009, 06:17 PM

I had to test for null in all the fields and it worked.