Results 1 to 14 of 14
  1. #1
    Nailati is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Posts
    15

    Search for string, ignore punctuation (attempt at button code failing horribly)

    Greetings,

    I have a database of publications that are identified by hyphenated numbers (TMINs). I have a button on my main form which searches records by TMIN--works fine, as long as I remember the hyphens. (Basic button, created with the wizard.) Now I'm trying to code a button that will search on TMIN while ignoring any hyphens.



    I'm not sure what this would involve.
    - Saving the search query as a variable so it can be compared against the TMIN twice, with and without hyphens?
    - Stripping the search query of hyphens, then comparing it to a hyphen-less version of the TMIN list (how? Would this have to be saved as another column of data?)

    I'm not sure if it's even possible to be honest. Here's my total shot in the dark, cobbled together with something I found on Google. Needless to say, it doesn't work

    Private Sub BtnSearchFieldTMIN_Click()
    If IsNull(FieldTMIN) = False Then
    TMIN_NoHyphens = Replace(Me.FieldTMIN, "-", "")
    Me.Recordset.FindFirst "[Database Field]=" & TMIN_NoHyphens
    Me!FieldTMIN = Null
    If Me.Recordset.NoMatch Then
    MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
    Me!FieldTMIN = Null
    End If
    End If
    End Sub

    Any help would be greatly appreciated--even if it's just to tell me that it's impossible--thanks!

  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
    Don't know what "doesn't work" means, but you appear to be comparing apples and oranges. Does this work?

    Me.Recordset.FindFirst "Replace([Database Field], "-", "")=" & TMIN_NoHyphens
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    First thing that I see wrong, here, is the line

    Me.Recordset.FindFirst "[Database Field]=" & TMIN_NoHyphens

    This is only the correct syntax if TMIN_NoHyphens is a Number, which it obviously isn't since Replace() returns a String and the Field originally had hyphens in it. The correct syntax for a Text Field would be

    Me.Recordset.FindFirst "[Database Field]='" & TMIN_NoHyphens & "'"

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Every/all code pages should begin with these two lines
    Code:
    Option Compare Database
    Option Explicit
    There is a setting to always add "Option Explicit" under Options (in A2K-A2K3)

    this is untested......
    Code:
    Private Sub BtnSearchFieldTMIN_Click()
       Dim rs As DAO.Recordset
       Dim TMIN_NoHyphens As String
    
    
       If Len(Trim(FieldTMIN)) > 0 Then
          rs = Me.RecordsetClone
          TMIN_NoHyphens = Replace(Me.FieldTMIN, "-", "")
    
          '  text type field, so need delimiters
          rs.FindFirst "[Database Field]= '" & TMIN_NoHyphens & "'"
    
          If rs.NoMatch Then
             MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
             Me!FieldTMIN = Null
          Else
             ' record found so move to record in main recordset
             Me.Bookmark = rs.Bookmark
          End If
          
          Set rs = Nothing
       End If
    
    End Sub

  5. #5
    Nailati is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Posts
    15
    Thank you so much for the input--I just knew you guys would cringe at that code

    I'm working through the info you guys gave me/testing and will let you know how it turns out.

  6. #6
    Nailati is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Posts
    15
    Okay--I've made some progress, but no joy yet. I'm getting run-time error 3070: "database engine does not recognize 'FieldTMINSearch' as a valid field name or expression." Code is at bottom.

    First of all, I realize I wasn't clear about what I was doing--I was imagining a search button that would bring up a prompt window, like the default wizard buttons do, rather than a button next to a search field. It seems, though, that the code I attempted is more suited to the latter so I'll stick to that. (Part of my confusion was that I didn't realize--duh!--that FieldTMIN was acting as the search input, when it is supposed to be a fixed/display field on this form.)

    To that end, I added a new unbound field (FieldTMINSearch) next to the search button (BtnTMINSearch). (I also added "Option Explicit" to all my code pages--good to know.) Aside from updating the names, the only change I made to ssanfu's code was to change ( rs = Me.RecordsetClone ) to ( Set rs = Me.RecordsetClone ) which stopped an error on that line. Here's what I'm working with:

    Private Sub BtnTMINSearch_Click()
    Dim rs As DAO.Recordset
    Dim TMIN_NoHyphens As String

    If Len(Trim(FieldTMINSearch)) > 0 Then
    Set rs = Me.RecordsetClone
    TMIN_NoHyphens = Replace(Me.FieldTMINSearch, "-", "")
    ' text type field, so need delimiters
    rs.FindFirst "[FieldTMINSearch] = '" & TMIN_NoHyphens & "'"
    If rs.NoMatch Then
    MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
    Me!FieldTMINSearch = Null
    Else
    ' record found so move to record in main recordset
    Me.Bookmark = rs.Bookmark
    End If

    Set rs = Nothing
    End If
    End Sub

    The debugger throws the error up at this line:

    rs.FindFirst "[FieldTMINSearch] = '" & TMIN_NoHyphens & "'"

    I've played around with punctuation & spacing, changed the field name, even tried removing the brackets, but nothing seems to get rid of the 3070 error. I've had little luck with finding anything on Google about this error that I can apply. Most results suggest adding the punctuation which is already present.

    Any suggestions? Thanks again!

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    My bad... Sorry about missing the "Set rs..."



    - Stripping the search query of hyphens, then comparing it to a hyphen-less version of the TMIN list
    and
    Okay--I've made some progress, but no joy yet. I'm getting run-time error 3070: "database engine does not recognize 'FieldTMINSearch' as a valid field name or expression."

    The debugger throws the error up at this line:

    rs.FindFirst "[FieldTMINSearch] = '" & TMIN_NoHyphens & "'"
    To that end, I added a new unbound field (FieldTMINSearch) next to the search button (BtnTMINSearch).
    Sooooo, "FieldTMINSearch" is NOT the name of a field in the recordset rs ????
    Before you had:
    Code:
          rs.FindFirst "[Database Field]= '" & TMIN_NoHyphens & "'"
    What is the name of the field that has the "hyphen-less version" of "TMin"? It shouldn't be the name of the unbound text box. It HAS to be the name of a field in the recordset (rs).

  8. #8
    Nailati is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Posts
    15
    Quote Originally Posted by ssanfu View Post
    My bad... Sorry about missing the "Set rs..." [...]
    Sooooo, "FieldTMINSearch" is NOT the name of a field in the recordset rs ????
    Before you had:
    Code:
          rs.FindFirst "[Database Field]= '" & TMIN_NoHyphens & "'"
    What is the name of the field that has the "hyphen-less version" of "TMin"? It shouldn't be the name of the unbound text box. It HAS to be the name of a field in the recordset (rs).
    Oh, that's not a problem at all. Can't complain about free help, and I learned a command

    I apologize--I must be officially confused! The [Database Field] came from the code I was Frankensteining, and clearly I didn't know what I was doing when I started plugging different things in there. I tried plugging in the TMIN field at first, but this led the script to erase the TMIN (and to fail to find existing matches, although at least it wasn't throwing error 3070). That led me to think I shouldn't use a pre-existing field there...oops!

    Correct, there is no FieldTMINSearch field in my rs. There is no field that has the hyphen-less versions of the TMINs; only the hyphenated versions are stored (in the field TMIN). I thought I needed a blank field to enter the search term into, and plopped FieldTMINSearch onto the form for that purpose. The TMIN field on this form is meant to display the TMIN (with hyphens) of the current record and is generally not edited.

    So, is there no way to use the Replace() function on the TMIN field to generate hyphen-less TMINs when the button is clicked? I was imagining some magic code that would strip the hyphens out of one's query (if they are there), then strip the hyphens out of the stored TMINs, then look for the first match. Do I instead need to create another field in my publication table to store the hyphen-less version of the TMIN for each publication?

    Also, where in this code is the user entering their seach term? Are they being prompted with a pop-up or typing the search term somewhere on the form?

    Thanks again for all the help!

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    From your first post:
    I have a database of publications that are identified by hyphenated numbers (TMINs). I have a button on my main form which searches records by TMIN--works fine, as long as I remember the hyphens. (Basic button, created with the wizard.) Now I'm trying to code a button that will search on TMIN while ignoring any hyphens.
    In that query, add a calculated column:
    Code:
    NoHypn: Replace([FieldTMIN],"-","")
    Then you have:
    - the unbound control (text box) "FieldTMINSearch" to enter the TMin search value (with or without hyphens)
    - a button to start the search ("BtnTMINSearch")

    The search code would reference the "new" column (without hyphens)
    Code:
    Private Sub BtnTMINSearch_Click()
       Dim rs As DAO.Recordset
       Dim TMIN_NoHyphens As String
    
       If Len(Trim(FieldTMINSearch)) > 0 Then
          Set rs = Me.RecordsetClone
          TMIN_NoHyphens = Replace(Me.FieldTMINSearch, "-", "")
          ' text type field, so need delimiters
          rs.FindFirst "[NoHypn]= '" & TMIN_NoHyphens & "'"
          If rs.NoMatch Then
             MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
             Me!FieldTMINSearch = Null
          Else
             ' record found so move to record in main recordset
             Me.Bookmark = rs.Bookmark
          End If
    
          Set rs = Nothing
       End If
    End Sub

  10. #10
    Nailati is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Posts
    15
    Hi--sorry for the late response--the button that the wizard created is a macro (pic below). Can I re-create this with a query?


  11. #11
    Nailati is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Posts
    15
    I've been trying to implement this, but I can't figure out what query is being referenced, or where to put the calculated column:

    Quote Originally Posted by ssanfu View Post
    From your first post:
    Quote Originally Posted by Nailati View Post
    Greetings,

    I have a database of publications that are identified by hyphenated numbers (TMINs). I have a button on my main form which searches records by TMIN--works fine, as long as I remember the hyphens. (Basic button, created with the wizard.) Now I'm trying to code a button that will search on TMIN while ignoring any hyphens.
    In that query, add a calculated column:
    Code:
    NoHypn: Replace([FieldTMIN],"-","")
    The button triggers a macro. Any ideas? Thanks!

  12. #12
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I don't use macros.... only VBA code. Can't help with the macro.

    Whatever form your button is on *should* (don't know what your Main Form consists of) have a record source - hopefully a query. That query is where you would put the calculated column
    Code:
    NoHypn: Replace([FieldTMIN],"-","")
    Go to the first empty column and paste/type it in.

  13. #13
    Nailati is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Posts
    15
    Ah, I knew it would be something easy...thanks so much!

  14. #14
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You're welcome

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

Similar Threads

  1. Search for a String in VBA code.
    By dandoescode in forum Access
    Replies: 3
    Last Post: 06-21-2012, 11:00 AM
  2. Search to ignore null fields
    By tommy93 in forum Queries
    Replies: 10
    Last Post: 02-07-2012, 10:58 AM
  3. Punctuation blowup
    By davisgwe in forum Access
    Replies: 2
    Last Post: 09-15-2011, 02:58 PM
  4. RunTime Error 3075, code for search button
    By jacie in forum Programming
    Replies: 1
    Last Post: 04-15-2011, 05:23 AM
  5. Ignore user input while code is running
    By Bigdoggit in forum Forms
    Replies: 5
    Last Post: 03-28-2011, 07:58 AM

Tags for this Thread

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