Update File Location Where Images will be Stored from a Query

  • Thread starter Thread starter adrian007uk
  • Start date Start date
A

adrian007uk

I have been trying to concatinate two fields to hold the image path of my
image folder (e.g., C:\Vinyl Images) with my image names (e.g., Vinyl1.jpg)
so if the location changed it would just require one name. John W was
helping me with this but it does not seem to work.

Fredg suggested using an update query. He posted:

Update MyTable Set MyTable.[Path] = "D" & Mid([Path],2) Where
Left([Path],1) = "C"

The above will change "C\My Folder" to "D:\My Folder" for all records.

However, i can not get the query to run. It says i am mising some syntax!
The field name where the images are stored is 'Picture' in a table called
'Vinyl'.

Does anybody know what i am doing wrong?
 
It's as below:

SELECT Vinyl.[Picture]
Update MyTable Set MyTable.[Path] = "D" & Mid([Path],2) Where Left([Path],1)
= "G"
FROM Vinyl;

Thanks

Adrian

Klatuu said:
Post the SQL of the query so we can have a look, please.

--
Dave Hargis, Microsoft Access MVP


adrian007uk said:
I have been trying to concatinate two fields to hold the image path of my
image folder (e.g., C:\Vinyl Images) with my image names (e.g., Vinyl1.jpg)
so if the location changed it would just require one name. John W was
helping me with this but it does not seem to work.

Fredg suggested using an update query. He posted:

Update MyTable Set MyTable.[Path] = "D" & Mid([Path],2) Where
Left([Path],1) = "C"

The above will change "C\My Folder" to "D:\My Folder" for all records.

However, i can not get the query to run. It says i am mising some syntax!
The field name where the images are stored is 'Picture' in a table called
'Vinyl'.

Does anybody know what i am doing wrong?
Click to expand...
Click to expand...
 
The SELECT Vinyl.[Picture] part doesn't belong in an update query.
Also the FROM Vinly doesn't belong there.

Here is an example I put together:

UPDATE _tblClientSave SET [_tblClientSave].AddressL1 = "3" &
Mid([AddressL1],2)
WHERE Left([AddressL1],1)="1";


So your's should be:

Update MyTable Set MyTable.[Path] = "D" & Mid([Path],2) Where Left([Path],1)
= "G";


--
Dave Hargis, Microsoft Access MVP


adrian007uk said:
It's as below:

SELECT Vinyl.[Picture]
Update MyTable Set MyTable.[Path] = "D" & Mid([Path],2) Where Left([Path],1)
= "G"
FROM Vinyl;

Thanks

Adrian

Klatuu said:
Post the SQL of the query so we can have a look, please.

--
Dave Hargis, Microsoft Access MVP


adrian007uk said:
I have been trying to concatinate two fields to hold the image path of my
image folder (e.g., C:\Vinyl Images) with my image names (e.g., Vinyl1.jpg)
so if the location changed it would just require one name. John W was
helping me with this but it does not seem to work.

Fredg suggested using an update query. He posted:

Update MyTable Set MyTable.[Path] = "D" & Mid([Path],2) Where
Left([Path],1) = "C"

The above will change "C\My Folder" to "D:\My Folder" for all records.

However, i can not get the query to run. It says i am mising some syntax!
The field name where the images are stored is 'Picture' in a table called
'Vinyl'.

Does anybody know what i am doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
Thanks for replying.

There seems to be a problem with 'where' as it still keeps saying invalid
syntax (i have also tried it in upper case).

Am I supposed to use 'MyTable Set' or am i suppoed to use references to my
database (if that makes sense)?

Adrian

Klatuu said:
The SELECT Vinyl.[Picture] part doesn't belong in an update query.
Also the FROM Vinly doesn't belong there.

Here is an example I put together:

UPDATE _tblClientSave SET [_tblClientSave].AddressL1 = "3" &
Mid([AddressL1],2)
WHERE Left([AddressL1],1)="1";


So your's should be:

Update MyTable Set MyTable.[Path] = "D" & Mid([Path],2) Where Left([Path],1)
= "G";


--
Dave Hargis, Microsoft Access MVP


adrian007uk said:
It's as below:

SELECT Vinyl.[Picture]
Update MyTable Set MyTable.[Path] = "D" & Mid([Path],2) Where Left([Path],1)
= "G"
FROM Vinyl;

Thanks

Adrian

Klatuu said:
Post the SQL of the query so we can have a look, please.

--
Dave Hargis, Microsoft Access MVP


:

I have been trying to concatinate two fields to hold the image path of my
image folder (e.g., C:\Vinyl Images) with my image names (e.g., Vinyl1.jpg)
so if the location changed it would just require one name. John W was
helping me with this but it does not seem to work.

