467,151 Members | 925 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,151 developers. It's quick & easy.

Problem with searching due to spaces

Hi there,
I had wrote a program using vb that take this Input string "061003C1DBN11N100MACH1" (without spaces) from Barcode Scanner in order to find the String "B 061003C1 DBN1 1 N100 MACH1" (with spaces) in MS Access Database. However it doens't match the record due to the spaces.
It only works if i spacing the input string like in the format in database. Any suggestion on solving this problem? Thanks

This is the search code that i used

'BarcodeSearch_form.Show

Private Sub BarcodeSearch_Click()

Dim strBarcode As String
Dim strSQL As String

'Perform Barcode Search

strBarcode = Barcode.Text

'Show the specimen status with the relevant Barcode
strSQL = "SELECT SpecimenName, Company, Material, Test, C/A, SpecsNeeded, Project, Status, Date, Time FROM Tracking WHERE AssignmentString= '" & strBarcode & "'"
AdodcBarcode.RecordSource = strSQL
AdodcBarcode.Refresh
BarcodeSearch_form.Hide
BarcodeResult_form.Show


End Sub
Nov 20 '06 #1
  • viewed: 1063
Share:
7 Replies
sashi
Expert 1GB
Hi there,
I had wrote a program using vb that take this Input string "061003C1DBN11N100MACH1" (without spaces) from Barcode Scanner in order to find the String "B 061003C1 DBN1 1 N100 MACH1" (with spaces) in MS Access Database. However it doens't match the record due to the spaces.
It only works if i spacing the input string like in the format in database. Any suggestion on solving this problem? Thanks

This is the search code that i used

'BarcodeSearch_form.Show

Private Sub BarcodeSearch_Click()

Dim strBarcode As String
Dim strSQL As String

'Perform Barcode Search

strBarcode = Barcode.Text

'Show the specimen status with the relevant Barcode
strSQL = "SELECT SpecimenName, Company, Material, Test, C/A, SpecsNeeded, Project, Status, Date, Time FROM Tracking WHERE AssignmentString= '" & strBarcode & "'"
AdodcBarcode.RecordSource = strSQL
AdodcBarcode.Refresh
BarcodeSearch_form.Hide
BarcodeResult_form.Show


End Sub
Hi there,

Refer to below code segment, does it rings any bell. Hope it helps. Good luck & Take care.

Expand|Select|Wrap|Line Numbers
  1.   Dim txt As String
  2.  
  3.   txt="This is a beautiful day!"
  4.   msgbox (Left(txt,11))
  5.  
Kindly refer to below attached link for further reading & understanding.

http://msdn2.microsoft.com/en-us/lib...wb(VS.80).aspx
Nov 20 '06 #2
1GB
Hi there,
I had wrote a program using vb that take this Input string "061003C1DBN11N100MACH1" (without spaces) from Barcode Scanner in order to find the String "B 061003C1 DBN1 1 N100 MACH1" (with spaces) in MS Access Database. However it doens't match the record due to the spaces.
It only works if i spacing the input string like in the format in database. Any suggestion on solving this problem? Thanks

This is the search code that i used

'BarcodeSearch_form.Show

Private Sub BarcodeSearch_Click()

Dim strBarcode As String
Dim strSQL As String

'Perform Barcode Search

strBarcode = Barcode.Text

'Show the specimen status with the relevant Barcode
strSQL = "SELECT SpecimenName, Company, Material, Test, C/A, SpecsNeeded, Project, Status, Date, Time FROM Tracking WHERE AssignmentString= '" & strBarcode & "'"
AdodcBarcode.RecordSource = strSQL
AdodcBarcode.Refresh
BarcodeSearch_form.Hide
BarcodeResult_form.Show


End Sub
Hi. Does the format of the spaced string always remain constant i.e.
1 character + 8 characters + 4 characters etc?
Nov 20 '06 #3
Hi. Does the format of the spaced string always remain constant i.e.
1 character + 8 characters + 4 characters etc?

