none
Newbie Search Questions RRS feed

  • Question

  • Hi all

    Couple of basic issues I'm struggling with here! Access 2007/2010.

    I have drawing numbers that are defined as hyperlinks. When I try to search for a specific drawing number I get a 3705 error. The search is done via a where clause in a search form if that makes a difference. It is important that this field is searchable and hyperlinks to external PDF files. Any thoughts please?

    When I try to use a * wildcard in my search form I get an error. Do I have to define the * as a wildcard somewhere?

    Thanks

    Phil

    Saturday, November 14, 2015 11:09 AM

Answers

  • 1.  and btw, you forgot wildcards ...

    2.  anyway, hyperlinks cannot do wildcards
    1.  Actually, I didn't.  The OP is entering the asterisk into the txtDwg control, so there is no need to concatenate it into the string expression.

    2.  Exactly; which is why, in my first reply, I advised him not to use a column of hyperlink data type.  A hyperlink value is a construct, whereas a text value is what it says on the tin, text.  As well as giving rise to the sort of problems the OP is experiencing, my biggest reservation about hyperlink columns is their lack of portability.  Portability of data used to figure strongly when I was first learning about relational databases, but one rarely hears it mentioned these days.  For those of us working a scientific environment it is important.


    Ken Sheridan, Stafford, England

    • Marked as answer by TheHC Tuesday, November 17, 2015 12:24 PM
    Sunday, November 15, 2015 10:31 AM

