Tags: access, criterialike, dao, database, filter, following, microsoft, mysql, oracle, query, recordset, sql

Using the filter in a DAO recordset

On Database » Microsoft Access

5,558 words with 3 Comments; publish: Fri, 30 May 2008 19:58:00 GMT; (25062.50, « »)

Hi,

I have a query that uses the following criteria:

Like "CS*" Or Like "HR*" Or Like "NR*" Or Like "NS*" Or Like "SR*" Or Like

"SS*" Or Like "WR*" Or Like "WS*"

What I would like to do is use the same criteria in a record set that has

been created using VBA.

Would this be done in the .filter parameter and if so how are the quotes

within quotes coded?

Any help is greatly appreciated.

Cheers,

GLT.

All Comments

Leave a comment...

  • 3 Comments
    • Use single quote within the double quote

      For example:

      SQLStr = "Select * From TableName Where FieldName Like 'CS*' Or FieldName

      Like 'HR*'"

      Good Luck

      BS"D

      "GLT" wrote:

      > Hi,

      > I have a query that uses the following criteria:

      > Like "CS*" Or Like "HR*" Or Like "NR*" Or Like "NS*" Or Like "SR*" Or Like

      > "SS*" Or Like "WR*" Or Like "WS*"

      > What I would like to do is use the same criteria in a record set that has

      > been created using VBA.

      > Would this be done in the .filter parameter and if so how are the quotes

      > within quotes coded?

      > Any help is greatly appreciated.

      > Cheers,

      > GLT.

      >

      #1; Fri, 30 May 2008 20:00:00 GMT
    • "GLT" <GLT.ms-access.todaysummary.com.discussions.microsoft.com> wrote in message

      news:556071A4-E499-4E67-AF03-6E4B44CB6C1E.ms-access.todaysummary.com.microsoft.com...

      > Hi,

      > I have a query that uses the following criteria:

      > Like "CS*" Or Like "HR*" Or Like "NR*" Or Like "NS*" Or Like "SR*" Or Like

      > "SS*" Or Like "WR*" Or Like "WS*"

      > What I would like to do is use the same criteria in a record set that has

      > been created using VBA.

      > Would this be done in the .filter parameter and if so how are the quotes

      > within quotes coded?

      I'm not sure what you mean. Do you mean ...

      (A) You want to open a recordset using a dynamically-built SQL statement

      that applies these criteria? In that case, you'd do something like this:

      Dim rs As DAO.Recordset

      Set rs = CurrentDb.OpenRecordset( _

      "SELECT * FROM MyTable WHERE " & _

      "SomeField Like 'CS*' OR " & _

      "SomeField Like 'HR*' OR " & _

      "SomeField Like 'NR*' OR " & _

      "SomeField Like 'NS*' OR " & _

      "SomeField Like 'SR*' OR " & _

      "SomeField Like 'SS*' OR " & _

      "SomeField Like 'WR*' OR " & _

      "SomeField Like 'WS*'"

      or do you mean ...

      (B) You already have an open recordset, and you want to open a filtered

      recordset from it? In that case, you'd do something like this:

      Dim rs As DAO.Recordset

      Dim rsFiltered As DAO.Recordset

      ' ... code to open recordset rs goes here somewhere ...

      ' Open rsFiltered by applying a filter to rs.

      rs.Filter = _

      "SomeField Like 'CS*' OR " & _

      "SomeField Like 'HR*' OR " & _

      "SomeField Like 'NR*' OR " & _

      "SomeField Like 'NS*' OR " & _

      "SomeField Like 'SR*' OR " & _

      "SomeField Like 'SS*' OR " & _

      "SomeField Like 'WR*' OR " & _

      "SomeField Like 'WS*'"

      Set rsFiltered = rs.OpenRecordset

      Or, of course, youy could mean something else entirely.

      Dirk Goldgar, MS Access MVP

      www.datagnostics.com

      (please reply to the newsgroup)

      #2; Fri, 30 May 2008 20:01:00 GMT
    • Hi Dirk,

      Option (b) was what I was looking for but thanks to both of my responses

      they provided much needed clarification.

      Cheers,

      GLT.

      "Dirk Goldgar" wrote:

      > "GLT" <GLT.ms-access.todaysummary.com.discussions.microsoft.com> wrote in message

      > news:556071A4-E499-4E67-AF03-6E4B44CB6C1E.ms-access.todaysummary.com.microsoft.com...

      >

      > I'm not sure what you mean. Do you mean ...

      > (A) You want to open a recordset using a dynamically-built SQL statement

      > that applies these criteria? In that case, you'd do something like this:

      > Dim rs As DAO.Recordset

      > Set rs = CurrentDb.OpenRecordset( _

      > "SELECT * FROM MyTable WHERE " & _

      > "SomeField Like 'CS*' OR " & _

      > "SomeField Like 'HR*' OR " & _

      > "SomeField Like 'NR*' OR " & _

      > "SomeField Like 'NS*' OR " & _

      > "SomeField Like 'SR*' OR " & _

      > "SomeField Like 'SS*' OR " & _

      > "SomeField Like 'WR*' OR " & _

      > "SomeField Like 'WS*'"

      > or do you mean ...

      > (B) You already have an open recordset, and you want to open a filtered

      > recordset from it? In that case, you'd do something like this:

      > Dim rs As DAO.Recordset

      > Dim rsFiltered As DAO.Recordset

      > ' ... code to open recordset rs goes here somewhere ...

      > ' Open rsFiltered by applying a filter to rs.

      > rs.Filter = _

      > "SomeField Like 'CS*' OR " & _

      > "SomeField Like 'HR*' OR " & _

      > "SomeField Like 'NR*' OR " & _

      > "SomeField Like 'NS*' OR " & _

      > "SomeField Like 'SR*' OR " & _

      > "SomeField Like 'SS*' OR " & _

      > "SomeField Like 'WR*' OR " & _

      > "SomeField Like 'WS*'"

      > Set rsFiltered = rs.OpenRecordset

      > Or, of course, youy could mean something else entirely.

      > --

      > Dirk Goldgar, MS Access MVP

      > www.datagnostics.com

      > (please reply to the newsgroup)

      >

      #3; Fri, 30 May 2008 20:02:00 GMT