Re: dcount with multiple criteria

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Ramone (anonymous_at_discussions.microsoft.com)
Date: 08/19/04 

Date: Thu, 19 Aug 2004 06:35:05 -0700

When i add the space and and brackets. I don't get hte
error but it doesn't return any data. If you had any
other suggetions I would appreciate it. thanks John and
Ken

This is the code

Dim agntct As Integer
Dim dbs As Database, rst As Recordset
Dim hiretype As String
hiretype = "Direct"
agntct = DCount("[Agent Hire
Date]", "tblAgentdetail", "[Hire Type]='" & hiretype & "'
and [Agent Hire Date]>#12/31/2002# ")
    ' Return reference to current database.D
    Set dbs = CurrentDb
    ' Open table-type Recordset object.
    Set rst = dbs.OpenRecordset("TestAG")
    rst.AddNew
    
    rst.Fields("Year 1").Value = agntct
    rst.Update
    rst.Close
    Set dbs = Nothing
    
    
End Sub

>-----Original Message-----
>John correctly points out the need for a space before
the "and" word in the
>SQL text. Also, I would Dim hiredate as String instead
of Text.
>
>--
>
> Ken Snell
><MS ACCESS MVP>
>
><anonymous@discussions.microsoft.com> wrote in message
>news:8a0001c48567$bb17a070$a401280a@phx.gbl...
>> I have the following code
>>
>> Dim agntct As Integer
>> Dim dbs As Database, rst As Recordset
>> Dim hiretype As Text
>> hiretype = "Direct"
>> agntct = DCount("Agent Hire
>> Date", "tblAgentdetail", "[Hire Type]='" & hiretype
>> & "'and[Agent Hire Date]>#12/31/2002# ")
>> ' Return reference to current database.D
>> Set dbs = CurrentDb
>> ' Open table-type Recordset object.
>> Set rst = dbs.OpenRecordset("TestAG")
>> rst.AddNew
>>
>> rst.Fields("Year 1").Value = agntct
>> rst.Update
>> rst.Close
>> Set dbs = Nothing
>> textbox20 = agnt
>>
>> End Sub
>>
>> >-----Original Message-----
>> >I'm guessing it's "choking" on the hiretype variable.
Is
>> it Dim'd in the
>> >code?
>> >
>> >--
>> >
>> > Ken Snell
>> ><MS ACCESS MVP>
>> >
>> >"Ramone" <anonymous@discussions.microsoft.com> wrote
in
>> message
>> >news:866f01c48562$3741b9c0$a501280a@phx.gbl...
>> >> When I corrected the code i received the following
>> error
>> >>
>> >> User-defined type not defined. Can you tell me what
I'm
>> >> doing wrong
>> >>
>> >> Thanks again for all your help
>> >>
>> >>
>> >> >-----Original Message-----
>> >> >Change
>> >> > #12/31/002#
>> >> >
>> >> >to
>> >> > #12/31/2002#
>> >> >
>> >> >Also, the [ ] characters are not needed in the
first
>> >> argument:
>> >> >
>> >> >agntct = DCount("Agent
Name", "tblAgentdetail", "[Hire
>> >> Type]= '" & hiretype
>> >> >& "' and [Date Hired]>#12/31/2002#")
>> >> >
>> >> >
>> >> >--
>> >> >
>> >> > Ken Snell
>> >> ><MS ACCESS MVP>
>> >> >
>> >> >"Ramone" <anonymous@discussions.microsoft.com>
wrote
>> in
>> >> message
>> >> >news:8a0101c48542$e38ad9c0$a601280a@phx.gbl...
>> >> >> The code errors on this line
>> >> >>
>> >> >> agntct = DCount("[Agent
>> >> Name]", "tblAgentdetail", "[Hire
>> >> >> Type]= '" &
>> >> >> >hiretype & "' and [Date Hired]>#12/31/002# ")
>> >> >>
>> >> >> error not defined
>> >> >>
>> >> >> thanks
>> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >> >> >-----Original Message-----
>> >> >> >i think you have to add a space before end:
>> >> >> >agntct = DCount("[Agent
>> >> Name]", "tblAgentdetail", "[Hire
>> >> >> Type]= '" &
>> >> >> >hiretype & "' and [Date Hired]>#12/31/002# ")
>> >> >> >
>> >> >> >--
>> >> >> >Alex Dybenko (MVP)
>> >> >> >http://Alex.Dybenko.com
>> >> >> >http://www.PointLtd.com
>> >> >> >
>> >> >> >
>> >> >> >"Ramone" <anonymous@discussions.microsoft.com>
>> wrote
>> >> in
>> >> >> message
>> >> >> >news:11c701c4852b$1980dce0$a301280a@phx.gbl...
>> >> >> >>I was looking for help with the following code:
>> >> >> >>
>> >> >> >> I would like to count the number of agents
hired
>> >> from
>> >> >> the
>> >> >> >> agent detail table. Using the date hired and
Hire
>> >> Type
>> >> >> >> fields.
>> >> >> >>
>> >> >> >> Dim hiretype As Text
>> >> >> >> Dim agntct As Integer
>> >> >> >> Dim dbs As Database, rst As Recordset
>> >> >> >> hiretype = "Direct"
>> >> >> >> agntct = DCount("[Agent
>> >> >> Name]", "tblAgentdetail", "[Hire
>> >> >> >> Type]= '" & hiretype & "'and [Date Hired]
>> >> >> >#12/31/002# ")
>> >> >> >> ' Return reference to current database.D
>> >> >> >> Set dbs = CurrentDb
>> >> >> >> ' Open table-type Recordset object.
>> >> >> >> Set rst = dbs.OpenRecordset("TestAG")
>> >> >> >> rst.AddNew
>> >> >> >>
>> >> >> >> rst.Fields("Year 1").Value = agntct
>> >> >> >> rst.Update
>> >> >> >> rst.Close
>> >> >> >> Set dbs = Nothing
>> >> >> >>
>> >> >> >> End Sub
>> >> >> >>
>> >> >> >> Thanks for any help
>> >> >> >>
>> >> >> >
>> >> >> >
>> >> >> >.
>> >> >> >
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>
>.
>

Relevant Pages

  • RE: Recordset Problem
    ... The recordset is filtered from a large table we'll call ... Dim rsDEST As DAO.Recordset ... Dim dbs As DAO.Database ...
    (microsoft.public.access.formscoding)
  • Re: AddNew
    ... and then change the database and recordset object ... Dim dbS As DAO.Database ... Dim rcdData As DAO.Recordset ... > Dim area As String ' Area ...
    (microsoft.public.access.formscoding)
  • Re: Recordset Problem
    ... The recordset is filtered from a large table we'll call ... Dim rsDEST As DAO.Recordset ... Dim dbs As DAO.Database ...
    (microsoft.public.access.formscoding)
  • Re: dcount with multiple criteria
    ... >>hiretype is the name of a combo box? ... >>Dim agntct As Integer ... >>Dim dbs As Database, rst As Recordset ... >>>>> Dim dbs As Database, ...
    (microsoft.public.access.modulesdaovba)
  • Re: Recordset Problem
    ... The recordset is filtered from a large table we'll call ... Dim rsDEST As DAO.Recordset ... Dim dbs As DAO.Database ...
    (microsoft.public.access.formscoding)