Query hyperlinks

[Jaye7]

I have a query which is a concatenated field of text and then a field from a table that has a hyperlink.

i.e 15mm predesigned mould Drw: [L:\10527-123-A.pdf#L:\10527-123-A.pdf#]

I want it to display in the query field as just the hyperlink

15mm predesigned mould Drw: [L:\10527-123-A.pdf]

 

[CJ_London]

this

[L:\10527-123-A.pdf#L:\10527-123-A.pdf#] and
[L:\10527-123-A.pdf]

is a field name (and full of illegal characters so I would be surprised if Access allowed you to create it anyway), not a field value. If it is a field name, then you cannot split it.

If your field is actually called say HLink then the code you would require is

15mm predesigned mould Drw: Left(HLink, instr(HLink,"#")-1)

 

[Jaye7]

Thanks CJ London, that script works well, the part that was getting the hyperlink was from a field.

 

[Jaye7]

Hi again,

I have tried to use the following and it works well if there is a value in the field, however not if it is empty

my field contains the following but I get an error if there is no value

Expr5: Left([PDF],InStr([PDF],"#")-1)

tried

Expr5: IIf(IsError(Left([PDF],InStr([PDF],"#")-1)),"",Left([PDF],InStr([PDF],"#")-1))

tried

Expr5: IIf(IsNull(Left([PDF],InStr([PDF],"#")-1)),"",Left([PDF],InStr([PDF],"#")-1))

tried

Expr5: IIf(Left([PDF],InStr([PDF],"#")-1) Is Null,"",Left([PDF],InStr([PDF],"#")-1))

tried

Expr5: IIf(Left([PDF],InStr([PDF],"#")-1)="","",Left([PDF],InStr([PDF],"#")-1))

tried

Expr5: IIf(Left([PDF],InStr([PDF],"#")-1)=0,"",Left([PDF],InStr([PDF],"#")-1))

 

[Jaye7]

I got around this by using the following script for the full hyperlink then I use another field to then do one thing for value 1 and another for value 0

Expr5: IIf(IsNull([Expr1]),0,1)

Thanks again

 

[CJ_London]

yup, that is pretty much what I would suggest what I would have suggested - the errors you need to protect against is if there is not a # in the string and the string is null

so I would suggest

expr5:iif(instr(nz([PDF],""),"#")<>0,left([PDF],instr([PDF],"#")-1),"")