Solved

Use Instr to pull image names out of Memo field

Posted on 2003-10-23
25
282 Views
Last Modified: 2008-03-06
I have a table containing an id column and a content column (memo field).  Each row may contain imbedded links to images, there may be more than 1 link in each row.  I need to extract the image name, it is either in jpg, bmp or gif format.  Here is an example of the material.  

Determine which of the following is worth the most money.
<img border="0" src="/pictures/clip_quarter.gif">   <img border="0" src="/pictures/clip_dimes.gif">    <img border="0" src="/pictures/clip_nickel.gif">
<img border="0" src="/pictures/clip_penny.gif">
The result needs to be a table with the id filed, which will not be unique, and 1 row for each image.  Any help will be sincerely appreciated.  This level of query is beyond my expertise.  Thanks Brent
0
Comment
Question by:brentokc
  • 13
  • 9
  • 2
  • +1
25 Comments
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 9610560
Take a look at this preliminary solution and give me a feed back.

Public Function fnPictureFileName(IdVal As Long) As String
Dim rs 'As DOA.RecordSet
Set rs=CurrentDB.OpenRecordSet("Select * From MyTable Where PictureID=" & IdVal)
If rs.RecordCOunt>0 Then
rs.MoveFirst
Do Until rs.EOF
   
    Dim TempStr As String
    TempStr =fnPicStr(rs!MemoField, "gif") & ""
    If TempStr ="" Then
       TempStr =fnPicStr(rs!MemoField, "bmp") & ""
       If TempStr ="" Then
          fnPictureFileName="Link doesn't contain any gif or bmp file."
       Else
         fnPictureFileName=TempStr
       End If
    Else
        fnPictureFileName=TempStr
    End If
fnPictureFileName=

rs.MoveNext
Loop
End If
rs.Close

End If

Function fnPicStr(MemoVal As String, FormatVal As String) As String
' this function is being worked at.  It is not done.
If FormatVal ="gif" Then
   fnPicStr="Has Located a gif"
Elseif FormatVal ="bmp" Then
  fnPicStr="Has Located a bmp"
Else
  fnPicStr=""
End IF

End Function

Mike
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 9610572
Opps...

Public Function fnPictureFileName(IdVal As Long) As String
.
.
.
End Function '<---- *********this line is missing in my last post****************

Function fnPicStr(MemoVal As String, FormatVal As String) As String
.
.
.
End Function
0
 
LVL 3

Expert Comment

by:Drizzt95
ID: 9610940
Mike,

Correct me if I'm wrong but your not actually searching the memo field for any images (I know your just playing with an idea at the moment).

In the below I am assuming that all images start with a /.

You would need to change your function to:

Function fnPicStr(MemoVal As String, FormatVal As String) As String
' this function is being worked at.  It is not done.

If instr(1, FormatVal. ".gif") > 0 Then
  fnPicStr = mid(FormatVal, instr(1, FormatVal. "/"), instr(1, FormatVal. "gif") +3) 'plus three as the instr gives you the first value (could be 4 though)

'AND THE SAME FOR THE OTHERS
Elseif FormatVal ="bmp" Then
 fnPicStr="Has Located a bmp"
Else
 fnPicStr=""
End IF

End Function


I haven't thrown in looping through to find more than one in a memo field.
I just thought I would throw in a quick comment.

0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 9610961
Hi Drizzt95,

You are right.  Function fnPicStr(MemoVal As String, FormatVal As String) As String is not ready yet.

Do you want do a teamwork on it?  I was plannig to write it this evening, unless you choose to coordinate it with my code to help out brentokc,

Regards,

Mike
0
 
LVL 3

Expert Comment

by:Drizzt95
ID: 9611134
Mike,

I'm happy to let you have the lead on this one, I'll just poke my head in occassionaly and see how your going.

I've just moved house so I don't have the net connected yet (it should have been a priority) so I'm stuck just using the net at work, which means I'm out for the weekend.

Thanks
Ian
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 9611406
Revised...

Assumption: All file names starts with word "Clip" and end with either .gif or .bmp
There is a table called tImageFilesName with id (Number/Long) and ImageFile (text)
------------
Public Function fnPictureFileName(IdVal As Long) As String
Dim rs 'As DOA.RecordSet
Set rs=CurrentDB.OpenRecordSet("Select * From MyTable Where PictureID=" & IdVal)
If rs.RecordCOunt>0 Then
rs.MoveFirst
Do Until rs.EOF
   
        Dim TempStr As String
        Dim gif As Integer, bmp As Integer, leftChar As Integer, i As Integer
       TempStr = rs!MemoField & ""
10: For i=1 to 10 '<----  **** 10 file names per memo**********
        If Instr(TempStr,"Clip")>0 then
           'remove all leading string before first "Clip"
           TempStr =Mid(TempStr,Instr(TempStr,"Clip"))
           ' check to see either .gif or .bmp exists in the remaining string
           gif = InStr(TempStr,".gif")+4: bmp = InStr(TempStr,".bmp")+4
           If gif >4 or bmp>4 then
               leftChar =IIF(gif>bmp,bmp,gif)
               TempStr = Left(TempStr,leftChar)
               'add image file name along with id value to table tImageFilesName
               CurrentDB.Execute "Insert Into tImageFilesName (Id, ImageFile) Select " & IdVal & ", " & TempStr
           End If
              TempStr=Mid(TempStr,leftChar+1) & ""
              GoTo 10
        Else
           Exit For
        End IF
      Next i
rs.MoveNext
Loop
End If
rs.Close

End If

End Function

This should do it.

Mike
0
 
LVL 2

Expert Comment

by:cbail
ID: 9611814
I wanted to mess with regular expressions

Public Function ExtractPicture()
    'assumes that src is at the end of the tag
    'returns the whole path
    'Needs Regular Expressions  Library
    Dim lObjRegexp As New regexp
    Dim lObjMatches As MatchCollection
    Dim lobjMatch As Match
    Dim rs As DAO.Recordset
    Dim strPath As String
   
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM Table1")
   
    Do While Not rs.EOF
   
        lObjRegexp.Global = True
        lObjRegexp.Pattern = "src=[^>]+"
        lObjRegexp.IgnoreCase = True
       
        Set lObjMatches = lObjRegexp.Execute(rs!Content)
       
        For Each lobjMatch In lObjMatches
            If Len(Trim(lobjMatch.Value)) > 0 Then
                strPath = Right(lobjMatch.Value, Len(lobjMatch.Value) - 4)
                Call CurrentDb.Execute("INSERT INTO Table3 (ID, PicturePath) SELECT " & rs!ID & ", " & strPath)
                'Debug.Print "INSERT INTO Table3 (ID, PicturePath) SELECT " & rs!ID & ", " & strPath
            End If
        Next
    rs.MoveNext
    Loop
End Function

Chris
0
 

Author Comment

by:brentokc
ID: 9612177
eghtebas - Sorry, guess I used a bad example, all the images do not start with clip and there are also jpg images in the memo field <img border="0" src="pictures/10long2411.gif">.  The table name is QuestionContent and the column is also QuestionContent, the id column is QuestionID.  I think I made the substitutions in the right places.  I created the table as you noted.  I pasted your code into the SQL window and got and "invalid SQL statement".  Added the Select and then received a "The SELECT statement includes a reserved word or argument name that is misspelled or missing, or the punctuation is incorrect."  When I choose OK String is highlighted.  You should probably assume I am ignorant of Access, since what you are doing is way beyond what I understand.  I'm not sure a vowel will help, believe I need the whole alphabet.  Also have just been told I have to leave town in the morning and won't be able to be on line until Sunday night.  Guess I should add some points to this deal, huh.  Thanks Brent

SELECT Public Function fnQuestionContent(IdVal As Long) As String
Dim rs 'As DOA.RecordSet
Set rs=CurrentDB.OpenRecordSet("Select * From QuestionContent Where QuestionID=" & IdVal)
If rs.RecordCOunt>0 Then
rs.MoveFirst
Do Until rs.EOF
   
        Dim TempStr As String
        Dim gif As Integer, bmp As Integer, leftChar As Integer, i As Integer
       TempStr = rs!QuestionContent.QuestionContent & ""
10: For i=1 to 10 '<----  **** 10 file names per memo**********
        If Instr(TempStr,"Clip")>0 then
           'remove all leading string before first "Clip"
           TempStr =Mid(TempStr,Instr(TempStr,"Clip"))
           ' check to see either .gif or .bmp exists in the remaining string
           gif = InStr(TempStr,".gif")+4: bmp = InStr(TempStr,".bmp")+4
           If gif >4 or bmp>4 then
               leftChar =IIF(gif>bmp,bmp,gif)
               TempStr = Left(TempStr,leftChar)
               'add image file name along with id value to table tImageFilesName
               CurrentDB.Execute "Insert Into tImageFilesName (Id, ImageFile) Select " & IdVal & ", " & TempStr
           End If
              TempStr=Mid(TempStr,leftChar+1) & ""
              GoTo 10
        Else
           Exit For
        End IF
      Next i
