Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO Function
Thanks to all. I have learned quite a bit today. The code that follows is the
code that works. Function GetItemDesc(ItemNumber As String) Set conn = CreateObject("ADODB.Connection") conn.ConnectionString = "DSN=AS400" conn.Open strQuery = "SELECT JFITDS FROM SCDBFP10.USIAJFPF WHERE JFITEM='" & ItemNumber & "'" GetItemDesc = conn.Execute(strQuery).Fields(0) Set conn = Nothing Set StrConn = Nothing End Function "keepITcool" wrote: your function doesnt return a value.. so you get the #value! error. try like: GetItemDesc = conn.Execute(strQuery).Fields(0) connExecute returns a recordset object.. normally you 's "catch that" like set oRST = conn.Execute(str) since your query retrieves 1 field only i've "piped" it into the string above. note that the recordset's fields collection is ZERO based! http://www.devguru.com/Technologies/...ado_intro.html has very good references for ADO and JetSql keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "?B?RGVhbiBVbmRlcndvb2Q=?=" wrote: First. Sorry about the double post. Now the code looks like the following and it still returns a "#VALUE" in the cell. Thanks for the response. As you can tell I am stumped. Function GetItemDesc(ItemNumber As String) Set conn = Server.CreateObject("ADODB.Connection") strConn = "DSN=AS400" conn.Open strConn strQuery = "SELECT USIAJFL0.JFITDS FROM AS400.SCDBFP10.USIAJFL0 WHERE USIAJFL0.JFITEM='" & ItemNumber & "'" conn.Execute (strQuery) End Function "Harald Staff" wrote: "Dean Underwood" skrev i melding ... I am new to witing VBA and am struggling with writing a function. The following code does not work. What could be wrong. strQuery = "SELECT USIAJFL0.JFITDS FROM AS400.SCDBFP10.USIAJFL0 WHERE USIAJFL0.JFITEM=ItemNumber" Hi Dean You are searching for the literal text "ItemNumber", not the variable value of ItemNumber. Bringing us to WHERE USIAJFL0.JFITEM=" & ItemNumber This should work if the JFITEM table is numerical. If it's a text field however then it needs to be enclosed by single quotes like this WHERE USIAJFL0.JFITEM='" & ItemNumber & "'" HTH. Best wishes Harald |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Data Validation/Lookup function does function correcty | Excel Worksheet Functions | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
User-Defined Function pre-empting Built-in Function? How to undo???? | Excel Programming |