Search results for query: *

Hi All; I am trying to use Access to to get the IP Address of multiple PC's via NSLookup. I am using the following code to output the information to a txt file; When ever I run this code from an Access module I only get the first two lines of output.dblRetVal = Shell("cmd /c nslookup.exe...

Oh the power of the Almighty JOIN!! I bow before thee. I ended up using the following: DELETE tblTFN.* FROM tblTFN LEFT JOIN tblTempTFN ON (Replace(Replace(Replace(tblTempTFN.TFN, "-", ""), "(", ""), ")", ""))=tblTFN.TFN WHERE tblTempTFN.TFN is Null; The difference between using replace and...

Nope, wouldn't be often. This is a side project for me so no hard time frame. In these cases I like to at least try to build it as well as I can, that way I can hopefully learn something useful in the process.

Thx Andy, I wasn't thinking about putting it directly in the query. I copy & pasted your query above to run a test. On the upside, it does work even if the VBA editor had been opened. On the downside, the UDF I posted above is about 900% faster. (6.2 seconds vs 58.6 seconds) I'll try using a...

Easier? Sure, but wouldn't that be slower since it needs to check every character to see if it is one of the characters that needs replaced?

Thx for the reply. That makes sense for why it takes so long while the VBA editor is open. (Your formula would make it about 800k iterations of the function.) I'm still not sure why it would go sooooo much faster if the VBA editor has not been opened.

I have a query with a custom function that works fine as long as I have not opened up the VBA editor. If that is/has been opened the query loops indefinitely calling the function with new data over and over looping through all the records and starting over again. The function is a simple string...

Hi All, I have an Access front end that I use to query an Oracle 10g database. After installing Oracle XE (I've tried both 10g and 11g) the Access front end is now longer able to connect and gives me a 3151 error. Uninstalling corrects the problem. Any ideas why this might be happening?

Wow! That's about the ugliest thing I've ever seen, lol. Works great though. Thanks a bunch. It never even dawned on me to just hard code the sheet names.

Hi All, I have a list of phone numbers on the seventh worksheet of a workbook. These same phone numbers appear in multiple worksheets (each number appearing only once across the first six worksheets.) I'm trying to figure out a formula (tried using VLOOKUP with no success) that will lookup...

Thx Carp, TRIM worked.

Hi Turkbear Unfortunately since the dial_digit field has entries with '#' and/or '*' I get an invalid number error when trying to convert dial_digit to a number.

Thx for the reply cmmrfrds. The field can be two or three digits. I tried (TO_CHAR (spd_num, '99') to see if that would return results for at least the two digit numbers, but still got zero results. The fact that (TO_CHAR (149) does return any records while just '149' does makes me wonder if...

Hi All, I am running a query with a subuery in an IN clause and am getting back zero results. The query is; SELECT COUNT(*), dial_digit FROM calldetail WHERE dial_digit IN (SELECT TO_CHAR (spd_num) FROM speed) GROUP BY dial_digit ORDER BY dial_digit If I run just the subquery I get the...

Hi All, I'm new to Visio and am trying to figure out how I can mimic the expanding functionality of the Legend shape. I have multiple custom properties for a custom shape would like to be able to assign a value to any combination of the properties and have only those properties display. Any...

Thanks for your help Mufasa. The following query works perfectly. SELECT msg_num, msg_id FROM announce MINUS (SELECT ann.msg_num, ann.msg_id FROM announce ann LEFT OUTER JOIN dnis d ON d.ct_ann_num = ann.msg_num HAVING COUNT(d.ct_ann_num) > 0 GROUP BY ann.msg_num, ann.msg_id UNION SELECT...

Thanks for the reply Mufasa. Unfortunately all three tables structures are different. Could I do something similar to your code since the two fields I want to return are identical?

Hi All, I'm pretty sure what I'm looking to do is not too complicated, but can't figure out how to write the query. I'm trying to get all records from one table that does not show up in two other tables. I currently have two queries similar to the following: SELECT tableA.item1...

Hi All, I'm trying to find a way to either catch ADO errors or test an ADO connection with VBA. The standard "On Error GoTo" statements don't catch them. Thanks, Tim

Hi All, I'm trying to setup a query in Access db "A" that will pull data from a table in access db "B". I have used pass-through queries and queries on the fly with VBA in Access to query SQL server db's, but not Access db's. I believe all I need is to figure out the connection string to allow...