Drop the end of an ip address

does anyone know how to drop the last part of an ip address? I've been searching various sites for string functions and havn't come up with anything that works. What I want is something that will "find the third occurance of a "." and then return everything to the left of it"

I've tried various types of InStr and even found a SUBSTRING_INDEX function, but apparently it's only for mysql.

i'm using sql server and linking tables thru access

fiskyou can do reverse and then a right with a nested charindex. with another reverse at the end.

or you can stuff with a nested charindex.|||i'm getting "undefined function" error.

SELECT dbo_AeXInv_AeX_AC_TCPIP.WrkstaId, REVERSE(dbo_AeXInv_AeX_AC_TCPIP.[IP Address]), dbo_AeXInv_AeX_AC_TCPIP.[Host Name]
FROM dbo_AeXInv_AeX_AC_TCPIP;

the reverse string function seems pretty simple, but I must be missing something.

fisk|||Same error message for charindex

SELECT dbo_AeXInv_AeX_AC_TCPIP.WrkstaId, charindex('.', dbo_AeXInv_AeX_AC_TCPIP.[IP Address]), dbo_AeXInv_AeX_AC_TCPIP.[Host Name]
FROM dbo_AeXInv_AeX_AC_TCPIP;

I just ran this in sql analyzer and it worked fine. Any idea why i'm getting this error in access?

fisk|||microsoft access does not use the same functions as microsoft sql server

i mean, you would think it would, what with sql standards and all, but you gots to remember, microsoft bought access from a different company than it bought sql server from

:)

as long as we're playing loosey-goosey with database platforms, why don't you run it on mysql? it has a wonderful function called SUBSTRING_INDEX

select substring_index(`IP Address`, '.', 3) as stuff_before_the_third_period
from dbo_AeXInv_AeX_AC_TCPIP|||I would have thought that access would just pass the sql statement to sql server and let it figure it out. I guess not.|||it will only do that if you have declared it as a pass-through query|||Not quiet. For linked tables, Access will attempt to format the statement as a pass-through query on its own, but if it cannot (and it cannot handle anything more than moderately complex) then it will execute it locally (and ineffeciently).|||Yep, that's pretty much what's happening.

Oh well, thanks anyway guys.

fisk|||For linked tables, Access will attempt to format the statement as a pass-through query on its ownholey moley

i've been using access with linked tables for about 10 years, and i didn't know that

so, what does it do when the sql contains an access function like InStr()?

it barfs, right?

so when you say "moderately complex" you really mean "anything even slightly more complex than SELECT foo FROM bar WHERE qux=937", eh?|||It's somewhere in the documentation. I don't know how complex it can get before it has to run it locally. It may depend upon whether it has embedded functions, or it may depend upon whether it is able to succesfully translate MS Access SQL to TSQL.|||You should try some simple queries and then monitor server activity to see what gets sent through.