Results 1 to 13 of 13
  1. #1
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754

    Caractors found after sql statement?? Error?

    FilterForm.zip



    I am working on a filter form in by db and should be simple yet never is. In any event, I am not getting an error but it doesn't work and if I refresh page, then I get an error.
    Something to do with "characters found after sql statement"
    I cant find any thing as such in the code.
    In any event, I borrowed this code from one of my older db's and though it would work, yet it doesn't work like intended.
    Any ideas?
    Thanks

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I lost my mind reading hat.
    Which form or query would that be?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Sorry, Form is "SearchFrm" and db opens to it. The code behind it is as follows:
    [CODEPrivate Function FilterScreenName()
    'Main Procedure for Filtering
    Dim p As String, mWhere As String
    p = "SELECT p.PersonID, p.ScreenName, p.IsActive, p.DND FROM PersonTbl AS p"
    If Not IsNull(Me.TxtFilterScreenName) Then
    mWhere = " p.ScreenName LIKE '" & Me.TxtFilterScreenName & "*'"
    End If
    If Not IsNull(Me.CboFilterCAL) Then
    mWhere = (mWhere & IIf(Len(mWhere) > 0, "AND", "")) & Me.CboFilterCAL
    End If
    If Len(mWhere) > 0 Then
    p = p & "WHERE" & mWhere
    Me.LblScreenNameFiltered.Caption = "Screen Names Filtered"
    Else
    Me.LblScreenNameFiltered.Caption = "Screen Names"
    End If

    Select Case Me.fraSort
    Case 2: p = p & " ORDER BY p.IsActive; "
    Case 3: p = p & " ORDER BY p.DND; "
    Case Else: p = p & " ORDER BY p.ScreenName; "
    End Select
    Debug.Print p
    Me.LstScreenName.RowSource = p
    Me.LstScreenName.Requery

    End Function

    ][/CODE]

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I haven't looked at your file.
    What do you see when you debug.print p?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Code in db has a semicolon following AS p;

    The WHERE code is messed up. Should step debug.

    What input goes in ScreenName textbox?

    Why bother with the alias table name?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I have to leave for most of the evening now, so either will have to wait or someone else can step in. Strongly suggest you adopt a proper naming convention for future help as deciphering your code is not fun. Not sure which is worse - using p for a variable or using it for a variable AND a table alias. OK, I think it's the latter
    http://access.mvps.org/access/general/gen0012.htm
    https://access-programmers.co.uk/for...d.php?t=225837

  7. #7
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    I took out the ; and it worked some what. It didn’t list the search criteria but gave me a -1 in the count? Reset worked and sort worked but didn’t show any filtered redords

  8. #8
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Took out the ;
    Reset worked,
    Screen name txt is just first few letters of a screen name

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Step debug. Look at the compiled WHERE clause. There is no field specified for the combobox parameter. WHERE1 means nothing.

    How can CboFilterCAL be used to filter the listbox? There is no data relationship.

    Your db is set for "SQL Server CompatibleSyntax" This means Access expects ALIKE with % wildcard instead of LIKE and * wildcard.

    mWhere = " ScreenName ALIKE '" & Me.TxtFilterScreenName & "%'"

    If you want to change that: Home > Options > Object Designers > Query Design > uncheck This Database
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Hi June7,
    Thank you so much? I guess that ansi 92 setting is default as I never changed it. I just bought a new copy of 2019 so didn't even dawn on me to check that. I knew there were two 89/92 but never had any reason to change it.
    With that said, it works now with the exception to the combo box, as it doesn't have a reference yet and am still researching this to see how I add that table into this mix as the only relationship it has with this is through the many2many table.
    Not sure if I need to bring in the many2many and the CAlTbl or not to accomplish this.
    Thanks
    Dave

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Consider:

    mWhere = mWhere & IIf(Len(mWhere) > 0, "AND", "") & " PersonID IN (SELECT PersonID FROM Person2CALTbl WHERE CALID=" & Me.CboFilterCAL & ")"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Thanks June7,
    You are so awesome! I will try to play with this tonight and see if I can get It working!
    Thanks much
    Dave

  13. #13
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Hi June7,
    You are the most awesome! Thank you as that worked like a charm! Saved me hours and hours of toying!
    Thanks so much
    God Bless
    Dave

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

Similar Threads

  1. Error : File not found
    By CoZak in forum Programming
    Replies: 5
    Last Post: 06-29-2018, 02:32 AM
  2. Replies: 7
    Last Post: 05-01-2017, 02:18 PM
  3. File Not Found error
    By polbit in forum Access
    Replies: 7
    Last Post: 07-10-2014, 09:54 AM
  4. Error in Report when value is not Found
    By SealM in forum Reports
    Replies: 2
    Last Post: 07-02-2013, 12:33 PM
  5. Replies: 7
    Last Post: 06-08-2012, 09:55 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