×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Trim Search value on Data Access Page

Trim Search value on Data Access Page

Trim Search value on Data Access Page

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

RE: Trim Search value on Data Access Page

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

RE: Trim Search value on Data Access Page

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

RE: Trim Search value on Data Access Page

Go with:


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



 

RE: Trim Search value on Data Access Page

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

RE: Trim Search value on Data Access Page

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

Approach 1

'*** 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.)
rs.find "[SerialNumber] LIKE '*" & CStr(document.all.item("SBSN").Value) & "'"



Approach 2
You could still do the last 7 characters with something like:

'*** This is some shorthand to work with the value.
Dim strSBSN
strSBSN = CStr(document.all.item("SBSN").Value)

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

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


 

RE: Trim Search value on Data Access Page

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

RE: Trim Search value on Data Access Page

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.


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

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

Dim strSearch
'*** Play with the data types a little until it works correctly.
'*** Try removing the single quotes to evaluate it as a number.

strSearch = "Right([SerialNumber], Len(strSBSN)) = '" & strSBSN & "'"

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

rs.Find strSearch



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.
 

RE: Trim Search value on Data Access Page

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

RE: Trim Search value on Data Access Page

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

RE: Trim Search value on Data Access Page

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.


SELECT    Field1, Field2
FROM      TableName
WHERE     SerialNumber = strSBSN


    OR


SELECT    Field1, Field2
FROM      TableName
WHERE     Right(SerialNumber, Len(strSBSN)) = strSBSN


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.

RE: Trim Search value on Data Access Page

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

RE: Trim Search value on Data Access Page

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

RE: Trim Search value on Data Access Page

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

 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close