No records in recordset

  • Thread starter Thread starter DellaCroce
  • Start date Start date
D

DellaCroce

I have a strange situation. I create a select statement behind a form.
that looks something like this:

Dim rs As New ADODB.Recordset
Dim dbJob As Connection
Set dbJob = CurrentProject.Connection
.....
ls_SQL = "Select * from [Action Item] where [Action Item].[Originator] Like
('*G*')"
ls_SQL = ls_SQL + " Order by Ainumber"
rs.Open ls_SQL, dbJob

The record set always comes back empty. I tested the select statement in a
query, and it come up with the correct records. I then tested the select
statement with a where clause that did not use the LIKE operator, and it
selects records correctly. But anytime I use the LIKE operator in the
statement, it comes up with no records.
1. I have check spelling on table and field names - they are correct
2. I have tried using other fields in the Where LIKE clause - they do not
come up with records.
3. I am using Access 2000 on a Win XP machine.

Can anyone point me towards a possible place to look for the error?
Thanks
 
Greg,

If this query is being issued against SQL Server, then change the asterisk
(*) to a percent (%).

Dim rs As New ADODB.Recordset
Dim dbJob As Connection
Set dbJob = CurrentProject.Connection
.....
ls_SQL = "Select * from [Action Item] where [Action Item].[Originator] Like
('%G%')"
ls_SQL = ls_SQL + " Order by Ainumber"
rs.Open ls_SQL, dbJob

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
Thanks for the idea Allen and Graham to use % instead of *. But this is
going against an Access database, not SQL Server. I did try it, and it gave
me an syntax error. Any other ideas?
Thanks again!
Greg
Allen Browne said:
What happens if you use % instead of * for the wildcard characters?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

DellaCroce said:
I have a strange situation. I create a select statement behind a form.
that looks something like this:

Dim rs As New ADODB.Recordset
Dim dbJob As Connection
Set dbJob = CurrentProject.Connection
....
ls_SQL = "Select * from [Action Item] where [Action Item].[Originator]
Like
('*G*')"
ls_SQL = ls_SQL + " Order by Ainumber"
rs.Open ls_SQL, dbJob

The record set always comes back empty. I tested the select statement in
a
query, and it come up with the correct records. I then tested the select
statement with a where clause that did not use the LIKE operator, and it
selects records correctly. But anytime I use the LIKE operator in the
statement, it comes up with no records.
1. I have check spelling on table and field names - they are correct
2. I have tried using other fields in the Where LIKE clause - they do not
come up with records.
3. I am using Access 2000 on a Win XP machine.

Can anyone point me towards a possible place to look for the error?
Thanks
Click to expand...
Click to expand...
 
What happens if you use DAO instead of ADO?

Dim rs As DAO.Recordset
Dim Is_SQL As String
ls_SQL = "SELECT [Action Item].* FROM [Action Item] WHERE [Action
Item].[Originator] Like ('*G*') ORDER BY [Action Item].Ainumber;"
Set rs = dbEngine(0)(0).OpenRecordset(Is_SQL)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

DellaCroce said:
Thanks for the idea Allen and Graham to use % instead of *. But this is
going against an Access database, not SQL Server. I did try it, and it
gave
me an syntax error. Any other ideas?
Thanks again!
Greg
Allen Browne said:
What happens if you use % instead of * for the wildcard characters?


DellaCroce said:
I have a strange situation. I create a select statement behind a form.
that looks something like this:

Dim rs As New ADODB.Recordset
Dim dbJob As Connection
Set dbJob = CurrentProject.Connection
....
ls_SQL = "Select * from [Action Item] where [Action Item].[Originator]
Like
('*G*')"
ls_SQL = ls_SQL + " Order by Ainumber"
rs.Open ls_SQL, dbJob

The record set always comes back empty. I tested the select statement in
a
query, and it come up with the correct records. I then tested the select
statement with a where clause that did not use the LIKE operator, and
it
selects records correctly. But anytime I use the LIKE operator in the
statement, it comes up with no records.
1. I have check spelling on table and field names - they are correct
2. I have tried using other fields in the Where LIKE clause - they do not
come up with records.
3. I am using Access 2000 on a Win XP machine.

