Solved: Hyperlink combined query

[Adonaioc]

I have 2 tables

- one with part numbers and descriptions
- one with desriptions specs and hyperlinks to files

I have a make table query that combines the the 2 into one table that has
it all. I did this to save time on data entry, many part numbers have the same descriptions and specs minus a color variable that is not important in this application.

Instead of a part number field and a hyperlink field I want a field that is read as the part number but when clicked opens the hyperlink.

So part number "A1" has link "1" when it is clicked. Can this be done?

[CreganTur]

In the field where you store the hyperlink you can add multiple parts to make up the hyperlink. The 2 you will be interested in are the first two:
1) Text to Display
2) Address

you seperate them with a # sign. So if you enter data into the field like this:
[vba]1#http://www.google.com[/vba]
The field it will display '1', but clicking on it will take you to the address.

[Adonaioc]

Indeed, however In my final table I will have many part numbers with the same link. I want each part number to be displayed as itself.

part number 1 link a
part number 2 link b
part number 3 link a
part number 4 link a

so on an so forth. basicly I want the query to take the link and add the part number to it as display text

[CreganTur]

The only way I can think of to do this is to create a blank table that is already formatted to receive your data. Then do an Append query to load the data into your fields. For your hyperlink field you would do something like the following in the Field section of Query Design view:

[VBA]FieldName:[PartNumber] & "#" & [Hyperlink][/VBA]
Where FieldName is the name of the field you're loading this calculated field into.

This is untested theory... but hopefully it will point you in the right direction.

If this doesn't work then the only other way I know of to do this is to create your table and load the data in via an ADO or DAO connection.