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....p ls, anyone who can put me in the right direction would very appreciated.
7 2616
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.Connectio n
Dim rsShowTable As ADODB.Recordset
Dim iResult as Integer
Dim iCounter as Integer
DIm i as String
Set conn1 = New ADODB.Connectio n
conn1.Provider = "Microsoft.Jet. OLEDB.4.0"
conn1.Open "C:\Apps\dataPM C.mdb"
Set rsShowTable = New ADODB.Recordset
rsShowTable.Ope n "tblItems", conn1, adOpenDynamic, adLockOptimisti c, adCmdTable
i= "NYL"
iResult = StrComp(txtItem sResult.Value, i)
Select Case iResult
Case 1
MsgBox "the first string is greater than the second"
'Iterate through each character
startPosition = InStr(txtActivi ty.Value, 1)
iCount = iCount + 1
lblCount.Captio n = iCount
Me.txtItemsResu lt.Value = rsShowTable!("P roductItem")
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....p ls, 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 - 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
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 - 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: - itemID ProductItems ProdOrder Comments
-
------ ------------ --------- ---------------
-
01 Nylon Belt 03 ok
-
02 Nylon Hose 03 ok
-
03 Nylon String 03 ok
-
04 Tarlon 07 ok
-
05 Glass Field Nylon 03
-
06 Plastic 05
-
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: - 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
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!
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. - Private Sub TitleText_AfterUpdate()
-
Dim Recs As DAO.Recordset, BookMark As Variant
-
Dim IsFound As Boolean, TheForm As Form
-
Dim SearchString As String, Response As VbMsgBoxResult, SearchFor As String
-
If Not IsNull(Me.TitleText) Then
-
SearchFor = Me.TitleText
-
SearchString = "[ProdItems] like '*" & SearchFor & "*'"
-
Me.FilterOn = False
-
Set Recs = EventForm.RecordsetClone
-
If Recs.RecordCount > 0 Then
-
Recs.MoveLast
-
Recs.FindFirst SearchString
-
If Recs.NoMatch Then
-
Response = MsgBox("No match within products for '" & SearchFor & "'", vbExclamation + vbOKOnly, "No match")
-
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 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 - SearchFor = Me.TitleText
-
SearchString = "[ProdItems] like '*" & SearchFor & "*'"
-
Me.Filter = SearchString
-
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 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. - Private Sub TitleText_AfterUpdate()
-
Dim Recs As DAO.Recordset, BookMark As Variant
-
Dim IsFound As Boolean, TheForm As Form
-
Dim SearchString As String, Response As VbMsgBoxResult, SearchFor As String
-
If Not IsNull(Me.TitleText) Then
-
SearchFor = Me.TitleText
-
SearchString = "[ProdItems] like '*" & SearchFor & "*'"
-
Me.FilterOn = False
-
Set Recs = EventForm.RecordsetClone
-
If Recs.RecordCount > 0 Then
-
Recs.MoveLast
-
Recs.FindFirst SearchString
-
If Recs.NoMatch Then
-
Response = MsgBox("No match within products for '" & SearchFor & "'", vbExclamation + vbOKOnly, "No match")
-
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 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 - SearchFor = Me.TitleText
-
SearchString = "[ProdItems] like '*" & SearchFor & "*'"
-
Me.Filter = SearchString
-
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,howeve r 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 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,howeve r 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.txtTi tle) Then 'Me.TextTitle = Recs.Fields(Pro ductItems).Valu e SearchFor = Me.txtTitle SearchString = "[ProductItems] like '*" & SearchFor & "*'" Me.FilterOn = False Set Recs = Form.RecordsetC lone 'theForm.Record setClone 'EventForm.Reco rdsetClone If Recs.RecordCoun t > 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
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!
Delighted to have been able to assist, Cephalon.
Thanks for your kind words.
-Stewart
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: wukexin |
last post by:
Help me, good men. I find mang books that introduce bit "mang header
files",they talk too bit,in fact it is my too fool, I don't learn it, I have
do a test program, but I have no correct doing result in any way. Who can
help me, I thank you very very much.
list.cpp(main program)...
|
by: bj0011 |
last post by:
I am trying to parse an XML file obtained from a public WebService (see below). This file does not seem to be loading into a .NET XmlDocument right (I think it is because of all of the <Item Name="" Type=""></Item> elements). I need to get out the data for the <Id/>, Name="Authors", Name="Title", and Name="PubDate" elements. Anyone have an idea...
|
by: Siegfried Heintze |
last post by:
I refered the engineer at my hosting service to
http://support.microsoft.com/default.aspx?scid=kb;en-us;825738 where he
tried to follow the directions there.
He said there was no such file: Document settings\ServerName\ASPNET\Local
settings\Temp
I said it must be a typo, they meant to say c:\Documents And Settings\Server...
|
by: Gert v O |
last post by:
Can someone help me parsing this ms-access PIVOT sql-statement to a
ms-sql-server sql-statement?
Many thanks in advance
TRANSFORM Count(KlantenStops.id) AS AantalVanid
SELECT KlantenStops.Uitvoerder, KlantenStops.Klant
FROM KlantenStops
GROUP BY KlantenStops.Uitvoerder, KlantenStops.Klant
PIVOT DatePart("m",leverdatum,1,0) In...
|
by: sommarlov |
last post by:
Hi everyone
>From one of our systems an xml file is produced. I need to validate
this file before we send it to an external system for a very lenghty
process. I cannot change the xml file layout.
The solution i got today is very slow, and i need help to find another
solution.
Here is the xml file. It consists of a list of position ids...
| |
by: syhzaidi |
last post by:
How can we do Parsing of Hexdecimel in C# reading string from stream file for eg..
i have a file like..........
0f 2f 12 2d 3a.......in hexa decimal save in a file.txt and i m reading it from the file.......
now i have to convert this in decimal and save in an array.of integers.......i thought it can be achieved through parsing ..means 0f...
|
by: Paulers |
last post by:
Hello,
I have a log file that contains many multi-line messages. What is the
best approach to take for extracting data out of each message and
populating object properties to be stored in an ArrayList? I have tried
looping through the logfile using regex, if statements and flags to
find the start and end of each message but I do not see a...
|
by: cephal0n |
last post by:
I have this peroblem thats really bugging me for days, please have a patience to read it and help me find the probplem because I knew I missed it and just cant tell where.
I have a table named tblProuctSummary and it has the 6 fields:
ItemID - for unique key
BuyerA - for those who made the buying
BuyerB - for another buyer
Manufacturer -for...
|
by: eyeore |
last post by:
Hello everyone my String reverse code works but my professor wants me to use pop top push or Stack code and parsing code could you please teach me how to make this code work with pop top push or Stack code and parsing code my professor i does not like me using buffer reader on my code and my professor did even give me an example code for parsing...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |