Need help comparing strings 1

[ribhead]

I am connecting to an AS400 db from XL. I am bringing in part numbers that are based on a department number. The problem is the department numbers aren't just numbers they have a dash in them i.e. 121-5224,137-5417 121 and 137 are the department numbers. I am tring to use the Like statement but to avail. When I run this I get nothing yet I know for a fact that these departments exist. I am extremely new to this so please be gentle and if you could keep it simple I would appreciate it.



Option Explicit
Sub Giveitatry()
'Connecting to AS400 in XL
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strSQL As String
Dim r As Long
Dim fld As Field
cnt.Open "DSN=AMES01;"
strSQL = "SELECT DISTINCT MFRFMR02 FROM DPNEW WHERE MFRFMR08 Like '121-5224'"
Set rst = cnt.Execute(strSQL)
r = 1
Do While Not rst.EOF And Not rst.BOF
For Each fld In rst.Fields
Cells(r, 1) = fld
Next fld
rst.MoveNext
r = r + 1
Loop
End Sub

Thanks, Rib

Bartender:Hey aren't you that rope I threw out an hour ago?

Rope:No, I'm a frayed knot.

 

[mrdenny]

This should be in the AS400 forum, but here goes anyway.

You don't need the like statement. You just need to use the equals operator.

Sub Giveitatry()
'Connecting to AS400 in XL
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strSQL As String
Dim r As Long
Dim fld As Field
cnt.Open "DSN=AMES01;"
strSQL = "SELECT DISTINCT MFRFMR02 FROM DPNEW WHERE MFRFMR08 [COLOR=red]=[/color] '121-5224'"
Set rst = cnt.Execute(strSQL)
r = 1
Do While Not rst.EOF And Not rst.BOF
For Each fld In rst.Fields
Cells(r, 1) = fld
Next fld
rst.MoveNext
r = r + 1
Loop
End Sub

Here is an example of a SQL statement using the LIKE operator.

select distinct MFRFMR02 
from DPNEW 
WHERE MFRFMR08 like '121%'

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[ribhead]

Denny,

Thanks a bunch for the help. I can't find an AS400 forum in my list.

Thanks, Rib

Bartender:Hey aren't you that rope I threw out an hour ago?

Rope:No, I'm a frayed knot.