Multiple use of Like command?

[robk5787]

Hey folks,
While working the night shift my company expects me to stay awake and develop a simple database for filtering data, but I have now achieved a brain block. I have a list of data that I need to search through and select only the numbers needed. For example, I need to filter and display only certain accounting numbers amongst a list of thousands. Can this be done? I tried Like "745110" & "745120" & "745125".... but no luck. Please help.

Thanks!

 

[Jon K]

I think you'll need VBA.

I have attached a demo DB. You can open the form, enter some accounting
numbers and click on the command button to view the records.

When the command button is clicked, a new query qryAccounts is built and
run. The code is contained in the On Click Event of the command button.

Private Sub cmdDisplayRecords_Click()
   ' if no accounting number is entered, dispaly message and exit.
   If IsNull(Me.txtAccNos) Then
      MsgBox "Enter some Accounting Numbers first."
      Me.txtAccNos.SetFocus
      Exit Sub
   End If
   
   Dim db As DAO.Database
   Dim qDef As DAO.QueryDef
   Dim SQL As String
   Dim sAccNos As String
   Dim sCriteria As String
   
   ' build criteria string.
   sAccNos = Trim(Me.txtAccNos)
   Do While InStr(sAccNos, ",") > 0
     sCriteria = sCriteria & ",'" & Trim(Left(sAccNos, InStr(sAccNos, ",") - 1)) & "'"
     sAccNos = Trim(Mid(sAccNos, InStr(sAccNos, ",") + 1))
   Loop
   sCriteria = sCriteria & ",'" & sAccNos & "'"
   sCriteria = "(" & Right(sCriteria, Len(sCriteria) - 1) & ")"
        
   ' build SQL statement: tblAccounts is the table name,
   ' Accounting Number is the field name.
   SQL = "Select * from [tblAccounts] where [Accounting Number] in " & sCriteria
      
   ' delete query qryAccounts, if already exits.
   On Error Resume Next
   DoCmd.DeleteObject acQuery, "qryAccounts"
   On Error GoTo 0
   
   ' create and run the new query qryAccounts.
   Set db = CurrentDb()
   Set qDef = db.CreateQueryDef("qryAccounts", SQL)
   DoCmd.OpenQuery qDef.Name
   
   Set qDef = Nothing
   Set db = Nothing
   
End Sub

The DB is in Access 97 format, so DAO is used. If you write the code in
Access 2000 or 2002, you must make a reference to DAO (when the code
window is open, choose menu Tools, References... select the Microsoft
DAO 3.6 Object Library from the list).

To open the attached DB in Access 2000 or 2002, just choose Convert
and save as a new name when it is opened for the first time.

 

[robk5787]

Thanks

I appreciate the help! I will try something very similiar to this. I have successfully tried and will probably end up using appending tables! This seems to work well for now.