Concatenating text and data 1

[sareteam]

I need a make table query to combine text with data.
Here is my query:

="

http://www.site.com/HomeImages/"

& [$LN] & ".jpg"

When I run the query all I get is the number from $LN.

What did I do wrong?

 

[LonnieJohnson]

Try the Cstr function...

Cstr("

http://www.site.com/HomeImages/")

& [$LN] & Cstr(".jpg&quot

ljprodev@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

 

[sareteam]

Lonnie,

Thanks for replying so quickly! Unfortunately that didn't work either. What really make me made is that I had this working 6 months ago project was cancelled and I DIDN'T SAVE IT! Arrrrrgh

 

[tlbroadbent]

Remove the semi-colon ( in your code. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[sareteam]

I removed the semi-colon when Lonie sent it.

(I couldn't find a reference for Cstr in Access help? SQL?)

Running Access 2000 with current updates ect.

I need to parse out a portion of data to another table that will be put out to a frontpage web to be further searched. I want to concatenate the data in $LN and make a pic ref in HTML. The pictures are already saved to web. As I've said I had this working once before and code at start is really close. Just hoping someone's light bulb will go on and figure out what I'm missing. Thanks.

 

[BoxHead]

Sareteam,

This is strange, indeed.

I built a table (tblConcatenate) and gave it four fields: SiteID, URL, Date, & Suffix. I put in your data and opened a query.

The query had the first 4 columns the same as the table. In column five, I typed,

String: [URL] & [Date] & [Suffix]

When I looked at the table view of the query, the string was almost correct except that the hyperlink had pound signs (#) on either end of it.

In the sixth column, I removed the # sign;

Parsed: Mid([URL],2,(Len([URL])-2)) & [Date] & [Suffix]

and that worked fine.

I'm using Access 97 and I know that 2000 is even more "Internet-friendly". I wonder if new underlying methods of handling hyperlinks or the hyperlink formatting itself is getting in your way. I don't have a clue where the pound signs were coming from.

Good luck!


John

 

[tlbroadbent]

CStr is a VBA function. You need to open the "Visual Basic Language Reference" to find info about this function. You can get to that book oif you open a VBA module and open then open Help.

You say that you have a make table query. However, you've never posted a make table query. What you've posted looks like a control source for a textbox. The following works in the control source of a text box in Access 2000.

="

http://www.site.com/HomeImages/"

& [FN] & ".jpg"

Here is a make table query that creates the hyperlinks in a table.

SELECT "

http://www.site.com/HomeImages/"

& [FN] & ".jpg"
AS LinkName INTO HyperLinkTable
FROM MyTable;

Now the question is, what are you really trying to do? Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[sareteam]

I'm in Access. I have a table called residential with 20k records. I have a make table query called active. I would like to pull all the records marked "ACT" with some additional fields (bed, bath, style, ect...) but I would like to add my own text (see prev post)to existing data.

No matter what I do all I get is data from $LN to appear in active table.

Contents of make table:

Field: $LN
Table: residential
Sort:
Show: (checked box)
Criteria:
or: ="

http://www.site.com/HomeImages/"

& "$LN" & ".jpg"

Even when I remove & $LN & All I get in active is data from &LN

Like I've posted, I had this working before...without getting fancy with sql or vbs (out of my knowledge base!)

I do appreciate the help! Thanks for previous postings Lonny, John and Terry!

(This is really what I'm trying to do!)

 

[tlbroadbent]

Placing the character string in the criteria of the query will not cause it to be inserted in a table. The portion of the query you posted should look like the following if you want to insert the hyperlink in the Active table.

Field: "

http://www.site.com/HomeImages/"

& [$LN] & ".jpg"
Table:
Sort:
Show: (checked box)
Criteria:

If you can't get this to work, it would be useful to those who are trying to help if you posted the entire SQL statement. If you open the query in design mode and click on SQL view, you can see the entire SQL statement for the query. If you copy that statement to the clipboard and paste it in a message here, someone should be able to help resolve any problems. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[sareteam]

BINGO! Thanks Terry!!!! I knew it would be something simple!!!

 

[BoxHead]

Terry,

Any ideas on why # signs are added to the string when it's concatenated using the field name as a reference?

John

 

[sareteam]

Terry,

Two last questions;

To get this to work as I need the text I concatenate makes my html ref. To get it to output quotes I tried this:

Field: Expr1: &quot;<img src=&quot; & [strQuote] & &quot;

http://www.site.com/Images/&quot;

& [$LN] & &quot;.jpg&quot; & [strQuote]

I'm prompted to &quot;Enter Prameter Value&quot;, I enter &quot; and it works. (Anything between [] would have generated the same prompt correct?)

Question #1, Is there another way to get the &quot; to appear? (I tried &quot;&quot;&quot
Question #2, After I run the make table query I then have to identify that column as &quot;contains HTML&quot; in FrontPage...Is there a way to make this identification here or is that a question for the FrontPage forum?

 

[tlbroadbent]

I find that this works and add the quotes needed.

Expr1: &quot;<img src=&quot; & &quot;&quot;&quot;

http://www.site.com/Images/&quot;

& [AnswerID] & &quot;.jpg&quot;&quot;&quot;

John,

I don't know why Access added the # unless having a date column caused it. If you post your query, maybe I'll be able to determine what happened. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[sareteam]

Worked again! Thanks again Terry. I tried it a couple of different ways...not that one ...go figure! Got to have everything in the right place!