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

HELP String Parsing Msaccess

P: 38
Hi there! I've been racking my brains out for 4 days and still not getting why is msaccess stubborn about string parsing, first I have a table named tblItems, this table has columns: ProductItem, ItemID. Now I want to search and count my items found and produce the result in a textbox and a label control called: txtItemResult and lblCount so I have the code
Expand|Select|Wrap|Line Numbers
  1. Public Sub StringFinder()
  2. Dim conn1 As ADODB.Connection
  3. Dim rsShowTable As ADODB.Recordset
  4. Dim iResult as Integer
  5. Dim iCounter as Integer
  6. DIm i as String
  7.  
  8. Set conn1 = New ADODB.Connection
  9. conn1.Provider = "Microsoft.Jet.OLEDB.4.0"
  10. conn1.Open "C:\Apps\dataPMC.mdb"
  11.  
  12. Set rsShowTable = New ADODB.Recordset
  13. rsShowTable.Open "tblItems", conn1, adOpenDynamic, adLockOptimistic, adCmdTable
  14.  
  15.  
  16. i= "NYL"
  17.  
  18.     iResult = StrComp(txtItemsResult.Value, i)
  19.     Select Case iResult
  20.     Case 1
  21.        MsgBox "the first string is greater than the second"
  22.  
  23.         'Iterate through each character
  24.  
  25.          startPosition = InStr(txtActivity.Value, 1)
  26.            iCount = iCount + 1
  27.              lblCount.Caption = iCount
  28.          Me.txtItemsResult.Value = rsShowTable!("ProductItem") 
  29.     Case Else
  30.             MsgBox "One or more strings are null"
  31.     End Select
  32. End sub
this produces unfamiliar errors for me and I appologize for the msgbox, I just want to see if I compared the right string. I know, that I should be using sql count(*) here, to be honestI should have but it dosent give what I want or my understanding is the problem (which is usually the case). The output should be like:

ProductItems
------------
Nylon
Nylon Cable
Nylon hose
Nylon string

there are: 4 items found

pls....pls....pls, anyone who can put me in the right direction would very appreciated.
Feb 14 '08 #1
Share this Question
Share on Google+
7 Replies


Expert Mod 2.5K+
P: 2,545
Hi there! I've been racking my brains out for 4 days and still not getting why is msaccess stubborn about string parsing, first I have a table named tblItems, this table has columns: ProductItem, ItemID. Now I want to search and count my items found and produce the result in a textbox and a label control called: txtItemResult and lblCount so I have the code

Public Sub StringFinder()
Dim conn1 As ADODB.Connection
Dim rsShowTable As ADODB.Recordset
Dim iResult as Integer
Dim iCounter as Integer
DIm i as String

Set conn1 = New ADODB.Connection
conn1.Provider = "Microsoft.Jet.OLEDB.4.0"
conn1.Open "C:\Apps\dataPMC.mdb"

Set rsShowTable = New ADODB.Recordset
rsShowTable.Open "tblItems", conn1, adOpenDynamic, adLockOptimistic, adCmdTable


i= "NYL"

iResult = StrComp(txtItemsResult.Value, i)
Select Case iResult
Case 1
MsgBox "the first string is greater than the second"

'Iterate through each character

startPosition = InStr(txtActivity.Value, 1)
iCount = iCount + 1
lblCount.Caption = iCount
Me.txtItemsResult.Value = rsShowTable!("ProductItem")
Case Else
MsgBox "One or more strings are null"
End Select
End sub




this produces unfamiliar errors for me and I appologize for the msgbox, I just want to see if I compared the right string. I know, that I should be using sql count(*) here, to be honestI should have but it dosent give what I want or my understanding is the problem (which is usually the case). The output should be like:

ProductItems
------------
Nylon
Nylon Cable
Nylon hose
Nylon string

there are: 4 items found

pls....pls....pls, anyone who can put me in the right direction would very appreciated.
Not sure what the object of all this is, but it sure looks a complex way to do things! SQL has the "like " operator to match substrings. To see how it works, without using code, add the base table to a query then add the ProductItem field to the query. Put "like [Item name or part of?]" in the ProductItem field criteria. This will pop up a parameter box when you run the query, you can then type NYL or anything else, and whatever matches there are will be shown in the query.

SQL for this is along the lines of

Expand|Select|Wrap|Line Numbers
  1. Select [ProductItems] from [name of your table] Having ([ProductItems] like [Item name or part of?]);
Your code has a number of errors: InStr is intended to find a match between two strings; you are supplying only one in the code shown. You don't need to iterate through each character. Not sure what icount is doing, but it is unnecessary unless you are searching for multiple occurrences of the substring in a main string. If you were, you would need to put icount in place of the 1 in the InStr.