rs.MoveNext
Loop
End If
rs.Close

End If

End Function
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 9612221
Could we assume they are all in a folder <pictures/>?   I will serach for pictures/ string knowing that file name starts after this string.

Don't worry about the points.  First we need to get it resoved.  I will modify to include any jpg and post it over the weekend.

Have a nice trip,

Mike
0
 

Author Comment

by:brentokc
ID: 9612244
Yes, that assumption is correct.

Thanks, the trip is to a park with no phone, no tv and no internet, its either heaven or hell - take your choice.  Actually it will be nice, good time of year to go, fall in southeast Oklahoma is nice.

Brent
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 9612283
That is exciting.  Good for you.

Mike

0
 

Author Comment

by:brentokc
ID: 9634069
Mike -

Does it look like you will have time to help me with this?

Thanks
Brent
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 9634794
I will work at it.  Thank you for reminding me.  Mike
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 9638775
Assumptions:

1. All file names are in folder called: pictures/
2. Always "pictures/" is followed by a file name (any extention type)
3. And, file formats (any kind) all have three letter extention after .

like:  .gif   .bmp   .pcx  and so on.

There is a table called tImageFilesName with id (Number/Long) and ImageFile (text)
------------
Public Function fnPictureFileName(IdVal As Long) As String
Dim rs 'As DOA.RecordSet
Set rs=CurrentDB.OpenRecordSet("Select * From MyTable Where PictureID=" & IdVal)
If rs.RecordCOunt>0 Then
rs.MoveFirst
Do Until rs.EOF
   
        Dim TempStr As String
        Dim leftChar As Integer, i As Integer,FileName As String
       TempStr = rs!MemoField & ""
10: For i=1 to 10 '<----  **** 10 file names per memo**********
        If Instr(TempStr,"pictures/")>0 then
           'remove all leading string to the end of first "pictures/"
           TempStr =Mid(TempStr,Instr(TempStr,"pictures/")+9)
           ' locate first dot after pictures/
           FileName = Left(TempStr,InStr(TempStr,".")+3)
               'add image file name along with id value to table tImageFilesName
               CurrentDB.Execute "Insert Into tImageFilesName (Id, ImageFile) Select " & IdVal & ", " & TempStr
           End If
              TempStr=Mid(TempStr,InStr(TempStr,".")+4) & ""
              GoTo 10
        Else
           Exit For
        End IF
      Next i
rs.MoveNext
Loop
End If
rs.Close

End If

End Function

Sorry for the delay.

Mike
0
 

Author Comment

by:brentokc
ID: 9639040
Mike -

Delay, I don't care about any delay; I was potentially looking at a cut and paste operation - this is a life saver.  I will start using it as soon as I get in the office tomorrow.  Will get back to you then.

Thanks,
Brent
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 9639179
correction... replace:

CurrentDB.Execute "Insert Into tImageFilesName (Id, ImageFile) Select " & IdVal & ", " & TempStr

with:

CurrentDB.Execute "Insert Into tImageFilesName (Id, ImageFile) Select " & IdVal & ", " & FileName

Mike
0
 

Author Comment

by:brentokc
ID: 9644386
Mike -
I have copied the code into a module - when I try and compile I get an "else without if" error at the following else.
GoTo 10
        Else
           Exit For

After a successful compile I assume I can run the module by clicking on !run in the database window and it will populate the tImageFilesName table.  I substituted QuestionContent for MyTable and QuestionID for PictureID, where those the only changes I needed to make?

I have never worked with modules before so your guidance is much appreciated.
Brent
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 9644643
After creating a new table called tImageFilesName with id (Number/Long) and ImageFile (text), add a new button on your form.  In OnClick event of this button include.
(Note: In this code, change Table1 which has MemeoFildName and ID as required):

Dim rst 'As DOA.RecordSet
CurrentDB.Execute "Delete * From tImageFilesName"
Set rst=CurrentDB.OpenRecordSet("Select ID, MemeoFildName From Table1")
If rst.RecordCount>0 Then
rst.movefirst
Do Until rst.EOF
    PictureFileName rst!ID, rst!MemeoFildName
rst.MoveNext
Loop
End If
rst.close
'------------------
And, in a standard module, under module tab, paste:

Public Sub PictureFileName(IdVal As Long, MemoVar As String)
       Dim TempStr As String, i As Integer,FileName As String
       TempStr = MemoVar
10:  For i=1 to 10 '<----  **** 10 file names per memo**********
           If Instr(TempStr,"pictures/")>0 then
              'remove all leading string to the end of first "pictures/"
               TempStr =Mid(TempStr,Instr(TempStr,"pictures/")+9)
              ' locate first dot after pictures/
              FileName = Left(TempStr,InStr(TempStr,".")+3)
               'add image file name along with id value to table tImageFilesName
               CurrentDB.Execute "Insert Into tImageFilesName (Id, ImageFile) Select " & IdVal & ", " & TempStr
           End If
              TempStr=Mid(TempStr,InStr(TempStr,".")+4) & ""
              GoTo 10
        Else
           Exit For
        End IF
      Next i

End Sub

'---------
Now, when you click on the button discussed in the beginning of this post, the existing content of tImageFilesName will be refreshed.

Mike
0
 

Author Comment

by:brentokc
ID: 9645105
Mike -
Have done all you indicated in last post - button on form seems to work - but still am receiving the compile error - else without if.  
"GoTo 10
        Else
           Exit For
End If
      Next i" - the else is highlighted.
Brent
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 9645339
use:

Public Sub PictureFileName(IdVal As Long, MemoVar As String)
       Dim TempStr As String, i As Integer,FileName As String
       TempStr = MemoVar
10:  For i=1 to 10 '<----  **** 10 file names per memo**********
           If Instr(TempStr,"pictures/")>0 then
              'remove all leading string to the end of first "pictures/"
               TempStr =Mid(TempStr,Instr(TempStr,"pictures/")+9)
              ' locate first dot after pictures/
              FileName = Left(TempStr,InStr(TempStr,".")+3)
               'add image file name along with id value to table tImageFilesName
               CurrentDB.Execute "Insert Into tImageFilesName (Id, ImageFile) Select " & IdVal & ", " & TempStr
              TempStr=Mid(TempStr,InStr(TempStr,".")+4) & ""
              GoTo 10
        Else
           Exit For
        End IF
      Next i

End Sub

sorry,
0
 

Author Comment

by:brentokc
ID: 9645510
Mike -
The change returns the first instance of the images, but it also returns the following error:  Runtime error '3075": Syntax error (missing operator) in query expression 'clip_quarter.gif"><img border="0" src="/pictures/clip_dimes.gif"><img border="0" src="/pictures/clip_nickel.gif"><img border="0" src="/pictures'clip_penny.gif">'.  When I choose debug the following line is highlighted: "CurrentDb.Execute "Insert Into tImageFilesName (Id, ImageFile) Select " & IdVal & ", " & TempStr".

The info referenced in the runtime error dialog is from questionid 54, the first question that contains any images, and it contains the 4 gifs listed, in the memo field(QuestionContent.QuestionContent).  That is, all in the same field.
Brent
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 9645591
Change:
               
.
.
CurrentDB.Execute "Insert Into tImageFilesName (Id, ImageFile) Select " & IdVal & ", " & TempStr
TempStr=Mid(TempStr,InStr(TempStr,".")+3) & ""
GoTo 10
.
.

If still didn't work, send a zipped version to eghtebas at hotmail dot com

Mike


0
 

Author Comment

by:brentokc
ID: 9646018
Mike -

Change didn't make any difference - removed extraneous material and sent you the zipped copy - it is in Access 2003, let me know if that is not alright.
Thanks
Brent
0
 
LVL 33

Accepted Solution

by:
Mike Eghtebas earned 500 total points
ID: 9646810
Change:

CurrentDB.Execute "Insert Into tImageFilesName (Id, ImageFile) Select " & IdVal & ", " & TempStr

To:

CurrentDb.Execute "Insert Into tImageFilesName (Id, ImageFile) Select " & IdVal & ", '" & FileName & "'"

Mike
0
 

Author Comment

by:brentokc
ID: 9651480
Outstanding - it works great - the amount of time saved is extraordinary.

Thanks
Brent
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now