Full Version: Display substrings of string
UtterAccess Forums > Microsoft Access > Access Forms
MAbdul
Hi all,
don't know if this is possible, but i wanted to display substrings of a string, the field being split is a memo one with the substrings being seperated by semicolons ";", baring in mind i am using access 97 so do not have the split function available. I am using Dlookup to display the field in a textbox. This is what my code looks like:
CODE

Me.Displaytxtbox= DLookup("MemoField", "table", "CriteriaField='" & Forms![formname]![CriteriaTxtbox] & "'")

This is what the memo field will looks like:
Address1;Address2;Address3. I want to dispaly in format:
Address1
Address2
Address3
If this is not possible does anyone know how i may go about parsing substrings for a string using access 97.
Any help very much appreciated, thanks in advance,
MA.
cheekybuddha
Hi MA,
This link to Access Web should be able to help you do what you need .
It is designed to parse for commas, but you can replace them with a semi colon.
hth,
d
MAbdul
Hi D,
Thank you very much for that, i will look into it.
MA. thumbup.gif
cheekybuddha
No problem.
ill your address in MemoField have a trailing full stop? To achieve what you want you could use some code like this:
CODE
    Dim strAddress as String
    Dim i as integer, j as integer
    strAddress = DLookup("MemoField", "table", "CriteriaField='" & Forms![formname]![CriteriaTxtbox] & "'")
    If Right(strAddress, 1) = "." Then
        strAddress = Left(strAddress, Len(strAddress) - 1)
    End if
    j = CountCSWords(strAddress)
    For i = 1 to j
        Me.Displaytxtbox = Me.Displaytxtbox & GetCSWord(strAddress, i)
        If i < j Then
            Me.Displaytxtbox  = Me.Displaytxtbox & vbnewline
        End if
    Next i

However, are you sure you want to be storing addresses in this way? It is not really best database practice to do so, though you may have your reasons. What happens if an address line contains a semi colon?
You could always modify the above code to parse out the address line elements and store them in their own fields in you address table.
Anyway, hope that is of some use,
d
ps. code not tested - give me a shout if it's crap
MAbdul
Hi D,
hats fantastic, thank you very much for that, i was close but could not yet get a solution until now with the one you just provided. The reason the data is stored like that is because it is an imported file, i want to allow the user to select and view individual parts for searches. frown.gif
p.s do you know how i may store data like above format but in a comobox, i.e. so displays in list, all the substrings. confused.gif
Thanks again,
MA. thumbup.gif
cheekybuddha
You can modify the code a bit:
!--c1-->
CODE
    Dim strAddress as String
    Dim strSrc as string
    Dim i as integer, j as integer
    strAddress = DLookup("MemoField", "table", "CriteriaField='" & Forms![formname]![CriteriaTxtbox] & "'")
    If Right(strAddress, 1) = "." Then
        strAddress = Left(strAddress, Len(strAddress) - 1)
    End if
    j = CountCSWords(strAddress)
    For i = 1 to j
        strSrc = strSrc & GetCSWord(strAddress, i)
        If i < j Then
            strSrc = strSrc & ";"
        End if
    Next i
    Me.YourComboBoxName.RowSourceType = "Value List"
    Me.YourComboBoxName.RowSource = strSrc

****** OR you could go the simple route! ******
CODE
    Dim strAddress as String
    strAddress = DLookup("MemoField", "table", "CriteriaField='" & Forms![formname]![CriteriaTxtbox] & "'")
    Me.YourComboBoxName.RowSourceType = "Value List"
    Me.YourComboBoxName.RowSource = strAddress

Since the address is already semi-colon separated it will be formatted perfectly as the rowsource for a combobox or listbox!
hth,
d
MAbdul
Hi D,
You beauty, thank you very much for that. yayhandclap.gif
thumbup.gif MA. thumbup.gif
cheekybuddha
It makes me laugh when you go round the houses only to find you had the answer all along!!
est of luck with the rest,
d thumbup.gif
MAbdul
Hi D,
I have come across a bit of a problem. If the criteria field (below) has 2 records with the criteria field being the same (ie. duplicate) then it will only display the initial record (i.e. 1) how can i get it to display both?.
strAddress = DLookup("MemoField", "table", " CriteriaField ='" & Forms![formname]![CriteriaTxtbox] & "'")
Thanks in advance for any help,
MA.
cheekybuddha
Hi MA,
mm... do you mean that the table has 2 records with the same criteria?
DLookup will only return the first matching record that it comes accross.
To achieve what you want I think you may have to create a recordset and parse the elements from that.
Is that what you mean?
d
MAbdul
Hi D,
hats the problem, how would i go about creating the recordset?
M-.
cheekybuddha
Try something like this:
!--c1-->
CODE
    Dim rs As ADODB.Recordset
    Dim cn As ADODB.Connection
    Dim strSQL as string, strSrc as String
    ' Assign the connection object
    Set cn = CurrentProject.Connection
    ' Build SQL statement
    strSQL = "Select Memofield From table Where Criteriafield = '" & Forms!formname!CriteriaTxtbox & "';"
    ' Retrieve the recordset (this method will return a read-only recordset)
    Set rs = cn.Execute(strSQL)
    ' Test whether any records were returned
    If not rs.BOF And not rs.EOF Then
        ' Go to the first record
        rs.MoveFirst
        Do While Not rs.EOF
            ' Add the contents of Memofield to strSrc
            strSrc = strSrc & rs.Fields(0) & ";"
            ' If we're not at the last record got to the next one
            If Not rs.EOF Then
                rs.MoveNext
            End if
        Loop
        ' Trim trailing ";"
        strSrc = Left(strSrc, Len(strSrc) - 1)
        Me.YourComboBoxName.RowSourceType = "Value List"
        Me.YourComboBoxName.RowSource = strSrc
    Else
        ' No records matched
        Me.YourComboBoxName.RowSource = ""
    End if
    ' Destroy all object variables
    If Not rs Is Nothing Then
        If rs.State = adStateOpen Then rs.Close
    End if
    Set rs = nothing
    If Not cn Is Nothing Then
        If cn.State = adStateOpen Then cn.Close
    End if
    Set cn = Nothing

