Results 1 to 6 of 6
  1. #1
    George is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Posts
    295

    Filter is working with literal string but not accepting variable

    Good Evening,

    The filter on a recordset works fine when a literal is used but when a variable containing the same value as the string literal the filter does not accept it. The code is below. Can you assist?

    Option Compare Database
    Private Sub Command43_Click()
    Dim curDatabase As Database
    Set curDatabase = CurrentDb

    Dim rs3 As Recordset
    Dim t As Recordset
    Dim g As String

    Set rs3 = curDatabase.OpenRecordset("Select * from [Courses under Programs]")



    g = "ANS.CT"

    rs3.Filter = "[ProgramCode]= " & g ' The problem is at this line

    Set t = rs3.OpenRecordset

    t.MoveLast
    t.MoveFirst

    MsgBox t.RecordCount

    End Sub

    ' This code works well:
    rs3.Filter = "[ProgramCode]= 'ANS.CT'"

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Curious what the overall goal is, but:

    rs3.Filter = "[ProgramCode]= '" & g & "'"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    George is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Posts
    295
    Thanks very much. I could never have figured it out. I whish the reasoning behind this could be explained so that I would know when to apply it.

    I am using this code to filter information in a table in a student information system.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    When you hard-coded the value and it worked, you included the single quotes. Your code with the variable evaluated to:

    rs3.Filter = "[ProgramCode]= ANS.CT"

    Which causes a syntax error. I added the single quotes around the variable.

    If all you're looking for is a count, I'd simply do this:

    Set rs3 = curDatabase.OpenRecordset("Select Count(*) AS HowMany from [Courses under Programs] WHERE [ProgramCode]= '" & g & "'")

    MsgBox rs3!HowMany
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    George is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Posts
    295

    Thanks

    Thanks for the explanation, I find it useful

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Extract Value from Variable in String
    By nguyenak in forum Programming
    Replies: 3
    Last Post: 05-24-2012, 03:50 PM
  2. Replies: 3
    Last Post: 12-02-2011, 04:14 PM
  3. Variable Filter
    By breana in forum Queries
    Replies: 5
    Last Post: 03-11-2011, 11:08 AM
  4. Replies: 1
    Last Post: 02-03-2011, 07:41 AM
  5. Using a string variable to specify a control
    By Gerry in forum Programming
    Replies: 3
    Last Post: 04-14-2010, 02:28 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums