Trim Search value on Data Access Page 1

[ScorpioX]

Hello,
What I want to do is trim the search field down to the last 7 characters, then search the records with that value. For example now I have a text box (SBSN), that a user puts in a serial number. Then clicks a button and the matching record is returned, if there is one. What I would like to do is. Let's say they type in "AMJHG12L3X24JH". I would like to trim it down to the last 7 characters "L3X24JH" and search with that value instead. I have no idea on how to do that. Any help would be great. Here is some more info on it.

This is a DAP, that takes the value the user types in the textbox "SBSN" and searches the field "SerialNumber", in the Table "tblMachines", and then returns the matching record if there is one.

Thanks,
ScorpioX

 

[AndyInNC]

Where are you doing this? In T-SQL, .NET, Access, something else?

 

[ScorpioX]

Hi AndyInNC,
Thank you for you response. I am attempting to do this on a Data Access Page using Access 2003. The code I am using to do the searching now is VbScript. I will show you the code below. If possible I would like to trim it down to search only the last 7 characters of the SerialNumber field.

'---CODE START---
<SCRIPT language=vbscript event=onclick for=Search0>
<!--
Dim rs
Set rs=MSODSC.DataPages(0).Recordset.Clone
On error resume next
rs.find "[SerialNumber] = '" & CStr(document.all.item("SBSN").Value) & "'"
If (err.number <> 0) Then
document.all.item("SBSN").value =""
MsgBox "Error: " & err.number & " " & err.Description,,"Invalid Search"
Exit Sub
End If
If (rs.bof) or (rs.eof) Then
document.all.item("SBSN").value =""
Msgbox "Serial Number Not Found",, "Search Done"
Exit Sub
End If
MSODSC.DataPages(0).Recordset.Bookmark = rs.Bookmark
document.all.item("SBSN").value =""
-->
'---CODE END---

The reason I am looking to trim the search is that the Serial number can be input in a couple of different ways. Either scanned with a barcoder or entered manually. The only thing consistent either way. Is the last 7 characters of the field.

Thanks,
ScorpioX

 

[AndyInNC]

Go with:

[tt]
[tab]rs.find "[SerialNumber] = '" & Right(CStr(document.all.item("SBSN").Value), 6) & "'"
[/tt]

 

[ScorpioX]

AndyInNC,
Thank you again, that worked like a charm. I forgot to add one part though. The field being searched may or may not have more then seven characters as well. So I would need it to search and return the matching record based on the last 7 characters. Right now it only returns the record if it matches in the field. For example:

A) If the Field contains "ASDFGHJKL", and it searches for "DFGHJKL" it will not return the record.

B) If the field contains "DFGHJKL", and it searches for "DFGHJKL" it returns that record.

It is doing in part what I want by dropping all but the last 7 characters, but if anything else is in the field it does not work. Got any ideas?

Thanks,
ScorpioX

 

[AndyInNC]

Ah. One of the following should get you what you need.

Approach 1[tt]
[green]
'*** This will allow a search against anything the user may enter.
' The asterisk means "match anything". It can go before or after
' with a LIKE. (But it needs to be included with the search text.)
[/green]rs.find "[SerialNumber] [red]LIKE[/red] '[red]*[/red]" & CStr(document.all.item("SBSN").Value) & "'"

[/tt]

Approach 2
You could still do the last 7 characters with something like:[tt]
[green]
'*** This is some shorthand to work with the value.
[/green]Dim strSBSN
strSBSN = CStr(document.all.item("SBSN").Value)

If Len(strSBSN) > 7 Then
strSBSN = Right(strSBSN, 7)
End If

rs.find "[SerialNumber] = '" & strSBSN & "'"
[/tt]

 

[ScorpioX]

Hi AndyInNC,
I tried both approaches and they do not seem to work. Approach 1 returns a Invalid Search box (error: 3001 Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another). Approach 2 just does not return anything. Got any ideas on what might be causing this? I thank you again for your time. I really do appreciate it.

ScorpioX

 

[AndyInNC]

Hi ScorpioX,

I didn't realize that SerialNumber is actually a number. So yes, Approach 1 wouldn't work since it is looking at it as a string value. Pretty much the same with Approach 2 (although I made an error that would have thrown it off anyway). Let's pick up with Approach 2.

[tt]
Dim strSBSN
strSBSN = CStr(document.all.item("SBSN").Value)

If Len(strSBSN) > 7 Then
strSBSN = Right(strSBSN, 7)
End If

Dim strSearch
[green]'*** Play with the data types a little until it works correctly.
'*** Try removing the single quotes to evaluate it as a number.[/green]
strSearch = "Right([SerialNumber], Len(strSBSN)) = '" & strSBSN & "'"

[green]'*** Check the value to make sure it looks right.[/green]
MsgBox strSearch [green]'*** Or use "Response.Write" if server-side[/green]

rs.Find strSearch
[/tt]


The bottom line is that either of these approaches are good. Sometimes a difference in data types will create the need for modifications (like adding or removing quotation marks and such).

Good luck.

 

[ScorpioX]

Hi AndyinNC,
Thank you again. I beleive it is a string unless I have coded something wrong. The Field excepts both letters and numbers, in any combination. I will play with them to see what I get though.

ScorpioX

 

[ScorpioX]

Hi AndyinNC,
I tried as you stated and it does not seem to be working. It comes back with the wrong type error like it did above. Sorry I thought this would be easy. Got any more ideas?

ScorpioX

 

[AndyInNC]

ScorpioX,

Apparently, the problem is still with the data type. (That's what came through in the error message.)

At this point, I suggest taking it apart one line at a time and looking at each value -- make sure that you're getting what you expect it will return. You can use multiple message boxes for this. Check the lengths, the Right() values that are returned, etc. You can also look at NULLs that could be in the data -- although I wouldn't expect to find any problems there. Still, it wouldn't hurt to look.

During development, isolate a single record where all the values you're working with are more than 7 characters. If you can get it working there, change the user input value for the same record to less than 7 characters. Then a record where the barcode has less than 7 characters, if that is a possibility. You may already be doing just this, but I felt like it was worth mentioning.

A final idea is to change the whole approach to the recordset. Open a recordset that uses a SQL statement using a WHERE clause to specify the record you want.

[tt]
SELECT Field1, Field2
FROM TableName
WHERE SerialNumber = strSBSN
[/tt]

OR

[tt]
SELECT Field1, Field2
FROM TableName
WHERE Right(SerialNumber, Len(strSBSN)) = strSBSN
[/tt]

Personally, I always prefer this approach as opposed to the ADO Find method. ("Prefer" is the key word here; it's not always the right way.)

Sorry I couldn't be more help. Good luck.

 

[ScorpioX]

Hi AndyinNC,
The code you suggested above the VBScript, does actually return the correct value within the MsgBox, but does not return a record. I had thought I read something somewhere on using the like command. So I will try to find that again. I will also try your sugesstion using Sql if possible. Thanks again for you time.

Scorpiox

 

[ScorpioX]

Hi AndyinNC,
Just wanted to drop you an update. I did get this working. I actually used both approaches and it is working like a charm. Thanks again for all your time.

ScorpioX

 

[ScorpioX]

Here is the working code in case you are curious and or someone else is having this issue.

The code below Searches the Default Recordset in this case "tblMachines", returning the matching record to the DAP. It searches The "SerialNumber" field within "tblMachines" using the value that is entered in to the Text box called "SBSN", once the "Search0" button is clicked. The nice part is it only uses the last seven characters of the value within "SBSN", searches the "SerialNumber" field within "tblMachines", for the matching record.

I thank AndyinNC for all his help in getting this working.

'...Start Code.........................
<SCRIPT language=vbscript event=onclick for=Search0>
<!--
Dim rs
Set rs=MSODSC.DataPages(0).Recordset.Clone
On error resume next
rs.find "SerialNumber LIKE *" & Right(CStr(document.all.item("SBSN").Value), 7) & "*"
If (err.number <> 0) Then
document.all.item("SBSN").value =""
Msgbox "Serial Number Not Found",, "Search Done"
Exit Sub
End If
If (rs.bof) or (rs.eof) Then
document.all.item("SBSN").value =""
Msgbox "Serial Number Not Found",, "Search Done"
Exit Sub
End If
MSODSC.DataPages(0).Recordset.Bookmark = rs.Bookmark
document.all.item("SBSN").value =""
-->
'...End Code........................

ScorpioX