Ohaven't tested this so let me know if it doesn't work.
MAbdul
Hi d,
Thank you for the code, looks amazing, but got error message "compile error, user-defined type not defined" it highlights dim rs As ADODB.Recordset . I think it is the ADODB.
M-.
cheekybuddha
Ah, forgot to mention that you must set a reference to ActiveX Data Objects! I just saw that you are using Acc97 and it isn't set automatically.
On the Visual Basic Editor go to: Tools | References
A window will appear. Scroll down the list until you get to Microsoft ActiveX Data Objects 2.1 Library and select the checkbox beside it.
That should help.
d
MAbdul
Thanks again D,
early there, got one more error though "variable not defined" it highlights
Set cn = CurrentProject .Connection. Do i have to state what the curent project is?
M.
cheekybuddha
Add this line before the one giving the error:
!--c1-->
CODE
    Set cn = New ADODB.Connection

and see if that helps.
cheekybuddha
Also, do a compact and repair before tryimg to run the code again.
MAbdul
Hi D,
Tried both code befor error line and compact and repair but still get same error i'm af
MA.
cheekybuddha
Do you have that space after CurrentProject before the '.' or is that a typo?
MAbdul
Sorry that is a typo. CurrentProject is not listed in the help files for access 97? if that helps.
cheekybuddha
Hmmm... it's been such a long time since I used Acc97 that I can't remember what needs to be done here.
If someone reading knows then they might jump in.
Otherwise, we will have to use a DAO recordset instead of an ADO recordset.
My DAO is a little rusty, but if you give me a few minutes I'll see if I can jog the memory.
d
MAbdul
Hi D,
o problem, you have been incredibly helpful as it is, thanks again,
MA.
cheekybuddha
Try this:
!--c1-->
CODE
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL as string, strSrc as String
    ' Build SQL statement
    strSQL = "Select Memofield From table Where Criteriafield = '" & Forms!formname!CriteriaTxtbox & "';"
    ' Open recordset
    Set db = DBEngine(0)(0)
    Set rs = db.OpenRecordset(strSQL, dbOpenForwardOnly)
    ' Test whether any records were returned
    If rs.RecordCount > 0 Then
        ' Go to the first record
        rs.MoveFirst
        Do While Not rs.EOF
            ' Add the contents of Memofield to strSrc
            strSrc = strSrc & rs(0) & ";"
            ' If we're not at the last record got to the next one
            If Not rs.EOF Then
                rs.MoveNext
            End if
        Loop
        ' Trim trailing ";"
        strSrc = Left(strSrc, Len(strSrc) - 1)
        Me.YourComboBoxName.RowSourceType = "Value List"
        Me.YourComboBoxName.RowSource = strSrc
    Else
        ' No records matched
        Me.YourComboBoxName.RowSource = ""
    End if
    ' Destroy all object variables
    rs.Close
    Set rs = nothing
    Set db = Nothing

I hope it works - we may have to play around a little longer yet
d
MAbdul
I think so, got another error "Run-time error 3219, invalid operation." it highlights rs.MoveFirst
A.
cheekybuddha
Told you my DAO was a bit rusty!
omment out that line. You can't MoveFirst with a forward only recordset.
d
MAbdul
Hi D,
don't know about being rusty maybe a little weary maybe, but my hat (if i did have one) is off to you, that was fantastic, Thank you very much for that it did seem to do the trick notworthy.gif. I think i will get some good sleeping.gif tonight.
Thanks again,
thumbup.gif MA. thumbup.gif
cheekybuddha
Before celebrating, just do some tests to check that all addresses are returned when there are multiples - I don't fully trust the looping code yet. It should be right, but better make sure...
et me know if you get odd results, like one expected address doesn't show.
Otherwise, yayhandclap.gif
d
MAbdul
Hi D,
Can some tests looks great from where i am sitting, compute.gif thanks again,
MA. thumbup.gif
cheekybuddha
Good stuff MA,
hope you got the idea of what was happening.
Basically, we opened a recordset to do the job of DLookup (if you notice, the criteria etc are the same as you had before) but it can contain more than one record.
Then we stepped through the recordset one record at a time, adding the data to the rowsource of the combobox until we reached the end or the recordset (rs.EOF - I suppose it stands for End Of File).
Then we assigned the rowsource as before.
Finally, we released the memory associated with the objects we created (rs, db) - It is important to do this with every object you create using 'Set' otherwise you will end up with memory bloat and decreased performance (even crashing!).
That is why I suggested compact and repair after the errors, because we had not got as far as the code to destroy the objects from memory.
Hope I'm not teaching granny to suck eggs!
Good luck with the rest,
d thumbup.gif
MAbdul
Hi D,
The code is still looking good, thanks for the explanation. Don't worry your, i have studied pascal before to an advanced level, however my vba is still at a learning developmental stage.
Thanks again for the help,
MA. cool.gif
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.