ADODB Recordset Query Problem.

[kashif.special2005]

Hi,</SPAN>

I have a table in ms-access like below</SPAN>

Associate_Name</SPAN></SPAN>	Region</SPAN></SPAN>	Domain</SPAN></SPAN>	Role</SPAN></SPAN>
Steve</SPAN></SPAN>	East</SPAN></SPAN>	DB</SPAN></SPAN>	BOA</SPAN></SPAN>
Jhonson</SPAN></SPAN>	East</SPAN></SPAN>	DB</SPAN></SPAN>	BIO</SPAN></SPAN>
Josh</SPAN></SPAN>	East</SPAN></SPAN>	DB</SPAN></SPAN>	EBOA</SPAN></SPAN>
Derek</SPAN></SPAN>	East</SPAN></SPAN>	DC</SPAN></SPAN>	BOA</SPAN></SPAN>
Haroon</SPAN></SPAN>	East</SPAN></SPAN>	DC</SPAN></SPAN>	EBOA</SPAN></SPAN>
Aman</SPAN></SPAN>	East</SPAN></SPAN>	DC</SPAN></SPAN>	BIO</SPAN></SPAN>

<THEAD>
</THEAD><TBODY>
</TBODY>

I want to take Associate_Name in recordset, and I am using below code</SPAN>

Sub Fetching_Data()</SPAN>

Dim rs As ADODB.Recordset</SPAN>
Dim str_sql As String</SPAN>

str_sql = "select [Associate_Name] from Associates where ([Role] like '*BOA*' Or [Role] Like '*EBOA*')" & _</SPAN>
"And [Domain]='" & Me.cmbdomian & "'" & "And [Region]='" & Me.cmbregion & "'" & "group by [Associate_Name] order by [Associate_Name];"</SPAN>

Set rs = New ADODB.Recordset</SPAN>

With rs</SPAN>
.ActiveConnection = CurrentProject.Connection</SPAN>
.CursorType = adOpenKeyset</SPAN>
.LockType = adLockOptimistic</SPAN>
.Open str_sql</SPAN>
End With</SPAN>

If Not rs.EOF Then</SPAN>
rs.MoveLast</SPAN>
Debug.Print rs.RecordCount</SPAN>
End If</SPAN>

It is not giving me any error message but no record showing in recordset, but when I am manually running str_sal query it is giving me result like below</SPAN>

Associate_Name</SPAN></SPAN>
Josh</SPAN></SPAN>
Steve</SPAN></SPAN>

<THEAD>
</THEAD><TBODY>
</TBODY>

"select [Associate_Name] from Associates where ([Role] like '*BOA*' Or [Role] Like '*EBOA*')" & _</SPAN>
"And [Domain]='" & Me.cmbdomian & "'" & "And [Region]='" & Me.cmbregion & "'" & "group by [Associate_Name] order by [Associate_Name];"</SPAN>

Will show</SPAN>

select [Associate_Name] from Associates where ([Role] like '*BOA*' Or [Role] Like '*EBOA*')And [Domain]='DB' And [Region]='East' group by [Associate_Name] order by [Associate_Name];</SPAN>
Please help me to solve this problem.</SPAN>

Thanks</SPAN>
Kashif</SPAN>

 

[revans]

I don't see your problem. You are telling Access to return records where the role is like BOA or like EBOA that are also marked DB and East. According to your sample data, it seems to have done so.

The only issue I see being a potential problem is that if you were to run a query against this code with a where clause like this

WHERE [Role] like '*BOA*'

you will also return those records with EBOA as well, which might not be what you want. Otherwise, I don't know what to tell you.

hth,

Rich

 

[Norie]

How are you checking the recordset?

Try this.

WhileIf Not rs.EOF Then
  Debug.Print rs.Fields(0)
  rs.MoveNext
Wend

 

[kashif.special2005]

Thank you so much for reply

I found the solution on the net

wild card character like * will not work in ADODB Recordset query, it will work in DAO only

Wild card character like % will work in ADODB Recordset query instead of *.

Adodb not working query.

"SELECT Associates.Associate_Name FROM Associates WHERE Associates.[Region]='East' AND (Associates.[Role] Like '*BOA*' Or Associates.[Role] Like '*EBOA*') AND Associates.[Domain]='DB' ORDER BY Associates.[Associate_Name];"

Adodb working query.

"SELECT Associates.Associate_Name FROM Associates WHERE Associates.[Region]='East' AND (Associates.[Role] Like '%BOA%' Or Associates.[Role] Like '%EBOA%') AND Associates.[Domain]='DB' ORDER BY Associates.[Associate_Name];"

Difference only * to %.

Thanks
Kashif