Solved Using multiple criteria in DCOUNT function using a comparison (1 Viewer)

[tonez90]

Hi,
I have run into a issue when trying to use the DCOUNT function with multiple criteria.
I wish to simply count a number of records that match two criteria. The table is a simple list of a directory list of files and I wish to count those files that are mp3 (and then sum up their sizes).
I am trying to do this:
Open the recordset using -> Set rspath = CurrentDb.OpenRecordset("Select Distinct [DiskPath], [FileName], [FileType] from " & tablename & " ORDER by [DiskPath];")
Where [diskpath] is the path to the file, [Filename] is the actual file name, and [filetype] holds the type such as "MP3 Format Sound" etc, [Tablename] is a simple name (this works fine)

Then i am using: - > thecount = DCount("[FileName]", tablename, "[diskpath] = '" & rspath![DiskPath] & "' and lcase(rspath![Filetype]) LIKE '*mp3*'")
I also use the DSum function to calculate the total size of all selected files.

The issue is with the second criteria: "' and lcase(rspath![Filetype]) LIKE '*mp3*'" as the dcount works if I leave it out, but it counts ALL files and not just the mp3 ones.

What Am I doing wrong here?
Any advice appreciated.
Cheers
Tony.

 

[June7]

The case is irrelevant.

Why not do aggregation in query instead of running domain aggregate functions? Why do you even need a recordset?

What do you do with retrieved aggregate values?

Why is table name variable?

Need to reference field that file type is in.

DCount("*", tablename, "[diskpath] = '" & rspath![DiskPath] & "' And InStr([FileType], 'mp3')>0")