All replies

  • I'm not a great fan of storing links as a hyperlink data type.  My usual approach is to store the path in a column of text data type.  You'll find an example as BrowsDemo.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates how to browse to a file and insert its path into a control in a form.  The path is formatted as a hyperlink by setting its IsHyperlink property to True (Yes), and code in the control's Click event procedure calls the cmdOpen button's Click event procedure, though the code could be put directly in the text box's Click event procedure if you wish to dispense with a button.

    In my demo the control is unbound, but you might find you'd also need to set its DisplayAsHyperlink property to 'Always' in the case of a control bound to a text column.

    A text column is easily searchable of course.  When using the asterisk as a wildcard character you don't have to define it as such; just use the LIKE operator rather than the equality (=) operator.

    My demo opens the file by calling the Windows API ShellExecute function, as this also allows an argument to be passed into the function to print a file rather than opening it, but if you only wish to view the files then you can call the inbuilt FollowHyperlink method instead.  In the past this was not always reliable in the case of PDF files, but I think those problems have now been overcome.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Saturday, November 14, 2015 1:37 PM Typo corrected.
    Saturday, November 14, 2015 1:36 PM
  • Hey Ken

    Thanks for the detailed response re hyperlinks. I seem to remember encountering similar issues with Excel many moons ago.

    As for the use of the * character in searches, I don't think my details were clear.

    An existing Access database we use (created by a third party) allows the asterisk character to be entered into a search form as a wildcard. E. G. A5604* returns all drawings with numbers beginning with A5604. If I try this in my database I get the 3705 error. Is the use of the asterisk not compatible with a concatenated where clause?

    Thanks again

    Phil

    Saturday, November 14, 2015 3:13 PM
  • Please post your SQL and identify the field or fields that you're trying to use to build your WHERE clause.
    Saturday, November 14, 2015 3:22 PM
  • You don't describe the exact context in which the search form is operating, but let's assume you have another form bound to the table containing the file paths, that the search form is opened in dialogue mode from this bound form, and in the search form you have a text box named txtPath, the code in the search form's module to filter the bound form would be like this:

        Dim frm As Form
        Dim strFilter As String

        Set frm = Forms("YourBoundFormNameGoesHere")
        
        If Not IsNull(Me.txtPath) Then
            strFilter = "ImageFilePath Like """ & Me.txtPath & "*"""
            frm.Filter = strFilter
            frm.FilterOn = True
        Else
            frm.FilterOn = False
        End If

    Or if the results are presented in a form or report whose RecordSource is a query which references the txtPath control in the dialogue form the query would be like this:

    SELECT *
    FROM ImagePaths
    WHERE (ImageFilePath LIKE Forms!YourDialogueForm!txtPath & "*"
    OR Forms!YourDialogueForm!txtPath IS NULL);

    in which case the code in the dialogue form's module would either open the form or report, or requery the form if it is currently open.

    Ken Sheridan, Stafford, England

    Saturday, November 14, 2015 3:42 PM
  • An existing Access database we use (created by a third party) allows the asterisk character to be entered into a search form as a wildcard. E. G. A5604* returns all drawings with numbers beginning with A5604. If I try this in my database I get the 3705 error. Is the use of the asterisk not compatible with a concatenated where clause?

    Hi Phil,

    I can imagine the asterisk is built in in the filename for the hyperlink, and the program that accepts the hyperlink does not like asterisks.

    Imb.

    Saturday, November 14, 2015 3:44 PM
  • if you allow wildcards in your data, then they should be stored as text and code should find and open the files.

    For instance:

    'at top of module: Option Base 1 'start arrays at 1 'procedure to read files into array and open Sub LoopFilesAndOpen() '151114, strive4peace dim sPathFile as string _ , sPath as string _ , sFilename as string _ , sMask as string _ , i as integer Dim arrFile() As String with me.controlname_with_mask if isnull(.value) then exit sub sMask = .value & ".dwg" '---- or whatever is your extension end with sPath = "c:\path\" 'path to your files - can be determined by code too sFilename = Dir(sPath & sMask) 'ie: c:\folder\A5604*.dwg i = 1 ReDim arrFile(1) arrFile(1) = sFilename

    'load files matching mask into an array Do While sFilename <> "" If (GetAttr(sPath & "\" & arrFile(i)) And vbDirectory) <> vbDirectory Then i = i + 1 ReDim Preserve arrFile(i) end if 'get next filename sFilename = Dir() arrFile(i) = sFilename End If Loop 'remove last entry which is blank If i > 1 Then ReDim Preserve arrFile(i - 1) 'open all the files If Not UBound(arrFile) > 0 Then 'No Files exit sub End If 'loop through specified files and open For i = LBound(arrFile) To UBound(arrFile) sPathFile = sPath & arrFile(i) application.followhyperlink sPathFile next i end sub




    Warm Regards, Crystal http://www.AccessMVP.com/strive4peace * (: have an awesome day :)


    Saturday, November 14, 2015 8:13 PM
  • Hello all

    Thanks for the various suggestions.

    Unfortunately I think I may have caused some confusion by including two questions in the original post.

    The issue with the hyperlink field is that, even if I enter the correct content of the field into a search, no result is returned from my query.  The field contains a drawing number and in the table the field is defined as hyperlink.  The actual path to the file has been added by right clicking the drawing number in the field and selecting Edit Hyperlink.

    The section of my Where clause relating to this field is:

    If Not IsNull(txtDwg) Then
    If whereclause <> "" Then whereclause = whereclause & " AND "
    whereclause = whereclause & "[Drawing] = '" & txtDwg & "'"
    End If

    Is the format of this section incorrect when querying hyperlink field content?

    The second issue relates to the use of an asterisk in general as a wildcard in form based whereclause queries.

    If I enter an asterisk to introduce wildcards as part of a field I get a runtime error 3075 and syntax error (missing operator) in query expression '[SONo] = 18658*'. - Note that 18658.01 is a valid entry and the record is returned when the full value is entered.

    Phil

    Saturday, November 14, 2015 10:36 PM
  • To use a wildcard, you must format your compare value as string and you have to use the Like comparison operator.

    Example:

    WHERE [SONo] Like "18658*"


    • Edited by RunningManHD Saturday, November 14, 2015 11:05 PM
    Saturday, November 14, 2015 11:04 PM
  • Actually, a wildcard would be needed before the drawing number too IF this continues to be stored as a hyperlink.  In my opinion, however, it seems like a lot of extra work to edit all the hyperlinks to add the path. 

    Are all the drawings for a project in the same folder?

    If so and the field is stored as short text, it could be underlined and blue so it looks like a hyperlink ... then code can open the file(s)

    A Hyperlink is a special form of short text that will act as a link to open a file when you click its value. 

    A hyperlink has 4 parts, separated by octothorpes (#)

    1. text to display.
    2. actual address of the file.
    3. reference a named section within the specified file
    4. screen tip


    Warm Regards, Crystal http://www.AccessMVP.com/strive4peace * (: have an awesome day :)

    Saturday, November 14, 2015 11:33 PM
  • As RunningManHD and I have both pointed out, if you are going to use pattern matching then you must use the LIKE operator.  The code for your search expression would then be:

        whereclause = whereclause & "[Drawing] LIKE '" & txtDwg & "'"

    I agree with Crystal that a short text column is a simpler solution.  The little demo to which I referred you illustrates how a text value can be presented to the user as a hyperlink, though in my case formatting the control as a hyperlink is used rather than underlining.  The former also changes the mouse pointer to a hand.

    Ken Sheridan, Stafford, England

    Sunday, November 15, 2015 12:41 AM
  • thanks, Ken ... and btw, you forgot wildcards ...

    whereclause = whereclause & "[Drawing] LIKE '*" & txtDwg & "*'"

    without wildcards, Like is the same as = only slower ...

    anyway, hyperlinks cannot do wildcards, they must be to a specific file.  Code, of course, can do anything ;) ~ almost


    Warm Regards, Crystal http://www.AccessMVP.com/strive4peace * (: have an awesome day :)

    Sunday, November 15, 2015 12:48 AM
  • Many thanks to all.

    I will test your various recommendations during the coming week.

    Phil

    Sunday, November 15, 2015 10:30 AM
  • 1.  and btw, you forgot wildcards ...

    2.  anyway, hyperlinks cannot do wildcards
    1.  Actually, I didn't.  The OP is entering the asterisk into the txtDwg control, so there is no need to concatenate it into the string expression.

    2.  Exactly; which is why, in my first reply, I advised him not to use a column of hyperlink data type.  A hyperlink value is a construct, whereas a text value is what it says on the tin, text.  As well as giving rise to the sort of problems the OP is experiencing, my biggest reservation about hyperlink columns is their lack of portability.  Portability of data used to figure strongly when I was first learning about relational databases, but one rarely hears it mentioned these days.  For those of us working a scientific environment it is important.


    Ken Sheridan, Stafford, England

    • Marked as answer by TheHC Tuesday, November 17, 2015 12:24 PM
    Sunday, November 15, 2015 10:31 AM
  • please accept my apology, Ken

    However, since the field is a hyperlink, it DOES need a wildcard in the beginning too, because of the way that hyperlinks are stored

    Phil, another consideration is that Short Text is limited to 255 characters.  For a hyperlink, this means that the paths cannot be very long as the file specification cannot use all the characters because it is just one of 4 parts of a hyperlink.

    Ken and I both agree that you should strongly consider storing as Short Text, not Hyperlink.  We can help you with SQL to convert what you already have to Short Text as well as give you a way to automatically update the file specification with Path once we learn more about where and how your drawings are stored.  Please let us know if you want to try that route.  The users will see no difference -- and you will have a much easier time too, and retain use of wildcards ... so win-win for everyone :)


    Warm Regards, Crystal http://www.AccessMVP.com/strive4peace * (: have an awesome day :)

    Sunday, November 15, 2015 6:58 PM
  • Thanks to all for suggestions.

    Ken - I have used your BrowseDemo file to store the filepaths as text only.  Unfortunately, I am not bright enough to follow all the module code, but it works so I will include it on trust.

    Also, as you mention, I just needed to use Like instead of = to allow the * wildcard in fields on my query forms.  Very basic, but I create an Access database on average once every 5 years!

    Thanks again

    Phil

    Tuesday, November 17, 2015 12:24 PM