Yes...its a constant naming format. (1 character + 8 characters + 4 characters+1 charachers+4 characthers+ 5 characthers)
(B 061003C1 DBN1 1 N100 MACH1)
every string will be in such format in database.
Nov 20 '06 #4
sashi
Expert 1GB
Yes...its a constant naming format. (1 character + 8 characters + 4 characters+1 charachers+4 characthers+ 5 characthers)
(B 061003C1 DBN1 1 N100 MACH1)
every string will be in such format in database.
Hi there,

In that case, the Left() function will do the work, hope it helps. Good luck & take care.
Nov 20 '06 #5
Sashi... i knew that command it just trim away sumthing that u dun wan but it doesn't apply to my situation. Anyway thanks for ur help!
maybe i didn't explain very clear at the first place, i'm having problems to match the input string "061003C1DBN11N100MACH1" (without spaces) with the strings in ms access database "B 061003C1 DBN1 1 N100 MACH1" (with spaces) to perform a search The spaces in between the stings in Ms Access is causing the problem.

P.S. The input format (without spaces) will be constant like that and so as the database (with spaces).

So should i wrote a code to add spaces in between for the input string or any better code for the SQL search? Or perhaps something i should modify in ms access? Really appreciated ...Thanks!
Nov 20 '06 #6
Expert 8TB
If the format of the input and the database entries are always going to remain constant, then at the simplest you have two options.

(1)
Modify your database entries (or more likely, add another field) to hold the same format as the input, or

(2)
Reformat the scanner input to match the database entries.

Personally I think that option 2 makes more sense.

Option 1 would require you to either use up more storage space (minor concern I know, but it is there) or reformat whenever you pull the value out of the database for any other purpose.

Option 2, on the other hand, requires only a simple reformat (probably one statement concatenating a few Left( ) and Mid( ) values at the time you receive the scanner input). In fact, here's a function which should do it...
Expand|Select|Wrap|Line Numbers
  1. Public Function SpacedOut(ByVal ScannerInput As String) As String
  2. SpacedOut = "B " & Left(ScannerInput, 8) & " " & Mid(ScannerInput, 9, 4) & " " & Mid(ScannerInput, 13, 1) & " " & Mid(ScannerInput, 14, 4) & " " & Mid(ScannerInput, 18)
  3. End Function
There might also be some way to use regular expressions to do the match, but I'm not sure.
Nov 20 '06 #7
If the format of the input and the database entries are always going to remain constant, then at the simplest you have two options.

(1)
Modify your database entries (or more likely, add another field) to hold the same format as the input, or

(2)
Reformat the scanner input to match the database entries.

Personally I think that option 2 makes more sense.

Option 1 would require you to either use up more storage space (minor concern I know, but it is there) or reformat whenever you pull the value out of the database for any other purpose.

Option 2, on the other hand, requires only a simple reformat (probably one statement concatenating a few Left( ) and Mid( ) values at the time you receive the scanner input). In fact, here's a function which should do it...
Expand|Select|Wrap|Line Numbers
  1. Public Function SpacedOut(ByVal ScannerInput As String) As String
  2. SpacedOut = "B " & Left(ScannerInput, 8) & " " & Mid(ScannerInput, 9, 4) & " " & Mid(ScannerInput, 13, 1) & " " & Mid(ScannerInput, 14, 4) & " " & Mid(ScannerInput, 18)
  3. End Function
There might also be some way to use regular expressions to do the match, but I'm not sure.
Thanks.. Killer42.. Ur Option2 is wat i'm looking for... Really appreciate ur help and for those who offerec help in this thread too..
:)
Nov 20 '06 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

12 posts views Thread by rbt | last post: by
2 posts views Thread by ajitgoel@gmail.com | last post: by
2 posts views Thread by Frank Millman | last post: by
4 posts views Thread by Jordan S. | last post: by
1 post views Thread by Tim | last post: by
11 posts views Thread by Lothar Behrens | last post: by
5 posts views Thread by Raj | last post: by
13 posts views Thread by Rafe | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.