Fredg suggested using an update query. He posted:

Update MyTable Set MyTable.[Path] = "D" & Mid([Path],2) Where
Left([Path],1) = "C"

The above will change "C\My Folder" to "D:\My Folder" for all records.

However, i can not get the query to run. It says i am mising some syntax!
The field name where the images are stored is 'Picture' in a table called
'Vinyl'.

Does anybody know what i am doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
I don't see anything wrong with your syntax if you are using what I suggested.
--
Dave Hargis, Microsoft Access MVP


adrian007uk said:
Thanks for replying.

There seems to be a problem with 'where' as it still keeps saying invalid
syntax (i have also tried it in upper case).

Am I supposed to use 'MyTable Set' or am i suppoed to use references to my
database (if that makes sense)?

Adrian

Klatuu said:
The SELECT Vinyl.[Picture] part doesn't belong in an update query.
Also the FROM Vinly doesn't belong there.

Here is an example I put together:

UPDATE _tblClientSave SET [_tblClientSave].AddressL1 = "3" &
Mid([AddressL1],2)
WHERE Left([AddressL1],1)="1";


So your's should be:

Update MyTable Set MyTable.[Path] = "D" & Mid([Path],2) Where Left([Path],1)
= "G";


--
Dave Hargis, Microsoft Access MVP


adrian007uk said:
It's as below:

SELECT Vinyl.[Picture]
Update MyTable Set MyTable.[Path] = "D" & Mid([Path],2) Where Left([Path],1)
= "G"
FROM Vinyl;

Thanks

Adrian

:

Post the SQL of the query so we can have a look, please.

--
Dave Hargis, Microsoft Access MVP


:

I have been trying to concatinate two fields to hold the image path of my
image folder (e.g., C:\Vinyl Images) with my image names (e.g., Vinyl1.jpg)
so if the location changed it would just require one name. John W was
helping me with this but it does not seem to work.

Fredg suggested using an update query. He posted:

Update MyTable Set MyTable.[Path] = "D" & Mid([Path],2) Where
Left([Path],1) = "C"

The above will change "C\My Folder" to "D:\My Folder" for all records.

However, i can not get the query to run. It says i am mising some syntax!
The field name where the images are stored is 'Picture' in a table called
'Vinyl'.

Does anybody know what i am doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
Hi Dave

Here is the excat SQL code:

UPDATE Vinyl SET
UPDATE MyTable Set MyTable.[Path] = "D" & Mid([Path],2) Where Left([Path],1)
= "G";

Klatuu said:
I don't see anything wrong with your syntax if you are using what I suggested.
--
Dave Hargis, Microsoft Access MVP


adrian007uk said:
Thanks for replying.

There seems to be a problem with 'where' as it still keeps saying invalid
syntax (i have also tried it in upper case).

Am I supposed to use 'MyTable Set' or am i suppoed to use references to my
database (if that makes sense)?

Adrian

Klatuu said:
The SELECT Vinyl.[Picture] part doesn't belong in an update query.
Also the FROM Vinly doesn't belong there.

Here is an example I put together:

UPDATE _tblClientSave SET [_tblClientSave].AddressL1 = "3" &
Mid([AddressL1],2)
WHERE Left([AddressL1],1)="1";


So your's should be:

Update MyTable Set MyTable.[Path] = "D" & Mid([Path],2) Where Left([Path],1)
= "G";


--
Dave Hargis, Microsoft Access MVP


:

It's as below:

SELECT Vinyl.[Picture]
Update MyTable Set MyTable.[Path] = "D" & Mid([Path],2) Where Left([Path],1)
= "G"
FROM Vinyl;

Thanks

Adrian

:

Post the SQL of the query so we can have a look, please.

--
Dave Hargis, Microsoft Access MVP


:

I have been trying to concatinate two fields to hold the image path of my
image folder (e.g., C:\Vinyl Images) with my image names (e.g., Vinyl1.jpg)
so if the location changed it would just require one name. John W was
helping me with this but it does not seem to work.

Fredg suggested using an update query. He posted:

Update MyTable Set MyTable.[Path] = "D" & Mid([Path],2) Where
Left([Path],1) = "C"

The above will change "C\My Folder" to "D:\My Folder" for all records.

However, i can not get the query to run. It says i am mising some syntax!
The field name where the images are stored is 'Picture' in a table called
'Vinyl'.

Does anybody know what i am doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
The entire query should be more like

UPDATE [YourTable]
Set [YourTable].[Path] = "D" & Mid([Path],2)
Where Left([Path],1) = "G"

If your table name is Vinyl then replace YourTable with Vinyl. And replace
Path with the name of the field if it is named something else than Path.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hi Dave

