Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL syntax (2000(SP3))

    I am working on an application for a gallery gift shop. The receipt printing function is designed to print, at the same time as the receipt, selected artist bios which are stored as Word documents in another folder. The path to the artist bios is stored as a hyperlink . In the onclick event of the Print button, I create a recordset and then loop through the detail records on the receipt, check whether a bio is requested and then whether one exists and if both are true to print using the Word object.

    All of this works fine, my only little snag right now is that if someone buys more than one piece by the same artist they get the bio more than once. When playing around with DISTINCTROW I get an error message about hyperlinks not being compatible with DISTINCTROW. Is there a way around this? Perhaps a second recordset that filters the first after the hyperlink has been converted to a string? The SQL goes something as follows (I'm writing it from memory so don't be too worried if I @#$!% up a comma or bracket <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    strSQL = "SELECT qryDetails.ReceiptNum, qryDetails.Biography, qryDetails.PrintBio FROM qryDetails " & _
    "WHERE qryDetails.ReceiptNum = " & Me.ReceiptNum

    Biography is the hyperlink and PrintBio is a Yes/No field. This SQL gives me every instance of a requested biography. I want it to only give me one instance of each biography.

    Hopefully this is sufficient info. Thanks as always.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL syntax (2000(SP3))

    Have you tried SELECT DISTINCT qryDetails.ReceiptNum, ... ? DISTINCTROW tries to select unique records, DISTINCT to select unique values.

  3. #3
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL syntax (2000(SP3))

    I will try that. I thought I had but I was also playing around with a saved query testing out various things this morning so who knows?

    In order to pull out the part of the hyperlink that I actually want, is it possible to use the HyperlinkPart function as part of a SQL string? It would save several Mid, Left and Len incarnations of that field to strip off the # characters which is what I was doing before I found HyperlinkPart.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL syntax (2000(SP3))

    Yep. You should be able to use

    "SELECT DISTINCT ReceiptNum, Hyperlinkpart(Biography,2) AS Hyp, ..."

    (You must use the constant value 2 here, not the symbolic constant acAddress)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •