ID variable in TEXT field

[samsoniikskv]

Hello. I need to get ID variable from main column to my other column where is URL address. Bellow you can see what I need. In first column is ID and I need to get it to Mainimageforsolution column as a variable. Column "Mainimageforsolution" is set now for text. How can I do that ? Thanks a lot guys

 

[theDBguy]

Hi. Welcome to AWF!

Did you try concatenating it?

 

[The_Doc_Man]

If this is always going to have the same exact format, you could make a two-part template in the program to generate the string using something like

WebURL = "https://website.com/images/" & cstr([IDfield] & "main.jpg"

If not, then there is also the chance if you have a unique value in your string, to use REPLACE

WebURL = Replace( URLString, "[ID]", cstr( [IDfield] ) )

Then in either case do an update of the record to insert the changed Web URL string.

 

[samsoniikskv]

What do you mean by concatenating ? I'm new in MS access.. I need create that only for XML export to my website. And my problem is only with that field. @The_Doc_Man in that column there always be same format. Only ID will change

 

[Cronk]

Concatenate means to join or unite. In this case, you seem to want to replace the literal value of {ID] with the numeric value in your key field.

If you want to update the field MainImageForSolution, then use an update query. If you do not want the field to be changed but only need the data for export, then use a select query.

Come back if you need more help.

 

[The_Doc_Man]

What do you mean by concatenating ?

In Access and in VBA, "concatenate" is an operation like addition or subtraction but it applies to strings, not numbers. It means "to bring together" or "to merge." In Latin (its origin language) it means to build a chain by joining the links. Latin: "con" meaning with, "catena" meaning chain.

In the strings I showed you, the ampersand (&) is the concatenation operator just like plus (+) is an addition operator. You take two strings (or in my example, three strings and two ampersands) to join them together to form one long string.

You said the format is constant. Therefore my first option would work to take two constant strings (enclosed in quotes) and make a string out of the ID field (CSTR = VBA function to create a string based on an input number). You "chain" together the two constant strings with that converted string and put that in a string variable that you can then load into that field in the table.

Since we can't currently see how that table is being built, it is hard for us to take it much further. However, it should be trivial to load up values for it.

 

[Isaac]

Piggybacking off previous posts, so if you are wanting to actually UPDATE the url column with the new information, you would create an UPDATE query, and drag the url field down to the designer grid, and in the "Update To" row you would type something like:

"https://website.com/images/" & cstr([ID]) & "main.jpg"

or whatever the actual values are.

 

[samsoniikskv]

Thank you guys it's working. I created an update query with @Isaac command "website . com/images/" & cstr([ID]) & "/main.jpg"
but I have another problem in my next column. There is a solution gallery column and there are 6 URL adresses of gallery images.
I need in this field URLs of images separated by this mark " | " so gallery field should look like this:

website . com/images/[ID]/1.jpg|website . com/images/[ID]/2.jpg|website . com/images/[ID]/3.jpg etc...

I tried to use this: "website . com/images/" & cstr([ID]) & "/1.jpg"|"website . com/images/" & cstr([ID]) & "/2.jpg" etc..
but I got an error message that I used an invalid symbol, so I tried to put separator to quoatation marks like this:
(I HAD TO DELETE HTTPS HERE ON FORUM, AND GIVE SOME SPACE TO URL BECAUSE OF ANTISPAM WON'T LET ME SEND A POST)
"website . com/images/" & cstr([ID]) & "/1.jpg""|""website . com/images/" & cstr([ID]) & "/2.jpg" now error dissapear but in output I have this:

 

[samsoniikskv]

update1:
I added a separator to quotation marks, but is showing it on frontend too

 

[Gasman]

Why don't you just replace ID with the value of ID when you need the populated string?
That would also fix your second problem in one foul swoop.

 

[samsoniikskv]

I fixed the problem by this, I'm not sure if it's good but it's working. I added & "|" & between URLs
"mywebsite. com/images/" & CStr([ID]) & "/1.jpg" & "|" & "mywebsite. com/images/" & CStr([ID]) & "/2.jpg"

 

[samsoniikskv]

Can I ask one more question ? I need to add one more column to table, but if I make an export I don't want to export this column in XML. It should be only information column inside table with product number. If it's possible to select in XML export which columns I want to export ? Thanks

update: I found a problem in my solution with separator & "|" & it's working good if I have only 4 images, if I add one more url 5.jpg then output is not finished... as you can see on image bellow there is only number 5 and missing .jpg and 6.jpg url

Can you help me ? Thanks a lot

 

[Isaac]

You create a Select query (which is the default when you go to Create > Query design). Double click on the table you want added to the query. Then double click only on the query fields you want to show on the query.

 

[samsoniikskv]

Thank you. I solved the problem, I created that by UPDATE QUERY and problem was that I set Gallery field as a short text field type so that's why is was not displaying correctly. Now it's working.

I want to ask,I added to table one more column (it's only for information),and I don't want to export it in XML file. Does Access have an option to choose which columns I want to export in XML ? Or I have to export all and then delete it manually in XML file ? THanks

 

[Gasman]

Create a query with that one column missing and export from that.?

 

[Isaac]

Thank you. I solved the problem, I created that by UPDATE QUERY and problem was that I set Gallery field as a short text field type so that's why is was not displaying correctly. Now it's working.

I want to ask,I added to table one more column (it's only for information),and I don't want to export it in XML file. Does Access have an option to choose which columns I want to export in XML ? Or I have to export all and then delete it manually in XML file ? THanks

Re(?)-read posts 13 and 15 ...

Not sure XML destination has anything to do with creating this query.