If you could provide more detail about what it is you are really wanting to do, and the structure of your base queries/tables, I would be happy to help more. No need for code in this case, I am sure!

-Stewart
Feb 14 '08 #2

P: 38
Not sure what the object of all this is, but it sure looks a complex way to do things! SQL has the "like " operator to match substrings. To see how it works, without using code, add the base table to a query then add the ProductItem field to the query. Put "like [Item name or part of?]" in the ProductItem field criteria. This will pop up a parameter box when you run the query, you can then type NYL or anything else, and whatever matches there are will be shown in the query.

SQL for this is along the lines of

Expand|Select|Wrap|Line Numbers
  1. Select [ProductItems] from [name of your table] Having ([ProductItems] like [Item name or part of?]);
Your code has a number of errors: InStr is intended to find a match between two strings; you are supplying only one in the code shown. You don't need to iterate through each character. Not sure what icount is doing, but it is unnecessary unless you are searching for multiple occurrences of the substring in a main string. If you were, you would need to put icount in place of the 1 in the InStr.

If you could provide more detail about what it is you are really wanting to do, and the structure of your base queries/tables, I would be happy to help more. No need for code in this case, I am sure!

-Stewart
-------------------------------------------------------------------------------------------------------------
HI Stewart!
thank you for responding to my post. I apologize for providing a confusing code and hoping this second time I get it right. I hope I under stand youre request:
If you could provide more detail about what it is you are really wanting to do, and the structure of your base queries/tables, I would be happy to help more. No need for code in this case, I am sure! Let me start over, as I mentioned earlier ihave a table named: tblItems
with a field columns: itemID, ProductItem, ProdOrder, Comments

this is the structure:
Expand|Select|Wrap|Line Numbers
  1. itemID  ProductItems         ProdOrder  Comments
  2. ------  ------------         ---------  ---------------
  3. 01      Nylon Belt           03         ok
  4. 02      Nylon Hose           03         ok
  5. 03      Nylon String         03         ok
  6. 04      Tarlon               07         ok
  7. 05      Glass Field Nylon    03
  8. 06      Plastic              05
  9. 07      Plastic              05
***Im sorry if the table seemed mixed up, its the best I can do *******
** Edit ** Tidied up layout using [ CODE ] tags.
each field has been populated accrordingly, including my field ProductItem with Nylon as its listed items. now for the big brain crunching!

problem 1: I have a ProductItem and ProOrder sync with each other, its like where theres cheese theres a mouse behind it (forgive the analogy).

problem 2: My ProdOrder has repeated number with a different ProductItem description, that will have an effect in my searching later.

Now I planned my supper design for my table. my objective here is provide a search box that when a user typed in the string "NYL" it will provide the necessary ouput, so that is why I created a function StringFinder in VB, to find the specific string.
hence my code:
Expand|Select|Wrap|Line Numbers
  1. i="NYL"
  2.  
  3. iResult = StrComp(txtItemsResult.Value, i)
  4. Select Case iResult
  5. Case 1
  6. MsgBox "the first string is greater than the second"
  7.  
  8. 'Iterate through each character
  9.  
  10. startPosition = InStr(txtActivity.Value, 1)
  11. iCount = iCount + 1
  12. lblCount.Caption = iCount
  13. Me.txtItemsResult.Value = rsShowTable!("ProductItem") 
  14. Case Else
Of course I planed to put an IF Else condition there but I want to try if msaccess
would like my code first.

This may seemed over the top, but this is the only way I can think of, I know that sql provides a count and like functions but when I tried it, the output only provides a particular Item. ex. LIKE "NYL*" in access, it only shows all my nylon items. I really dont want my search to eliminate other items since they share ProdOrder. assuming I am successful in creating my search, the output would be: there are: 3 Nylon and 1 Glass Field Nylon found

I hope this does not complicate it more, coz I really need help here. Thanks gain!
Feb 15 '08 #3

Expert Mod 2.5K+
P: 2,545
Hi Cephalon. If you use an Access form to display your records you can add an unbound text box for a user to enter words to match. I have provided below a general search routine which checks whether or not there are any matches before applying a filter to the form. It is the filter that uses the 'like' clause I mentioned in my previous reply.

The code itself is for the AfterUpdate event of your unbound string match text box. In this version I refer to TitleText, the control in the form which this was taken from; substitute the name of the actual control for TitleText in the SearchFor line. I have changed the 'like' clause to refer to field ProdItems in your table.

Expand|Select|Wrap|Line Numbers
  1. Private Sub TitleText_AfterUpdate()
  2.     Dim Recs As DAO.Recordset, BookMark As Variant
  3.     Dim IsFound As Boolean, TheForm As Form
  4.     Dim SearchString As String, Response As VbMsgBoxResult, SearchFor As String
  5.     If Not IsNull(Me.TitleText) Then
  6.         SearchFor = Me.TitleText
  7.         SearchString = "[ProdItems] like '*" & SearchFor & "*'"
  8.         Me.FilterOn = False
  9.         Set Recs = EventForm.RecordsetClone
  10.         If Recs.RecordCount > 0 Then
  11.            Recs.MoveLast
  12.            Recs.FindFirst SearchString
  13.            If Recs.NoMatch Then
  14.                 Response = MsgBox("No match within products for '" & SearchFor & "'", vbExclamation + vbOKOnly, "No match")
  15.             Else
  16.                 Me.Filter = SearchString
  17.                 Me.FilterOn = True
  18.             End If
  19.         Else
  20.             Response = MsgBox("There are no products currently recorded", vbExclamation + vbOKOnly)
  21.         End If
  22.     End If
  23. End Sub
Try this out for your search and see how you get on.

Remember, the active lines that do the actual matching in the code are very few - the rest is simply checking to see that there is something to match. If I extract the matching bits these are just

Expand|Select|Wrap|Line Numbers
  1.       SearchFor = Me.TitleText
  2.       SearchString = "[ProdItems] like '*" & SearchFor & "*'"
  3.       Me.Filter = SearchString
  4.       Me.FilterOn = True
I have used the wildcard "*" on each side of the searchstring so it will return matching substrings from anywhere in the main string.

Hope you find some of this useful.

Cheers

Stewart
Feb 15 '08 #4

P: 38
Hi Cephalon. If you use an Access form to display your records you can add an unbound text box for a user to enter words to match. I have provided below a general search routine which checks whether or not there are any matches before applying a filter to the form. It is the filter that uses the 'like' clause I mentioned in my previous reply.

The code itself is for the AfterUpdate event of your unbound string match text box. In this version I refer to TitleText, the control in the form which this was taken from; substitute the name of the actual control for TitleText in the SearchFor line. I have changed the 'like' clause to refer to field ProdItems in your table.

Expand|Select|Wrap|Line Numbers
  1. Private Sub TitleText_AfterUpdate()
  2.     Dim Recs As DAO.Recordset, BookMark As Variant
  3.     Dim IsFound As Boolean, TheForm As Form
  4.     Dim SearchString As String, Response As VbMsgBoxResult, SearchFor As String
  5.     If Not IsNull(Me.TitleText) Then
  6.         SearchFor = Me.TitleText
  7.         SearchString = "[ProdItems] like '*" & SearchFor & "*'"
  8.         Me.FilterOn = False
  9.         Set Recs = EventForm.RecordsetClone
  10.         If Recs.RecordCount > 0 Then
  11.            Recs.MoveLast
  12.            Recs.FindFirst SearchString
  13.            If Recs.NoMatch Then
  14.                 Response = MsgBox("No match within products for '" & SearchFor & "'", vbExclamation + vbOKOnly, "No match")
  15.             Else
  16.                 Me.Filter = SearchString
  17.                 Me.FilterOn = True
  18.             End If
  19.         Else
  20.             Response = MsgBox("There are no products currently recorded", vbExclamation + vbOKOnly)
  21.         End If
  22.     End If
  23. End Sub
Try this out for your search and see how you get on.

Remember, the active lines that do the actual matching in the code are very few - the rest is simply checking to see that there is something to match. If I extract the matching bits these are just

Expand|Select|Wrap|Line Numbers
  1.       SearchFor = Me.TitleText
  2.       SearchString = "[ProdItems] like '*" & SearchFor & "*'"
  3.       Me.Filter = SearchString
  4.       Me.FilterOn = True
I have used the wildcard "*" on each side of the searchstring so it will return matching substrings from anywhere in the main string.

Hope you find some of this useful.

Cheers

Stewart
------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
HI Stewart!

