using HYPERLINK column in query

[dharkangel]

Hi,

I have a linked table that contains a 'Hyperlink' column. Unfortunately for me, I cannot change the format of this column to another and I have to use it as my unique identifier. So far I cannot figure out how to compare it properly to a text value since as a hyperlink, it is not.

For example, with my query here:

+++++++++++++++++++++++++++++++++
SELECT [PDP Deliverable]
FROM test_sp WHERE [PDP Deliverable] = 'test8.doc';
+++++++++++++++++++++++++++++++++

I don't get a match, even though when I open my database and view this column, I have a value that is 'test8.doc' but it is a hyperlink. Can I use SQL somehow to capture this needed value in the hyperlink column?

 

[PHV]

Have a look at the HyperlinkPart function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dharkangel]

Yes, I know about the hyperlinkpart function, so I guess I have to do this in VB. I was hoping to keep it all in SQL but its probably not possible.

 

[PHV]

Have you even TRIED to use this function in SQL ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dharkangel]

Yes, I did, I tried this:

MySQL3 = "UPDATE test_sp SET test_sp.[Status] = 'Draft' WHERE HyperlinkPart(test_sp.[PDP deliverable], acDisplayedValue) = '" & fileName & "'"

and this:

MySQL3 = "UPDATE test_sp SET test_sp.[Status] = 'Draft' WHERE HyperlinkPart(test_sp.[PDP deliverable], ""acDisplayedValue"") = '" & fileName & "'"

The first one asked me to give a value for acDisplayedValue and the second option says there is a data type mismatch. I have used the HyperlinkPart function ok when i used it to the right of the = sign in a where statement but not like this. MS Access documentation also says it is a VB function so a follow-up question is what is the syntax to use it as I need to?

 

[dharkangel]

I figured it out, I cannot use the constants in this method, so this worked:

MySQL3 = "UPDATE test_sp SET test_sp.[Status] = 'Draft' WHERE HyperlinkPart(test_sp.[PDP deliverable], 0) = '" & fileName & "'"

Thanks again PHV.