EXCEL SQL Query help please! 1

[Hondy]

Hi

I would like to compare a column of data I have in excel with some data from SQL db.

I have a list of IP addresses in Excel and I want to find out if they are in my SQL db.

Just to complicate things the data is stored differently in Excel and SQL -- so in my Excel the data is "192.168.123.123" and in the db it's IP1="192", IP2="168",IP3="123",IP4="123"

Is there some kind of macro/query I can use to compare the two sources?

Thanks!!

 

[xlbo]

Best bet would be to download your IP addresses from your database in a query that concatenates the fields together as per your excel format

"SELECT IP1 + '.' + IP2 + '.' + IP3 etc as IPAddress FROM Table"

You could set this up through Data>Get External Data

Once in excel, a simple vlookup will tell you if the IP address in excel is returned in the query

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

I think that that SQL should be...

SQL = "SELECT IP1 + '.' + IP2 + '.' + IP3 etc as IPAddress FROM Table"
SQL = SQL & "Where IP1 = '" & [Cell where ip1 resides] & "'"
SQL = SQL & "  AND IP2 = '" & [Cell where ip2 resides] & "'" 
SQL = SQL & "  AND IP3 = '" & [Cell where ip3 resides] & "'" 
SQL = SQL & "  AND IP4 = '" & [Cell where ip4 resides] & "'"

assuming that they are STRING values. Otherwise loose the delimiters in the where clause.

Skip,

_{I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent!}

 

[Hondy]

thanks guys