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

[Nailati]

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!

[pbaldy]

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

[Missinglinq]

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)>

[ssanfu]

Every/all code pages should begin with these two lines

Option Compare Database
Option Explicit

There is a setting to always add "Option Explicit" under Options (in A2K-A2K3)

this is untested......

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

[Nailati]

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.

[Nailati]

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!

[ssanfu]

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:

      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).

[Nailati]

My bad... Sorry about missing the "Set rs..." [...]
Sooooo, "FieldTMINSearch" is NOT the name of a field in the recordset rs ????
Before you had:

      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!

[ssanfu]

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:

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)

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

[Nailati]

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?

[Nailati]

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:

From your first 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:

NoHypn: Replace([FieldTMIN],"-","")

The button triggers a macro. Any ideas? Thanks!

[ssanfu]

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

NoHypn: Replace([FieldTMIN],"-","")

Go to the first empty column and paste/type it in.

[Nailati]

Ah, I knew it would be something easy...thanks so much!

[ssanfu]

You're welcome