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

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))


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Carlos" <(E-Mail Removed)> wrote in message
news:C3538EB8-CA06-44FF-A41A-(E-Mail Removed)...
> 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

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/...UBED1=ACCESS-L


"Carlos" <(E-Mail Removed)> wrote in message
news:C3538EB8-CA06-44FF-A41A-(E-Mail Removed)...
> 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

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

"Roger Carlson" wrote:

> 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/...UBED1=ACCESS-L
>
>
> "Carlos" <(E-Mail Removed)> wrote in message
> news:C3538EB8-CA06-44FF-A41A-(E-Mail Removed)...
> > 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
>
>
>

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
http://I.Am/DougSteele
(no e-mails, please!)


"Carlos" <(E-Mail Removed)> wrote in message
news:A0C1D558-97DC-41B8-B434-(E-Mail Removed)...
> 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
>
> "Roger Carlson" wrote:
>
>> 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/...UBED1=ACCESS-L
>>
>>
>> "Carlos" <(E-Mail Removed)> wrote in message
>> news:C3538EB8-CA06-44FF-A41A-(E-Mail Removed)...
>> > 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
>>
>>
>>

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

"Douglas J. Steele" wrote:

> 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
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Carlos" <(E-Mail Removed)> wrote in message
> news:A0C1D558-97DC-41B8-B434-(E-Mail Removed)...
> > 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
> >
> > "Roger Carlson" wrote:
> >
> >> 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/...UBED1=ACCESS-L
> >>
> >>
> >> "Carlos" <(E-Mail Removed)> wrote in message
> >> news:C3538EB8-CA06-44FF-A41A-(E-Mail Removed)...
> >> > 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
> >>
> >>
> >>
>
>
>