Update problem using Dsum with like

[Rory]

I have a table containing the directories, names and sizes of a set of files.
I have created a table containing just the directories. I am now trying to
update the directory size column of the directory table by summing the sizes
of all files in each directory and its subdirectories. My query returns a
data conversion error. Both size fields are 'doubles'. My query is:
UPDATE Directories SET Directories.DirSize =
DSUM("[FileSize]", "Files", "[Path] like """ & [Path] & [Name] & "*""");
Any ideas?
Rory

 

[Allen Browne]

In what context are you running this?

To debug the problem, open the Immediate Window (Ctrl+G), and break the
expression down into chunks:
? "[Path] like """ & [Path] & [Name] & "*"""
? DSUM("[FileSize]", "Files", "[Path] like """ & [Path] & [Name] & "*""")
and so on.

Nearly everything in Access has a Name property, so Access may misunderstand
what you intend there. But I would expect that would cause no result, not a
type conversion error.

 

[Rory]

Thanks Allen for your response. I am running this as an Access Query. I have
not come accross the Immediate Window before. When I looked it up it is to do
with Visual Basic - how do I use it to debug an Access Query?

Your comment about Access misunderstanding caused me to change a few field
names in the tables so that all had different names. I changed 'Path' and
'Name' to 'FilePath', 'FileName' in the 'Files' table and 'DirPath' and
'DirName' in the 'Directories' table. This time I did not get the conversion
error but did get the empty field (which I got with the query below in
addition to the conversion error as you suspected I should).

The context is. 'Files' and 'Directories' are tables in the same Access
database as the query. 'DirSize' and 'FileSize' are Double fields in the
'Directories' and 'Files' tables. 'DirPath', 'DirName', 'FilePath' and
'FileName' are 255 text fields in the 'Directories' and 'Files' tables.

The content of the 'Files' table is a list of all files and directories of a
hard drive gained using the 'log parser' utility. The 'FilePath' field is the
directory path with out the file or directory name. I populated the
'Directories' table with an insert query from the 'Files' table extracting
the records with a 'D' in the Attributes column. Thus the intention is to
find the total size of all the files in each directory and its subdirectories
- hence the 'like' condition with a '*' on the end.

If you are able to help further with that information then I will be very
grateful.

Thanks for your time.

In what context are you running this?

To debug the problem, open the Immediate Window (Ctrl+G), and break the
expression down into chunks:
? "[Path] like """ & [Path] & [Name] & "*"""
? DSUM("[FileSize]", "Files", "[Path] like """ & [Path] & [Name] & "*""")
and so on.

Nearly everything in Access has a Name property, so Access may misunderstand
what you intend there. But I would expect that would cause no result, not a
type conversion error.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a table containing the directories, names and sizes of a set of
files.
I have created a table containing just the directories. I am now trying to
update the directory size column of the directory table by summing the
sizes
of all files in each directory and its subdirectories. My query returns a
data conversion error. Both size fields are 'doubles'. My query is:
UPDATE Directories SET Directories.DirSize =
DSUM("[FileSize]", "Files", "[Path] like """ & [Path] & [Name] & "*""");
Any ideas?
Rory

 

[Allen Browne]

Not sure why the file name is in the DSum() expression.

Did you intend something like this:

UPDATE Directories
SET Directories.DirSize = DSum("FileSize", "Files",
"FilePath Like """ & Directories.DirPath & "*""");

Might need to consider the trailing slash to get this accurate.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen for your response. I am running this as an Access Query. I
have
not come accross the Immediate Window before. When I looked it up it is to
do
with Visual Basic - how do I use it to debug an Access Query?

Your comment about Access misunderstanding caused me to change a few field
names in the tables so that all had different names. I changed 'Path' and
'Name' to 'FilePath', 'FileName' in the 'Files' table and 'DirPath' and
'DirName' in the 'Directories' table. This time I did not get the
conversion
error but did get the empty field (which I got with the query below in
addition to the conversion error as you suspected I should).

The context is. 'Files' and 'Directories' are tables in the same Access
database as the query. 'DirSize' and 'FileSize' are Double fields in the
'Directories' and 'Files' tables. 'DirPath', 'DirName', 'FilePath' and
'FileName' are 255 text fields in the 'Directories' and 'Files' tables.