Here is the excat SQL code:

UPDATE Vinyl SET
UPDATE MyTable Set MyTable.[Path] = "D" & Mid([Path],2) Where Left([Path],1)
= "G";

Klatuu said:
I don't see anything wrong with your syntax if you are using what I suggested.
--
Dave Hargis, Microsoft Access MVP


adrian007uk said:
Thanks for replying.

There seems to be a problem with 'where' as it still keeps saying invalid
syntax (i have also tried it in upper case).

Am I supposed to use 'MyTable Set' or am i suppoed to use references to my
database (if that makes sense)?

Adrian

:

The SELECT Vinyl.[Picture] part doesn't belong in an update query.
Also the FROM Vinly doesn't belong there.

Here is an example I put together:

UPDATE _tblClientSave SET [_tblClientSave].AddressL1 = "3" &
Mid([AddressL1],2)
WHERE Left([AddressL1],1)="1";


So your's should be:

Update MyTable Set MyTable.[Path] = "D" & Mid([Path],2) Where Left([Path],1)
= "G";


--
Dave Hargis, Microsoft Access MVP


:

It's as below:

SELECT Vinyl.[Picture]
Update MyTable Set MyTable.[Path] = "D" & Mid([Path],2) Where Left([Path],1)
= "G"
FROM Vinyl;

Thanks

Adrian

:

Post the SQL of the query so we can have a look, please.

--
Dave Hargis, Microsoft Access MVP


:

I have been trying to concatinate two fields to hold the image path of my
image folder (e.g., C:\Vinyl Images) with my image names (e.g., Vinyl1.jpg)
so if the location changed it would just require one name. John W was
helping me with this but it does not seem to work.

Fredg suggested using an update query. He posted:

Update MyTable Set MyTable.[Path] = "D" & Mid([Path],2) Where
Left([Path],1) = "C"

The above will change "C\My Folder" to "D:\My Folder" for all records.

However, i can not get the query to run. It says i am mising some syntax!
The field name where the images are stored is 'Picture' in a table called
'Vinyl'.

Does anybody know what i am doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
Thanks John,

That worked a treat.

The SQL code that got it to work was:

UPDATE Vinyl SET [Vinyl].[Picture] = "I" & Mid([Picture],2)
Where Left([Picture],1) = "D"

Adrian

John Spencer said:
The entire query should be more like

UPDATE [YourTable]
Set [YourTable].[Path] = "D" & Mid([Path],2)
Where Left([Path],1) = "G"

If your table name is Vinyl then replace YourTable with Vinyl. And replace
Path with the name of the field if it is named something else than Path.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hi Dave

Here is the excat SQL code:

UPDATE Vinyl SET
UPDATE MyTable Set MyTable.[Path] = "D" & Mid([Path],2) Where Left([Path],1)
= "G";

Klatuu said:
I don't see anything wrong with your syntax if you are using what I suggested.
--
Dave Hargis, Microsoft Access MVP


:

Thanks for replying.

There seems to be a problem with 'where' as it still keeps saying invalid
syntax (i have also tried it in upper case).

Am I supposed to use 'MyTable Set' or am i suppoed to use references to my
database (if that makes sense)?

Adrian

:

The SELECT Vinyl.[Picture] part doesn't belong in an update query.
Also the FROM Vinly doesn't belong there.

Here is an example I put together:

UPDATE _tblClientSave SET [_tblClientSave].AddressL1 = "3" &
Mid([AddressL1],2)
WHERE Left([AddressL1],1)="1";


So your's should be:

Update MyTable Set MyTable.[Path] = "D" & Mid([Path],2) Where Left([Path],1)
= "G";


--
Dave Hargis, Microsoft Access MVP


:

It's as below:

SELECT Vinyl.[Picture]
Update MyTable Set MyTable.[Path] = "D" & Mid([Path],2) Where Left([Path],1)
= "G"
FROM Vinyl;

Thanks

Adrian

:

Post the SQL of the query so we can have a look, please.

--
Dave Hargis, Microsoft Access MVP


:

I have been trying to concatinate two fields to hold the image path of my
image folder (e.g., C:\Vinyl Images) with my image names (e.g., Vinyl1.jpg)
so if the location changed it would just require one name. John W was
helping me with this but it does not seem to work.

Fredg suggested using an update query. He posted:

Update MyTable Set MyTable.[Path] = "D" & Mid([Path],2) Where
Left([Path],1) = "C"

The above will change "C\My Folder" to "D:\My Folder" for all records.

However, i can not get the query to run. It says i am mising some syntax!
The field name where the images are stored is 'Picture' in a table called
'Vinyl'.

Does anybody know what i am doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top