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