Can anyone point me towards a possible place to look for the error?
Thanks
Click to expand...
Click to expand...
Click to expand...
 
Allen,
You are right that works. You don't need the Dim rs as DAO.Recordset,
btw, Access does not recognize DAO. But the Set rs.... works!
OK, now I have a question, WHY??? What is the difference between the
DAO and the ADO that causes one to bomb and the other to work???
Thanks!!

Greg
Allen Browne said:
What happens if you use DAO instead of ADO?

Dim rs As DAO.Recordset
Dim Is_SQL As String
ls_SQL = "SELECT [Action Item].* FROM [Action Item] WHERE [Action
Item].[Originator] Like ('*G*') ORDER BY [Action Item].Ainumber;"
Set rs = dbEngine(0)(0).OpenRecordset(Is_SQL)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

DellaCroce said:
Thanks for the idea Allen and Graham to use % instead of *. But this is
going against an Access database, not SQL Server. I did try it, and it
gave
me an syntax error. Any other ideas?
Thanks again!
Greg
Allen Browne said:
What happens if you use % instead of * for the wildcard characters?


I have a strange situation. I create a select statement behind a form.
that looks something like this:

Dim rs As New ADODB.Recordset
Dim dbJob As Connection
Set dbJob = CurrentProject.Connection
....
ls_SQL = "Select * from [Action Item] where [Action Item].[Originator]
Like
('*G*')"
ls_SQL = ls_SQL + " Order by Ainumber"
rs.Open ls_SQL, dbJob

The record set always comes back empty. I tested the select
Click to expand...
statement
in
a
query, and it come up with the correct records. I then tested the select
statement with a where clause that did not use the LIKE operator, and
it
selects records correctly. But anytime I use the LIKE operator in the
statement, it comes up with no records.
1. I have check spelling on table and field names - they are correct
2. I have tried using other fields in the Where LIKE clause - they do not
come up with records.
3. I am using Access 2000 on a Win XP machine.

Can anyone point me towards a possible place to look for the error?
Thanks
Click to expand...
Click to expand...
Click to expand...
 
DAO and ADO are libraries.

From a code window, choose References from the Tools menu to see which
one(s) you have selected.

If it does not recoginse "DAO.", then you may have a problem with your
references. See:
http://members.iinet.net.au/~allenbrowne/ser-38.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

DellaCroce said:
Allen,
You are right that works. You don't need the Dim rs as DAO.Recordset,
btw, Access does not recognize DAO. But the Set rs.... works!
OK, now I have a question, WHY??? What is the difference between the
DAO and the ADO that causes one to bomb and the other to work???
Thanks!!

Greg
Allen Browne said:
What happens if you use DAO instead of ADO?

Dim rs As DAO.Recordset
Dim Is_SQL As String
ls_SQL = "SELECT [Action Item].* FROM [Action Item] WHERE [Action
Item].[Originator] Like ('*G*') ORDER BY [Action Item].Ainumber;"
Set rs = dbEngine(0)(0).OpenRecordset(Is_SQL)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

DellaCroce said:
Thanks for the idea Allen and Graham to use % instead of *. But this is
going against an Access database, not SQL Server. I did try it, and it
gave
me an syntax error. Any other ideas?
Thanks again!
Greg
What happens if you use % instead of * for the wildcard characters?


I have a strange situation. I create a select statement behind a form.
that looks something like this:

Dim rs As New ADODB.Recordset
Dim dbJob As Connection
Set dbJob = CurrentProject.Connection
....
ls_SQL = "Select * from [Action Item] where [Action Item].[Originator]
Like
('*G*')"
ls_SQL = ls_SQL + " Order by Ainumber"
rs.Open ls_SQL, dbJob

The record set always comes back empty. I tested the select statement
in
a
query, and it come up with the correct records. I then tested the
select
statement with a where clause that did not use the LIKE operator,
and
it
selects records correctly. But anytime I use the LIKE operator in the
statement, it comes up with no records.
1. I have check spelling on table and field names - they are
correct
2. I have tried using other fields in the Where LIKE clause - they
do
not
come up with records.
3. I am using Access 2000 on a Win XP machine.

Can anyone point me towards a possible place to look for the error?
Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top