I appologize for the delay replying to you, Im currently studying
the code using DAO and thank you very much for the explanation and sample code you provided,however I have a few question so I hope you dont mind, you said earlier that the code is for the AfterUpdate event for the textbox. Is this hit "EnterKey" after typing inside the string finder textbox, coz I add a cmdFind control within the code:
Expand|Select|Wrap|Line Numbers
  1. with cmdFind
  2. If Not IsNull(Me.txtTitle) Then
  3.         'Me.TextTitle = Recs.Fields(ProductItems).Value
  4.         SearchFor = Me.txtTitle
  5.         SearchString = "[ProductItems] like '*" & SearchFor & "*'"
  6.         Me.FilterOn = False
  7.     Set Recs = Form.RecordsetClone
  8.  
  9.     'theForm.RecordsetClone 'EventForm.RecordsetClone
  10.  
  11.             If Recs.RecordCount > 0 Then
  12.                 Recs.MoveLast
  13.                 Recs.FindFirst SearchString
  14.                     If Recs.NoMatch Then
  15.                     With cmdFind
  16.                     Response = MsgBox("No Match within products for '" & SearchFor & "'", vbExclamation + vbOKOnly, "No match")
  17.                     End With
  18.                     Else
  19.                         Me.Filter = SearchString
  20.                         Me.FilterOn = True
  21.                     End If
  22.                 Else
  23.                     Response = MsgBox("There are no products currently recorded", vbExclamation + vbOKOnly)
  24.             End If
  25.     End If 
  26. End With
and I seemed to get a cold response from it, I also tried hiting the enter key and no response and I think I'm quite confused with executing the vbExclamation, vbOkOnly functions.How about Bookmark, TheForm, how does this things comes into place.pls! provide a few explanation, this would be very helpful. Thank you again!
Feb 18 '08 #5

Expert Mod 2.5K+
P: 2,545
------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
HI Stewart!

I appologize for the delay replying to you, Im currently studying
the code using DAO and thank you very much for the explanation and sample code you provided,however I have a few question so I hope you dont mind, you said earlier that the code is for the AfterUpdate event for the textbox. Is this hit "EnterKey" after typing inside the string finder textbox, coz I add a cmdFind control within the code:

with cmdFind
If Not IsNull(Me.txtTitle) Then
'Me.TextTitle = Recs.Fields(ProductItems).Value
SearchFor = Me.txtTitle
SearchString = "[ProductItems] like '*" & SearchFor & "*'"
Me.FilterOn = False
Set Recs = Form.RecordsetClone

'theForm.RecordsetClone 'EventForm.RecordsetClone

If Recs.RecordCount > 0 Then
Recs.MoveLast
Recs.FindFirst SearchString
If Recs.NoMatch Then
With cmdFind
Response = MsgBox("No Match within products for '" & SearchFor & "'", vbExclamation + vbOKOnly, "No match")
End With
Else
Me.Filter = SearchString
Me.FilterOn = True
End If
Else
Response = MsgBox("There are no products currently recorded", vbExclamation + vbOKOnly)
End If
End If
End With

and I seemed to get a cold response from it, I also tried hiting the enter key and no response and I think I'm quite confused with executing the vbExclamation, vbOkOnly functions.How about Bookmark, TheForm, how does this things comes into place.pls! provide a few explanation, this would be very helpful. Thank you again!
Hi Cephalon. The AfterUpdate event of the textbox is indeed triggered by an enterkey press (or by data entry followed by mousing to another control). I'm not sure what CmdFind is - you are using a With statement to refer to it in two places, but don't appear to be referring to any of CmdFind's properties thereafter.

To add the AfterUpdate code, view the set of Properties of your StringFinder control (double-click on the control in Design View to bring up the properties tabs if not already showing), select the Event tab and you will see the list of events - including Before Update, After Update, and several others.

Right-click on the After Update event line and choose Builder from the menu. This places a sub procedure header and footer in the form's code, ready for you to add the body of the After Update routine. You should then copy the routine as provided in my previous reply, with any changes for the differing control names.

vbExclamation and vbOkOnly are actually system constants which give names to integer values and are simply more readable than referring to numbers. They are associated with the Message Box function.

Bookmark is an internal property that is used to match records between copied recordsets. As I mentioned, these are used in this routine just to check that there are some records to find - the bookmark property is not otherwise used. In other applications Bookmark can be used to go to a specific record by finding a match and then moving the record to the bookmark setting - just like placing a bookmark in a book - but I am not using this method in the routine provided.

TheForm is a local variable of mine of type Form, defined in the Dim statement associated with the code.

I would strongly suggest that you copy the code provided ' as is ' for the present, making only the change suggested to the control names to ensure that it fits your circumstances.

Regards

Stewart
Feb 18 '08 #6

P: 38
Hi Stewart!

Now I get IT!!! sorry for the delayed response but I'm still on the process of studying the code you provided, I hope it will be only a matter of time before I get my database Up n running smoothly.

Thanks for helping and explaining it more. It means a gazillion and more!
Feb 21 '08 #7

Expert Mod 2.5K+
P: 2,545
Delighted to have been able to assist, Cephalon.
Thanks for your kind words.
-Stewart
Feb 21 '08 #8

Post your reply

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