update hyperlink through query

[Gary Walter]

sorry..

another wild guess:

UPDATE Bildoriginal
SET Bildfil =
Replace([Bildfil], "K:" & Chr(47), Chr(92) & Chr(92) & "Avantgrp" & Chr(92)
& "gemensam2" & Chr(92),1,-1,1);

also...
BildFil is for sure the name of the field?

No, that generated even more errors (unknown syntax). In my help the
expression looks like:

Replace(expression, find, replace[, start[, count[, compare]]])

/Maria


Gary Walter skrev:

it looks like you have extra ")" at end?

else,
double-check your Help for Replace function

is there a chance that in your language version
it needs ";" instead of "," to separate parameters?

UPDATE Bildoriginal
SET Bildfil = Replace([Bildfil]; 'K:\'; '\\Avantgrp\gemensam2\';1;-1;1);

Like this:
UPDATE Bildoriginal
SET Bildfil = Replace(Bildfil, 'K:\', '\\Avantgrp\gemensam2\',1,-1,1));
I didn't know it had to be in SQL.

Although, I added it and still get the same error message. :-(

/Maria



x-rays skrev:

I see, is there any chance that the end of statement mark ";" is not
at
the end?

Maria wrote:
Hello again x-rays,
I tried it with double quotes first but someone told me single
quotes
were more common in SQL. That's why I tried changing it. However
both
ways, unfotunately, gave the same error message.

/Maria

PS. No worries... My name's common all over the world.

x-rays skrev:

Hello Maria,

I don't see how is that possible to fail either, eclose strings
with
double quotes instead of single and tell me if works this way.

PS: oh ok, sorry for asking.


Maria wrote:
Hello x-ray,

Thankyou so much for the help. I thought your solution looked
absolutely perfect however I got an error message saying
somehing
like
"the expression contains a function with the wrong number of
arguments." I tried to look inte the help to understand this but
I
couldn't see anything wrong.

Here's my new line of code:

UPDATE Bildoriginal
SET Bildfil = Replace(Bildfil, 'K:\', '\\Avantgrp\gemensam2\')

/Maria

Ps. No I'm not greek. I'm Swedish....


x-rays wrote:
Hello Maria,

I think you need something like that:

Lets say that the value of your column (in that case Field1)
of a
row
is "D:\Avant\blabla"

UPDATE YourTable
SET Field1 = Replace(Field1, "D:\", "C:\")

Now your value is C:\Avant\blabla

Is this works for you?

PS: are you greek?

Maria wrote:
Hello again,

I think I might be on may way to the answer but I would
still
need some
help:

I realize that my query has to look something like this

UPDATE MyTable
SET Bildoriginal = (Bildoriginal - "K:\Avant Structure\Avant
Intellectual Capital AIC\Produkt\Symboler\Bildoriginal\)" &
("#\\Avantgrp\gemensam2\Avant Structure\Avant Intellectual
Capital
AIC\Produkt\Symboler\Bildoriginal\" & Bildoriginal)

although not quite like this at all.

/Maria


Maria wrote:
Hi,
I have created a table in a database that contains
hyperlinks
pointing
to various documents, all in the same folder but for some
reason the
hyperlinks wouldn't work correctly unless I put the direct
address in
the hyperlink.

Now the location of the document folder has changed and I
need to
update the hyperlink addresses but I can't quite figure
out
how to do
that. I'm quite new to this... The filed shows the
fliename.
I need to
keep the filename and both in the visible field and at the
end of the
adress but I ned to change the part of the address that
points into the
folder. It's a few hundred files so it's not appealing
having
to do
this manualy and I'm sure it could be done through a
query.

I would be very thankful for all directions.

/Maria

 

[Gary Walter]

all should be Chr(92) i.e., back slash

don't know why I put Chr(47) in there..

sorry..

another wild guess:

UPDATE Bildoriginal
SET Bildfil =
Replace([Bildfil], "K:" & Chr(47), Chr(92) & Chr(92) & "Avantgrp" &
Chr(92) & "gemensam2" & Chr(92),1,-1,1);

also...
BildFil is for sure the name of the field?

No, that generated even more errors (unknown syntax). In my help the
expression looks like:

Replace(expression, find, replace[, start[, count[, compare]]])

/Maria


Gary Walter skrev:

it looks like you have extra ")" at end?

else,
double-check your Help for Replace function

is there a chance that in your language version
it needs ";" instead of "," to separate parameters?

UPDATE Bildoriginal
SET Bildfil = Replace([Bildfil]; 'K:\'; '\\Avantgrp\gemensam2\';1;-1;1);

:

Like this:
UPDATE Bildoriginal
SET Bildfil = Replace(Bildfil, 'K:\',
'\\Avantgrp\gemensam2\',1,-1,1));
I didn't know it had to be in SQL.

Although, I added it and still get the same error message. :-(

/Maria



x-rays skrev:

I see, is there any chance that the end of statement mark ";" is not
at
the end?

Maria wrote:
Hello again x-rays,
I tried it with double quotes first but someone told me single
quotes
were more common in SQL. That's why I tried changing it. However
both
ways, unfotunately, gave the same error message.

/Maria

PS. No worries... My name's common all over the world.

x-rays skrev:

Hello Maria,

I don't see how is that possible to fail either, eclose strings
with
double quotes instead of single and tell me if works this way.

PS: oh ok, sorry for asking.


Maria wrote:
Hello x-ray,

Thankyou so much for the help. I thought your solution looked
absolutely perfect however I got an error message saying
somehing
like
"the expression contains a function with the wrong number of
arguments." I tried to look inte the help to understand this
but I
couldn't see anything wrong.

Here's my new line of code:

UPDATE Bildoriginal
SET Bildfil = Replace(Bildfil, 'K:\', '\\Avantgrp\gemensam2\')

/Maria

Ps. No I'm not greek. I'm Swedish....


x-rays wrote:
Hello Maria,

I think you need something like that:

Lets say that the value of your column (in that case Field1)
of a
row
is "D:\Avant\blabla"

UPDATE YourTable
SET Field1 = Replace(Field1, "D:\", "C:\")

Now your value is C:\Avant\blabla

Is this works for you?

PS: are you greek?

Maria wrote:
Hello again,

I think I might be on may way to the answer but I would
still
need some
help:

I realize that my query has to look something like this

UPDATE MyTable
SET Bildoriginal = (Bildoriginal - "K:\Avant
Structure\Avant
Intellectual Capital AIC\Produkt\Symboler\Bildoriginal\)"
&
("#\\Avantgrp\gemensam2\Avant Structure\Avant Intellectual
Capital
AIC\Produkt\Symboler\Bildoriginal\" & Bildoriginal)

although not quite like this at all.

/Maria


Maria wrote:
Hi,
I have created a table in a database that contains
hyperlinks
pointing
to various documents, all in the same folder but for some
reason the
hyperlinks wouldn't work correctly unless I put the
direct
address in
the hyperlink.

Now the location of the document folder has changed and I
need to
update the hyperlink addresses but I can't quite figure
out
how to do
that. I'm quite new to this... The filed shows the
fliename.
I need to
keep the filename and both in the visible field and at
the
end of the
adress but I ned to change the part of the address that
points into the
folder. It's a few hundred files so it's not appealing
having
to do
this manualy and I'm sure it could be done through a
query.

I would be very thankful for all directions.

/Maria

 

[Maria]

Thanks Gary,
but that line of code generates the error message "invalid syntax".

Yes, The table is Bildoriginal and it contains two fields "ID" and
"Bildfil"

/Maria



Gary Walter skrev:

all should be Chr(92) i.e., back slash

don't know why I put Chr(47) in there..

sorry..

another wild guess:

UPDATE Bildoriginal
SET Bildfil =
Replace([Bildfil], "K:" & Chr(47), Chr(92) & Chr(92) & "Avantgrp" &
Chr(92) & "gemensam2" & Chr(92),1,-1,1);

also...
BildFil is for sure the name of the field?

No, that generated even more errors (unknown syntax). In my help the
expression looks like:

Replace(expression, find, replace[, start[, count[, compare]]])

/Maria


Gary Walter skrev:

it looks like you have extra ")" at end?

else,
double-check your Help for Replace function

is there a chance that in your language version
it needs ";" instead of "," to separate parameters?

UPDATE Bildoriginal
SET Bildfil = Replace([Bildfil]; 'K:\'; '\\Avantgrp\gemensam2\';1;-1;1);

:

Like this:
UPDATE Bildoriginal
SET Bildfil = Replace(Bildfil, 'K:\',
'\\Avantgrp\gemensam2\',1,-1,1));
I didn't know it had to be in SQL.

Although, I added it and still get the same error message. :-(

/Maria



x-rays skrev:

I see, is there any chance that the end of statement mark ";" is not
at
the end?

Maria wrote:
Hello again x-rays,
I tried it with double quotes first but someone told me single
quotes
were more common in SQL. That's why I tried changing it. However
both
ways, unfotunately, gave the same error message.

/Maria

PS. No worries... My name's common all over the world.

x-rays skrev:

Hello Maria,

I don't see how is that possible to fail either, eclose strings
with
double quotes instead of single and tell me if works this way.

PS: oh ok, sorry for asking.


Maria wrote:
Hello x-ray,

Thankyou so much for the help. I thought your solution looked
absolutely perfect however I got an error message saying
somehing
like
"the expression contains a function with the wrong number of
arguments." I tried to look inte the help to understand this
but I
couldn't see anything wrong.

Here's my new line of code:

UPDATE Bildoriginal
SET Bildfil = Replace(Bildfil, 'K:\', '\\Avantgrp\gemensam2\')

/Maria

Ps. No I'm not greek. I'm Swedish....


x-rays wrote:
Hello Maria,

I think you need something like that:

Lets say that the value of your column (in that case Field1)
of a
row
is "D:\Avant\blabla"

UPDATE YourTable
SET Field1 = Replace(Field1, "D:\", "C:\")

Now your value is C:\Avant\blabla

Is this works for you?

PS: are you greek?

Maria wrote:
Hello again,

I think I might be on may way to the answer but I would
still
need some
help:

I realize that my query has to look something like this

UPDATE MyTable
SET Bildoriginal = (Bildoriginal - "K:\Avant
Structure\Avant
Intellectual Capital AIC\Produkt\Symboler\Bildoriginal\)"
&
("#\\Avantgrp\gemensam2\Avant Structure\Avant Intellectual
Capital
AIC\Produkt\Symboler\Bildoriginal\" & Bildoriginal)

although not quite like this at all.

/Maria


Maria wrote:
Hi,
I have created a table in a database that contains
hyperlinks
pointing
to various documents, all in the same folder but for some
reason the
hyperlinks wouldn't work correctly unless I put the
direct
address in
the hyperlink.

Now the location of the document folder has changed and I
need to
update the hyperlink addresses but I can't quite figure
out
how to do
that. I'm quite new to this... The filed shows the
fliename.
I need to
keep the filename and both in the visible field and at
the
end of the
adress but I ned to change the part of the address that
points into the
folder. It's a few hundred files so it's not appealing
having
to do
this manualy and I'm sure it could be done through a
query.

I would be very thankful for all directions.

/Maria

 

[Gary Walter]

I'm sorry..I really do have to go to work...

go to your Debug window by typing
{ALT}F11 keys

at bottom of window (in Immediate window)
type

Bildfil="a"

then hit {ENTER}

on next line, type

?Replace(Bildfil,"a","B",1,-1,1)

then hit {ENTER} key

does that work?

if so, try

Bildfil=":"
?Replace(BildFil,":","\",1,-1,1)

Thanks Gary,
but that line of code generates the error message "invalid syntax".

Yes, The table is Bildoriginal and it contains two fields "ID" and
"Bildfil"

/Maria



Gary Walter skrev:

all should be Chr(92) i.e., back slash

don't know why I put Chr(47) in there..

sorry..

another wild guess:

UPDATE Bildoriginal
SET Bildfil =
Replace([Bildfil], "K:" & Chr(47), Chr(92) & Chr(92) & "Avantgrp" &
Chr(92) & "gemensam2" & Chr(92),1,-1,1);

also...
BildFil is for sure the name of the field?

 

[Maria]

Thanks again for trying to help,
I understand. Have fun at work.

I can't save my query because of the syntax error so therefore it
seemed like I couldn't run the debug tool on it either.

/Maria




Gary Walter skrev:

I'm sorry..I really do have to go to work...

go to your Debug window by typing
{ALT}F11 keys

at bottom of window (in Immediate window)
type

Bildfil="a"

then hit {ENTER}

on next line, type

?Replace(Bildfil,"a","B",1,-1,1)

then hit {ENTER} key

does that work?

if so, try

Bildfil=":"
?Replace(BildFil,":","\",1,-1,1)

Thanks Gary,
but that line of code generates the error message "invalid syntax".

Yes, The table is Bildoriginal and it contains two fields "ID" and
"Bildfil"

/Maria



Gary Walter skrev:

all should be Chr(92) i.e., back slash

don't know why I put Chr(47) in there..

sorry..

another wild guess:

UPDATE Bildoriginal
SET Bildfil =
Replace([Bildfil], "K:" & Chr(47), Chr(92) & Chr(92) & "Avantgrp" &
Chr(92) & "gemensam2" & Chr(92),1,-1,1);

also...
BildFil is for sure the name of the field?

 

[John Spencer]

Try surrounding the source field with [ ] to make sure Access understands
what you are trying to do.

UPDATE Bildoriginal
SET Bildfil = Replace([Bildfil], "K:\", "\\Avantgrp\gemensam2\",1,-1,1);

I

Thanks again for trying to help,
I understand. Have fun at work.

I can't save my query because of the syntax error so therefore it
seemed like I couldn't run the debug tool on it either.

/Maria




Gary Walter skrev:

I'm sorry..I really do have to go to work...

go to your Debug window by typing
{ALT}F11 keys

at bottom of window (in Immediate window)
type

Bildfil="a"

then hit {ENTER}

on next line, type

?Replace(Bildfil,"a","B",1,-1,1)

then hit {ENTER} key

does that work?

if so, try

Bildfil=":"
?Replace(BildFil,":","\",1,-1,1)

Thanks Gary,
but that line of code generates the error message "invalid syntax".

Yes, The table is Bildoriginal and it contains two fields "ID" and
"Bildfil"

/Maria



Gary Walter skrev:

all should be Chr(92) i.e., back slash

don't know why I put Chr(47) in there..

sorry..

another wild guess:

UPDATE Bildoriginal
SET Bildfil =
Replace([Bildfil], "K:" & Chr(47), Chr(92) & Chr(92) & "Avantgrp" &
Chr(92) & "gemensam2" & Chr(92),1,-1,1);

also...
BildFil is for sure the name of the field?

 

[Maria]

Thank you but that gives me invalid syntax.

/Maria


John Spencer skrev:

Try surrounding the source field with [ ] to make sure Access understands
what you are trying to do.

UPDATE Bildoriginal
SET Bildfil = Replace([Bildfil], "K:\", "\\Avantgrp\gemensam2\",1,-1,1);

I

Thanks again for trying to help,
I understand. Have fun at work.

I can't save my query because of the syntax error so therefore it
seemed like I couldn't run the debug tool on it either.

/Maria




Gary Walter skrev:

I'm sorry..I really do have to go to work...

go to your Debug window by typing
{ALT}F11 keys

at bottom of window (in Immediate window)
type

Bildfil="a"

then hit {ENTER}

on next line, type

?Replace(Bildfil,"a","B",1,-1,1)

then hit {ENTER} key

does that work?

if so, try

Bildfil=":"
?Replace(BildFil,":","\",1,-1,1)



:
Thanks Gary,
but that line of code generates the error message "invalid syntax".

Yes, The table is Bildoriginal and it contains two fields "ID" and
"Bildfil"

/Maria



Gary Walter skrev:

all should be Chr(92) i.e., back slash

don't know why I put Chr(47) in there..

sorry..

another wild guess:

UPDATE Bildoriginal
SET Bildfil =
Replace([Bildfil], "K:" & Chr(47), Chr(92) & Chr(92) & "Avantgrp" &
Chr(92) & "gemensam2" & Chr(92),1,-1,1);

also...
BildFil is for sure the name of the field?

 

[Maria]

OK,
I'm starting to feel like an idiot. I opened my query in SQL-mode and
entered the code and this time compilation error. My version of Access
is in Swedish so it translated my SQL to Swedish. That's probably why
it wasn't ok to use English in query mode.

However I don't understand the compilation error. Is it at all possible
to use replace on hyperlinks?!


/Maria


Maria skrev:

Thank you but that gives me invalid syntax.

/Maria


John Spencer skrev:

Try surrounding the source field with [ ] to make sure Access understands
what you are trying to do.

UPDATE Bildoriginal
SET Bildfil = Replace([Bildfil], "K:\", "\\Avantgrp\gemensam2\",1,-1,1);

I

Thanks again for trying to help,
I understand. Have fun at work.

I can't save my query because of the syntax error so therefore it
seemed like I couldn't run the debug tool on it either.

/Maria




Gary Walter skrev:

I'm sorry..I really do have to go to work...

go to your Debug window by typing
{ALT}F11 keys

at bottom of window (in Immediate window)
type

Bildfil="a"

then hit {ENTER}

on next line, type

?Replace(Bildfil,"a","B",1,-1,1)

then hit {ENTER} key

does that work?

if so, try

Bildfil=":"
?Replace(BildFil,":","\",1,-1,1)



:
Thanks Gary,
but that line of code generates the error message "invalid syntax".

Yes, The table is Bildoriginal and it contains two fields "ID" and
"Bildfil"

/Maria



Gary Walter skrev:

all should be Chr(92) i.e., back slash

don't know why I put Chr(47) in there..

sorry..

another wild guess:

UPDATE Bildoriginal
SET Bildfil =
Replace([Bildfil], "K:" & Chr(47), Chr(92) & Chr(92) & "Avantgrp" &
Chr(92) & "gemensam2" & Chr(92),1,-1,1);

also...
BildFil is for sure the name of the field?

 

[x-rays]

Hello Maria, how are you?

I saw your statement on the screen you post me but I can't see any
trouble.

Is there a any chance you have 2 or more different versions of Access
installed in your PC? Just trying to figure out where is the problem.

An alternative solution to replace your values manually on the table
(maybe you know this but..):

1) Backup your data in another table or export it in another database
2) Open your table in Datasheet View (viewing your data)
3) Click on a record in column Bildfil
4) Type Ctrl + H and "Find & Replace" Window will appear
5) In 1st textbox type the text you want to find
6) In 2nd, type the text you want to replace the text typed in 1st
textbox
7) Choose Match Case "Any part of Field" or "Start of Field"
8) Hit Replace All
9) A message will appear if you're sure to do this because will be
unable to undo this. Choose yes and work is done.

Thanks again for trying to help,
I understand. Have fun at work.

I can't save my query because of the syntax error so therefore it
seemed like I couldn't run the debug tool on it either.

/Maria




Gary Walter skrev:

I'm sorry..I really do have to go to work...

go to your Debug window by typing
{ALT}F11 keys

at bottom of window (in Immediate window)
type

Bildfil="a"

then hit {ENTER}

on next line, type

?Replace(Bildfil,"a","B",1,-1,1)

then hit {ENTER} key

does that work?

if so, try

Bildfil=":"
?Replace(BildFil,":","\",1,-1,1)

Thanks Gary,
but that line of code generates the error message "invalid syntax".

Yes, The table is Bildoriginal and it contains two fields "ID" and
"Bildfil"

/Maria



Gary Walter skrev:

all should be Chr(92) i.e., back slash

don't know why I put Chr(47) in there..

sorry..

another wild guess:

UPDATE Bildoriginal
SET Bildfil =
Replace([Bildfil], "K:" & Chr(47), Chr(92) & Chr(92) & "Avantgrp" &
Chr(92) & "gemensam2" & Chr(92),1,-1,1);

also...
BildFil is for sure the name of the field?

 

[Maria]

Hello again,
I just realized it must be my database that has some fault in it. It
tried creating a new database with one tine table with a field
containing hyperlinks and I could update those hyperlinks without
problem. Now I'm just worried that I won't be able to save the table
data without having to redo the entire database.

/Maria

x-rays skrev:

Hello Maria, how are you?

I saw your statement on the screen you post me but I can't see any
trouble.

Is there a any chance you have 2 or more different versions of Access
installed in your PC? Just trying to figure out where is the problem.

An alternative solution to replace your values manually on the table
(maybe you know this but..):

1) Backup your data in another table or export it in another database
2) Open your table in Datasheet View (viewing your data)
3) Click on a record in column Bildfil
4) Type Ctrl + H and "Find & Replace" Window will appear
5) In 1st textbox type the text you want to find
6) In 2nd, type the text you want to replace the text typed in 1st
textbox
7) Choose Match Case "Any part of Field" or "Start of Field"
8) Hit Replace All
9) A message will appear if you're sure to do this because will be
unable to undo this. Choose yes and work is done.

Thanks again for trying to help,
I understand. Have fun at work.

I can't save my query because of the syntax error so therefore it
seemed like I couldn't run the debug tool on it either.

/Maria




Gary Walter skrev:

I'm sorry..I really do have to go to work...

go to your Debug window by typing
{ALT}F11 keys

at bottom of window (in Immediate window)
type

Bildfil="a"

then hit {ENTER}

on next line, type

?Replace(Bildfil,"a","B",1,-1,1)

then hit {ENTER} key

does that work?

if so, try

Bildfil=":"
?Replace(BildFil,":","\",1,-1,1)



:
Thanks Gary,
but that line of code generates the error message "invalid syntax".

Yes, The table is Bildoriginal and it contains two fields "ID" and
"Bildfil"

/Maria



Gary Walter skrev:

all should be Chr(92) i.e., back slash

don't know why I put Chr(47) in there..

sorry..

another wild guess:

UPDATE Bildoriginal
SET Bildfil =
Replace([Bildfil], "K:" & Chr(47), Chr(92) & Chr(92) & "Avantgrp" &
Chr(92) & "gemensam2" & Chr(92),1,-1,1);

also...
BildFil is for sure the name of the field?