By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,594 Members | 2,016 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,594 IT Pros & Developers. It's quick & easy.

Problem with searching due to spaces

P: 4
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
Share this Question
Share on Google+
7 Replies


sashi
Expert 100+
P: 1,754
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

100+
P: 1,646
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

P: 4
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 100+
P: 1,754
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

P: 4
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 5K+
P: 8,434
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

P: 4
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.