How would I search through a particular field of a particular table for a
particular string value thats stored in a variable. Table name is License
Allocation the string variable is called initials and I wanna write some
code that searches through the INITIALS field for the initials string and if
it exists I wanna do soemthing..like

If  (initials exist in License Allocation)
    [code]
else
    [code]
end if

im not sure how to setuip the first line of the If statement....any ideas?
Thanks in advance.

Hanif Merali

Hi Hanif,

My name is Joseph Moore.  Thank you for using the Microsoft Access
Newsgroups.

Here is some sample code using DAO to search through a field in a table.  
If you are using Access 2000 or later, you may need to set a reference to
DAO before running the code (in the Visual Basic Editor, click Tools
-->References).

Function test()
Dim db As Database
Dim rs As Recordset
Dim varInitials as String

Set db = CurrentDb()
Set rs = db.OpenRecordset("Filemon_log", dbOpenDynaset)
varInitials = "TextToSearchFor"

rs.MoveLast
rs.FindFirst "INITIALS = '" & varInitials & "'"

If rs.NoMatch Then
MsgBox "No Record Found"
Else
MsgBox "Record was Found"
End If

End Function

I hope this helps!  If you have additional questions on this topic, please
respond back to this posting.

Regards,

Joseph Moore
Microsoft Access Engineer

This posting is provided 'AS IS' with no warranties, and confers no rights.
You assume all risk for your use. ? 2001 Microsoft Corporation. All rights
reserved.

Joseph moore's code works - or you can do it using a
query.  Advantage of a query is that with large records,
it may be faster.  Once again, this is assuming you're
using Acc'97 and DAO.

public function InitialsExist () as Boolean
'true if match if found
  dim rs as recordset
  dim sql as string
  dim qry as querydef

'use * for wildcard searches.  Remove * if you want exact
'matches only
  sql = "SELECT INITIALS FROM [License Allocation] WHERE
INITIALS LIKE '*" & initials & "*'"

  set qry = currentdb.createquerydef("",sql)
  set rs = qry.openrecordset (dbopendynaset)

  if rs.recordcount >0 then
     InitialsExist = true
  else
     InitalsExist = false
  end if

  rs.close
  qry.close
end function

'Here's how to call it

private sub YOURSUB
  if InitialsExist = true then
    [Run Code]
  else
    [do something else]
  end if
end sub

Remember, it'll run each time you call InitialsExist -
decreasing your database speed.  If you must use the same
result in multiple places of code, declare a boolean
variable and make it equal the InitialsExist.  Then use
the variable in place of the function - this way the
function is run only once.

-hai

particular table for a
initials string and if
statement....any ideas?