The content of the 'Files' table is a list of all files and directories of
a
hard drive gained using the 'log parser' utility. The 'FilePath' field is
the
directory path with out the file or directory name. I populated the
'Directories' table with an insert query from the 'Files' table extracting
the records with a 'D' in the Attributes column. Thus the intention is to
find the total size of all the files in each directory and its
subdirectories
- hence the 'like' condition with a '*' on the end.

If you are able to help further with that information then I will be very
grateful.

Thanks for your time.

In what context are you running this?

To debug the problem, open the Immediate Window (Ctrl+G), and break the
expression down into chunks:
? "[Path] like """ & [Path] & [Name] & "*"""
? DSUM("[FileSize]", "Files", "[Path] like """ & [Path] & [Name] & "*""")
and so on.

Nearly everything in Access has a Name property, so Access may
misunderstand
what you intend there. But I would expect that would cause no result, not
a
type conversion error.

I have a table containing the directories, names and sizes of a set of
files.
I have created a table containing just the directories. I am now trying
to
update the directory size column of the directory table by summing the
sizes
of all files in each directory and its subdirectories. My query returns
a
data conversion error. Both size fields are 'doubles'. My query is:
UPDATE Directories SET Directories.DirSize =
DSUM("[FileSize]", "Files", "[Path] like """ & [Path] & [Name] &
"*""");
Any ideas?
Rory

 

[Rory]

Thanks Allen,
I used DirName and DirPath because the Directories table had the path and
name of the directory in these separate fields. What I needed to do was
include the slash in between these two to construct the correct string for
'like'. This worked! The final query was:

UPDATE Directories SET Directories.DirSize =
DSUM("[FileSize]", "Files", "[FilePath] like """ & [DirPath] & "/" &
[DirName] &
"*""");

Thanks for all the help.

Not sure why the file name is in the DSum() expression.

Did you intend something like this:

UPDATE Directories
SET Directories.DirSize = DSum("FileSize", "Files",
"FilePath Like """ & Directories.DirPath & "*""");

Might need to consider the trailing slash to get this accurate.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen for your response. I am running this as an Access Query. I
have
not come accross the Immediate Window before. When I looked it up it is to
do
with Visual Basic - how do I use it to debug an Access Query?

Your comment about Access misunderstanding caused me to change a few field
names in the tables so that all had different names. I changed 'Path' and
'Name' to 'FilePath', 'FileName' in the 'Files' table and 'DirPath' and
'DirName' in the 'Directories' table. This time I did not get the
conversion
error but did get the empty field (which I got with the query below in
addition to the conversion error as you suspected I should).

The context is. 'Files' and 'Directories' are tables in the same Access
database as the query. 'DirSize' and 'FileSize' are Double fields in the
'Directories' and 'Files' tables. 'DirPath', 'DirName', 'FilePath' and
'FileName' are 255 text fields in the 'Directories' and 'Files' tables.

The content of the 'Files' table is a list of all files and directories of
a
hard drive gained using the 'log parser' utility. The 'FilePath' field is
the
directory path with out the file or directory name. I populated the
'Directories' table with an insert query from the 'Files' table extracting
the records with a 'D' in the Attributes column. Thus the intention is to
find the total size of all the files in each directory and its
subdirectories
- hence the 'like' condition with a '*' on the end.

If you are able to help further with that information then I will be very
grateful.

Thanks for your time.

In what context are you running this?

To debug the problem, open the Immediate Window (Ctrl+G), and break the
expression down into chunks:
? "[Path] like """ & [Path] & [Name] & "*"""
? DSUM("[FileSize]", "Files", "[Path] like """ & [Path] & [Name] & "*""")
and so on.

Nearly everything in Access has a Name property, so Access may
misunderstand
what you intend there. But I would expect that would cause no result, not
a
type conversion error.

I have a table containing the directories, names and sizes of a set of
files.
I have created a table containing just the directories. I am now trying
to
update the directory size column of the directory table by summing the
sizes
of all files in each directory and its subdirectories. My query returns
a
data conversion error. Both size fields are 'doubles'. My query is:
UPDATE Directories SET Directories.DirSize =
DSUM("[FileSize]", "Files", "[Path] like """ & [Path] & [Name] &
"*""");
Any ideas?
Rory