By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,477 Members | 1,641 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,477 IT Pros & Developers. It's quick & easy.

Like Opertor is not working in MS acces-Retun error code 3021 -eith EOF OR BOF is tru

P: 7
Dar team

need u r help

I am new to aceess . I am writign a query in access for finding the last name mathcing with criteria using Like operator.
Expand|Select|Wrap|Line Numbers
  1. selct a,b,c from Table1 where last  name like '*"&  Paramter value &"*'
When in debug mode I can see teh in immediate window teh query and when i put this query in query window in access it is returning the
data also which is very good

but in Actual from where I am using a function
Expand|Select|Wrap|Line Numbers
  1. set rs = getlastname(LastName)--(Last name is the Paramet er in above query)
The function is as follows

Expand|Select|Wrap|Line Numbers
  1. Function getlastname(lastnamyName As String) As ADODB.Recordset
  2.     Dim rs As ADODB.Recordset
  3.     Dim strsql As String
  4.     Dim da As New DABase
  7.     strsql = "SELECT *"
  8.     strsql = strsql & " FROM tables1  "
  9.     strsql = strsql & " WHERE (((lastname) like  ""*" & lastame & "*"")) ;"
  11.     da.openConnection
  12.     da.CommandText = strsql
  13.     Set getlastname= da.openRecordset
  14.     Set da = Nothing
  16. End Function

after completion of this fun.. I agin come in my main form where this fn has een written
Expand|Select|Wrap|Line Numbers
  1. set rs = getlastname(LastName)-
after htis line rs.recordcount showing me as zero ??? so that is te reason run time error 3021 is coming..

Funny thing is if I remove liek operator it is wrking finme.

Request u r help
Jan 29 '10 #1
Share this Question
Share on Google+
2 Replies

Expert Mod 2.5K+
P: 2,545
Hi, and Welcome to Bytes!

If you have copied and pasted your function accurately then you have a mis-spelt variable name in your LIKE clause:

Expand|Select|Wrap|Line Numbers
  1. strsql = strsql & " WHERE (((lastname) like ""*" & lastame & "*"")) ;"
should be

Expand|Select|Wrap|Line Numbers
  1. strsql = strsql & " WHERE (((lastname) like ""*" & lastname & "*"")) ;"
If this is the case, it also means that you are not using the OPTION EXPLICIT directive at the top of your code in the VBA Editor. If you were, the editor would itself pick up that you have an undeclared variable (which is what it will treat your misnamed variable as), in turn allowing you to see and fix the error before it causes you difficulties.

I may be wrong, as there are many, many spelling errors in your post and in the code (including the parameter name, which in your function is listed as lastnamyName). If you have copied in code by hand please don't - give us the actual code you are using, not a mis-spelt version of it. Saves a lot of our time!

Jan 29 '10 #2

P: 7
@Stewart Ross Inverness

Sorry for mis-spelling, In the code it is right, now again I have searced on the net. For DAO we need to put * & for ADO we can use %.

Now it is wokring. Thanks for u r help Stewart.

Sorry for the inconvenince

Jan 29 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.