Extracting and Combining data from different Fields

[COiSman]

In Field1 I have a Web Browser Control link:


http://maps.google.co.za/maps?q=-25.396769, 27.111767&num=1&vpsrc=0&t=k&z=15&output=embed

In Field2 I have a GPS co-ordinate:
-24.396769, 26.111767

In Field3 I want to combine the link in Field1 and the data in Field2 replacing co-ordinates in BOLD.

In other words the data in Field3 should look like this:
http://maps.google.co.za/maps?q=-24.396769, 26.111767&num=1&vpsrc=0&t=k&z=15&output=embed

[hertfordkc]

Use the function INSTR to determine the location of the first '=' and the first '&'.
Use LEFT and RIGHT to get the beginning and end of the strings that you want to keep.
Then concatenate them with the normal string operator '&'.

I would use VBA but you probably can do it with the expression builder.

[COiSman]

Thanks for the reply, but would not know where to start?

The expression builder I do not have any experience on except what it is and what it is for...

Can you give me an example or direct me in more detail?

[COiSman]

I have attached a file with the Fields 1 to 3.

[hertfordkc]

database. However, here is the SQL for a query. Note that I called my table TY and the field which you want F4.

SELECT TY.Field1, TY.Field2, Left([Field1],InStr(1,[Field1],"=")) & [Field2] & Right([Field1],Len([Field1])-InStr(1,[Field1],"&")+1) AS F4
FROM TY;

You might be able to paste this into a control source if you need it on a form:
Left([Field1],InStr(1,[Field1],"=")) & [Field2] & Right([Field1],Len([Field1])-InStr(1,[Field1],"&")+1)

[COiSman]

Thanks man...

How will I use this in a Table in Field3?

Left([Field1],InStr(1,[Field1],"=")) & [Field2] & Right([Field1],Len([Field1])-InStr(1,[Field1],"&")+1)

[hertfordkc]

consider. Do you need to preserve Field1 in its original state? If you can create Field3 at
any time by using the SQL query, do you really need to save it as Field3?
Forget about the suggestion for a control source.
Edit the SQL which I posted and replace TY with your tablename. Replace F4 with 'Field3'.

Open query design in SQL mode and paste the edited SQL in as the query.
Run the query on your table and be sure you are getting the desired result in the query.
That may also give you the result in the table. (I haven't done this for a while.) If not, you may convert the select query into an update query. I believe your query design tools will help you do this with minimum fuss.

Back up your table before you start this. Now.

[COiSman]

I appreciate your assistance... The method worked perfectly when I created a query as explained by you.

Maybe I should explain to you what I am trying to accomplish and you might have a better way of doing this.

- I want to have a link to a map on googlemaps in Field1 in Table1.
- On a Form I would have a text box that will enable you to enter in new GPS co-ordinates which will then be linked to Field2 in Table1.
- I then want Field3 to do the above calculated action (you gave me) to use the newly entered co-ordinates, replacing the co-ordinates in the link in Field1.
- I then want to add the new link to the control source of a button. Once you press the button, a map will open with location.

Do you think this method is easy enough or do you think there is a better way of doing this?

Your help is much appreciated!!

[hertfordkc]

Whatever works. Computer time is less expensive than man houjrs.
Please mark thread as solved if you are satisfied.