Dcount Syntax Problem

[Guest]

Hello All,

I need some help sorting out a syntax problem with the DCount function. I
am trying to fill in file names from a specific folder to a table and exclude
any file names that may already be in the table. Here is the code I am using:

Function Findit()
Dim vItem As Variant
Dim db As DAO.Database
Dim i As Integer
Set db = CurrentDb
With Application.FileSearch
.filename = strFileName
.LookIn = "G:\Test Managment\Proposed Tests"
.SearchSubFolders = True
.Execute
For Each vItem In .FoundFiles

'Check if record exist using the dcount
i = 0
i = DCount("[filename]", "test", "" & Chr(34) & vItem & Chr(34) & "")
If i = 0 Then


db.Execute "INSERT INTO Test (Filename) " & _
"VALUES(" & Chr(34) & vItem & "#" & _
vItem & "#" & Chr(34) & ")", _
dbFailOnError


End If


Next vItem
End With
Set db = Nothing
End Function

The crieteria section of DCount is incorrect because it is counting the # of
records in the table and not the # of records with the value of vItem.


Any suggestions would be greatly appreciated.

Thanks

Carlos

 

[Douglas J. Steele]

That 3rd argument in the DCount function needs to look like a WHERE clause
(without the word WHERE).

I think you need:

i = DCount("*", "test", "[filename] = " & Chr(34) & vItem & Chr(34))

 

[Roger Carlson]

The criteria of a domain function is a Where condition without the WHERE
keyword. It is an expression that evaluates to yes or no. Your expression
always evaluates to "yes", therefore it counts all the records. You need to
have an expression that looks something like this:

i = DCount("[filename]", "test", "[filename] = '" & vItem & "'")


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Thank You both for your reply.
Both sentances run, but both are allowing multiple entries into the table
(ie i = 0 always). Some info that may be important:

When I run the code, the file name is input into the field "filename" which
is set to a hyperlink data type. Could it be that it is not seeing a match
because it is comparing text data to hyperlink data? if so, is it fixable

Thanks

The criteria of a domain function is a Where condition without the WHERE
keyword. It is an expression that evaluates to yes or no. Your expression
always evaluates to "yes", therefore it counts all the records. You need to
have an expression that looks something like this:

i = DCount("[filename]", "test", "[filename] = '" & vItem & "'")


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Hello All,

I need some help sorting out a syntax problem with the DCount function. I
am trying to fill in file names from a specific folder to a table and exclude
any file names that may already be in the table. Here is the code I am using:

Function Findit()
Dim vItem As Variant
Dim db As DAO.Database
Dim i As Integer
Set db = CurrentDb
With Application.FileSearch
.filename = strFileName
.LookIn = "G:\Test Managment\Proposed Tests"
.SearchSubFolders = True
.Execute
For Each vItem In .FoundFiles

'Check if record exist using the dcount
i = 0
i = DCount("[filename]", "test", "" & Chr(34) & vItem & Chr(34) & "")
If i = 0 Then


db.Execute "INSERT INTO Test (Filename) " & _
"VALUES(" & Chr(34) & vItem & "#" & _
vItem & "#" & Chr(34) & ")", _
dbFailOnError


End If


Next vItem
End With
Set db = Nothing
End Function

The crieteria section of DCount is incorrect because it is counting the # of
records in the table and not the # of records with the value of vItem.


Any suggestions would be greatly appreciated.

Thanks

Carlos

 

[Douglas J. Steele]

Have you checked what actually is being returned for vItem?

Since it's a hyperlink, it's possible that it contains special characters
and the like that you'd need to remove using the Replace function.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank You both for your reply.
Both sentances run, but both are allowing multiple entries into the table
(ie i = 0 always). Some info that may be important:

When I run the code, the file name is input into the field "filename"
which
is set to a hyperlink data type. Could it be that it is not seeing a
match
because it is comparing text data to hyperlink data? if so, is it fixable

Thanks

The criteria of a domain function is a Where condition without the WHERE
keyword. It is an expression that evaluates to yes or no. Your
expression
always evaluates to "yes", therefore it counts all the records. You need
to
have an expression that looks something like this:

i = DCount("[filename]", "test", "[filename] = '" & vItem & "'")


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Hello All,

I need some help sorting out a syntax problem with the DCount function.
I
am trying to fill in file names from a specific folder to a table and exclude
any file names that may already be in the table. Here is the code I am using:

Function Findit()
Dim vItem As Variant
Dim db As DAO.Database
Dim i As Integer
Set db = CurrentDb
With Application.FileSearch
.filename = strFileName
.LookIn = "G:\Test Managment\Proposed Tests"
.SearchSubFolders = True
.Execute
For Each vItem In .FoundFiles

'Check if record exist using the dcount
i = 0
i = DCount("[filename]", "test", "" & Chr(34) & vItem & Chr(34)
& "")
If i = 0 Then


db.Execute "INSERT INTO Test (Filename) " & _
"VALUES(" & Chr(34) & vItem & "#" & _
vItem & "#" & Chr(34) & ")", _
dbFailOnError


End If


Next vItem
End With
Set db = Nothing
End Function

The crieteria section of DCount is incorrect because it is counting the
# of
records in the table and not the # of records with the value of vItem.


Any suggestions would be greatly appreciated.

Thanks

Carlos

 

[Guest]

I was playing around with it yesterday and I got it to work
The end result was:
i = DCount("[filename]", "test", "[filename] = " & Chr(34) & vItem & "#" &
vItem & "#" & Chr(34) & "")

Thank you for all of your help.

Carlos

Have you checked what actually is being returned for vItem?

Since it's a hyperlink, it's possible that it contains special characters
and the like that you'd need to remove using the Replace function.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank You both for your reply.
Both sentances run, but both are allowing multiple entries into the table
(ie i = 0 always). Some info that may be important:

When I run the code, the file name is input into the field "filename"
which
is set to a hyperlink data type. Could it be that it is not seeing a
match
because it is comparing text data to hyperlink data? if so, is it fixable

Thanks

The criteria of a domain function is a Where condition without the WHERE
keyword. It is an expression that evaluates to yes or no. Your
expression
always evaluates to "yes", therefore it counts all the records. You need
to
have an expression that looks something like this:

i = DCount("[filename]", "test", "[filename] = '" & vItem & "'")


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Hello All,

I need some help sorting out a syntax problem with the DCount function.
I
am trying to fill in file names from a specific folder to a table and
exclude
any file names that may already be in the table. Here is the code I am
using:

Function Findit()
Dim vItem As Variant
Dim db As DAO.Database
Dim i As Integer
Set db = CurrentDb
With Application.FileSearch
.filename = strFileName
.LookIn = "G:\Test Managment\Proposed Tests"
.SearchSubFolders = True
.Execute
For Each vItem In .FoundFiles

'Check if record exist using the dcount
i = 0
i = DCount("[filename]", "test", "" & Chr(34) & vItem & Chr(34)
&
"")
If i = 0 Then


db.Execute "INSERT INTO Test (Filename) " & _
"VALUES(" & Chr(34) & vItem & "#" & _
vItem & "#" & Chr(34) & ")", _
dbFailOnError


End If


Next vItem
End With
Set db = Nothing
End Function

The crieteria section of DCount is incorrect because it is counting the
#
of
records in the table and not the # of records with the value of vItem.


Any suggestions would be greatly appreciated.

Thanks

Carlos