Problem with searching due to spaces

[Carmaniac]

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

[sashi]

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.

  Dim txt As String
 
  txt="This is a beautiful day!"
  msgbox (Left(txt,11))
 

Kindly refer to below attached link for further reading & understanding.

http://msdn2.microsoft.com/en-us/lib...wb(VS.80).aspx

[willakawill]

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?

[Carmaniac]

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.

[sashi]

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.

[Carmaniac]

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!

[Killer42]

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

Public Function SpacedOut(ByVal ScannerInput As String) As String
SpacedOut = "B " & Left(ScannerInput, 8) & " " & Mid(ScannerInput, 9, 4) & " " & Mid(ScannerInput, 13, 1) & " " & Mid(ScannerInput, 14, 4) & " " & Mid(ScannerInput, 18)
End Function

There might also be some way to use regular expressions to do the match, but I'm not sure.

[Carmaniac]

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

Public Function SpacedOut(ByVal ScannerInput As String) As String
SpacedOut = "B " & Left(ScannerInput, 8) & " " & Mid(ScannerInput, 9, 4) & " " & Mid(ScannerInput, 13, 1) & " " & Mid(ScannerInput, 14, 4) & " " & Mid(ScannerInput, 18)
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..
:)