dcount with multiple criteria

  • Thread starter Thread starter Ramone
  • Start date Start date
R

Ramone

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
 
i think you have to add a space before end:
agntct = DCount("[Agent Name]", "tblAgentdetail", "[Hire Type]= '" &
hiretype & "' and [Date Hired]>#12/31/002# ")
 
The code errors on this line

agntct = DCount("[Agent Name]", "tblAgentdetail", "[Hire
Type]= '" &
hiretype & "' and [Date Hired]>#12/31/002# ")
Click to expand...

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


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
Click to expand...


.
Click to expand...
 
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 said:
The code errors on this line

agntct = DCount("[Agent Name]", "tblAgentdetail", "[Hire
Type]= '" &
hiretype & "' and [Date Hired]>#12/31/002# ")
Click to expand...

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


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
Click to expand...


.
Click to expand...
Click to expand...
 
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>

The code errors on this line

agntct = DCount("[Agent Name]", "tblAgentdetail", "[Hire
Type]= '" &
hiretype & "' and [Date Hired]>#12/31/002# ")
Click to expand...

error not defined

thanks



-----Original Message-----
i think you have to add a space before end:
agntct = DCount("[Agent
Click to expand...
Name]", "tblAgentdetail", "[Hire
Type]= '" &
hiretype & "' and [Date Hired]>#12/31/002# ")

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com


"Ramone" <[email protected]> wrote
Click to expand...
in
message
I was looking for help with the following code:

I would like to count the number of agents hired
Click to expand...
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
Click to expand...
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



.
Click to expand...
Click to expand...


.
Click to expand...
 
I'm guessing it's "choking" on the hiretype variable. Is it Dim'd in the
code?

--

Ken Snell
<MS ACCESS MVP>

Ramone said:
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>

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


message
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



.
Click to expand...


.
Click to expand...
Click to expand...
 
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>

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" <[email protected]> wrote
Click to expand...
in
message
The code errors on this line

agntct = DCount("[Agent
Click to expand...
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
Click to expand...
Name]", "tblAgentdetail", "[Hire
Type]= '" &
hiretype & "' and [Date Hired]>#12/31/002# ")

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com


"Ramone" <[email protected]>
Click to expand...
wrote
in
message
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



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
You need to surround any field name with spaces or other "special" characters
with brackets [].
You need a space between the apostrophe and the word "And" and a space between
"And" and the [Agent Hire Date].

agntCt = DCount("[Agent Hire Date]", "tblAgentdetail", _
"[Hire Type]='" & hiretype & _
"' AND [Agent Hire Date]>#12/31/2002#")

Also, you could be missing a reference to DAO (Tools: References) and you
probably need to specify which library to use DAO or ADO.

So, Decalre

Dim dbs as DAO.Database, rst as DAO.Recordset


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>

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>

message
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


in
message
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



.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Shouldn't need the [ ] in the first argument, John, but definitely need them
in the third argument.

--

Ken Snell
<MS ACCESS MVP>

John Spencer (MVP) said:
You need to surround any field name with spaces or other "special" characters
with brackets [].
You need a space between the apostrophe and the word "And" and a space between
"And" and the [Agent Hire Date].

agntCt = DCount("[Agent Hire Date]", "tblAgentdetail", _
"[Hire Type]='" & hiretype & _
"' AND [Agent Hire Date]>#12/31/2002#")

Also, you could be missing a reference to DAO (Tools: References) and you
probably need to specify which library to use DAO or ADO.

So, Decalre

Dim dbs as DAO.Database, rst as DAO.Recordset


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>

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>

message
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


in
message
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



.



.



.
Click to expand...
Click to expand...
Click to expand...
 
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>

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>

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>

message
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


in
message
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



.



.
Click to expand...


.
Click to expand...
Click to expand...
 
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>

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.
Click to expand...
Is
it Dim'd in the
code?

--

Ken Snell
<MS ACCESS MVP>

"Ramone" <[email protected]> wrote
Click to expand...
in
message
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" <[email protected]>
Click to expand...
wrote
in
message
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


in
message
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



.



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
I am confused. Your code is opening a recordset solely for the purpose of
adding a new record to the recordset's source table/query. As such, that
recordset will not return any records or data. So, can you explain what
you're expecting this code to do?

--

Ken Snell
<MS ACCESS MVP>

Ramone said:
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>

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>

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

in
message
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" <[email protected]>
wrote
in
message
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



.



.



.
Click to expand...


.
Click to expand...
Click to expand...
 
i
-----Original Message-----
I am confused. Your code is opening a recordset solely for the purpose of
adding a new record to the recordset's source table/query. As such, that
recordset will not return any records or data. So, can you explain what
you're expecting this code to do?

--

Ken Snell
<MS ACCESS MVP>

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>

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
Click to expand...
variable.
Is
it Dim'd in the
code?

--

Ken Snell
<MS ACCESS MVP>

"Ramone" <[email protected]>
Click to expand...
wrote
in
message
When I corrected the code i received the following
error

User-defined type not defined. Can you tell me
Click to expand...
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
Click to expand...
Name", "tblAgentdetail", "[Hire
Type]= '" & hiretype
& "' and [Date Hired]>#12/31/2002#")


--

Ken Snell
<MS ACCESS MVP>

in
message
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"
Click to expand...
wrote
in
message
[email protected]...
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
Click to expand...
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



.



.



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
I would like to count the number of new hires from my
agent detail report. then add a new record to my table
with this value.

It works fine when i count the records based on date
only, however when I add the second critera it doesn't
give me any data. I think it may be because the hire type
is a drop down box.

thanks again for your help


-----Original Message-----
I am confused. Your code is opening a recordset solely for the purpose of
adding a new record to the recordset's source table/query. As such, that
recordset will not return any records or data. So, can you explain what
you're expecting this code to do?

--

Ken Snell
<MS ACCESS MVP>

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>

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
Click to expand...
variable.
Is
it Dim'd in the
code?

--

Ken Snell
<MS ACCESS MVP>

"Ramone" <[email protected]>
Click to expand...
wrote
in
message
When I corrected the code i received the following
error

User-defined type not defined. Can you tell me
Click to expand...
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
Click to expand...
Name", "tblAgentdetail", "[Hire
Type]= '" & hiretype
& "' and [Date Hired]>#12/31/2002#")


--

Ken Snell
<MS ACCESS MVP>

in
message
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"
Click to expand...
wrote
in
message
[email protected]...
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
Click to expand...
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



.



.



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
hiretype is the name of a combo box? Oh, then change your code this way
(I've deleted the Dim statement for hiretype and deleted the line that set
it to a value):



Dim agntct As Integer
Dim dbs As Database, rst As Recordset


agntct = DCount("[Agent Hire
Date]", "tblAgentdetail", "[Hire Type]='" & Me.hiretype.Value & "'
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

--

Ken Snell
<MS ACCESS MVP>

I would like to count the number of new hires from my
agent detail report. then add a new record to my table
with this value.

It works fine when i count the records based on date
only, however when I add the second critera it doesn't
give me any data. I think it may be because the hire type
is a drop down box.

thanks again for your help


-----Original Message-----
I am confused. Your code is opening a recordset solely for the purpose of
adding a new record to the recordset's source table/query. As such, that
recordset will not return any records or data. So, can you explain what
you're expecting this code to do?

--

Ken Snell
<MS ACCESS MVP>

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>

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>

in
message
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" <[email protected]>
wrote
in
message
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"
Click to expand...
wrote
in
message
[email protected]...
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



.



.



.



.
Click to expand...


.
Click to expand...
Click to expand...
 
When I copied your code I received the following error

method or data member not found

thanks again for all your help


-----Original Message-----
hiretype is the name of a combo box? Oh, then change your code this way
(I've deleted the Dim statement for hiretype and deleted the line that set
it to a value):



Dim agntct As Integer
Dim dbs As Database, rst As Recordset


agntct = DCount("[Agent Hire
Date]", "tblAgentdetail", "[Hire Type]='" & Me.hiretype.Value & "'
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

--

Ken Snell
<MS ACCESS MVP>

I would like to count the number of new hires from my
agent detail report. then add a new record to my table
with this value.

It works fine when i count the records based on date
only, however when I add the second critera it doesn't
give me any data. I think it may be because the hire type
is a drop down box.

thanks again for your help


-----Original Message-----
I am confused. Your code is opening a recordset solely for the purpose of
adding a new record to the recordset's source table/query. As such, that
recordset will not return any records or data. So, can you explain what
you're expecting this code to do?

--

Ken Snell
<MS ACCESS MVP>

"Ramone" <[email protected]> wrote
Click to expand...
in
message
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>

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>

in
message
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"
Click to expand...
wrote
in
message
[email protected]...
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"
wrote
in
message
news:11c701c4852b$1980dce0
Click to expand...
[email protected]...
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



.



.



.



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
On which line of the code did the error occur? Is hiretype the name of a
control on your form?

--

Ken Snell
<MS ACCESS MVP>

Ramone said:
When I copied your code I received the following error

method or data member not found

thanks again for all your help


-----Original Message-----
hiretype is the name of a combo box? Oh, then change your code this way
(I've deleted the Dim statement for hiretype and deleted the line that set
it to a value):



Dim agntct As Integer
Dim dbs As Database, rst As Recordset


agntct = DCount("[Agent Hire
Date]", "tblAgentdetail", "[Hire Type]='" & Me.hiretype.Value & "'
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

--

Ken Snell
<MS ACCESS MVP>

I would like to count the number of new hires from my
agent detail report. then add a new record to my table
with this value.

It works fine when i count the records based on date
only, however when I add the second critera it doesn't
give me any data. I think it may be because the hire type
is a drop down box.

thanks again for your help



-----Original Message-----
I am confused. Your code is opening a recordset solely
for the purpose of
adding a new record to the recordset's source
table/query. As such, that
recordset will not return any records or data. So, can
you explain what
you're expecting this code to do?

--

Ken Snell
<MS ACCESS MVP>

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

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" <[email protected]>
wrote
in
message
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"
Click to expand...
wrote
in
message
[email protected]...
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"
<[email protected]>
wrote
in
message
[email protected]...
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



.



.



.



.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Hire Type is the name of the field in the table and this
is where the error occured
-----Original Message-----
On which line of the code did the error occur? Is hiretype the name of a
control on your form?

--

Ken Snell
<MS ACCESS MVP>

When I copied your code I received the following error

method or data member not found

thanks again for all your help


-----Original Message-----
hiretype is the name of a combo box? Oh, then change your code this way
(I've deleted the Dim statement for hiretype and
Click to expand...
deleted
the line that set
it to a value):



Dim agntct As Integer
Dim dbs As Database, rst As Recordset


agntct = DCount("[Agent Hire
Date]", "tblAgentdetail", "[Hire Type]='" & Me.hiretype.Value & "'
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

--

Ken Snell
<MS ACCESS MVP>

I would like to count the number of new hires from my
agent detail report. then add a new record to my table
with this value.

It works fine when i count the records based on date
only, however when I add the second critera it doesn't
give me any data. I think it may be because the hire type
is a drop down box.

thanks again for your help



-----Original Message-----
I am confused. Your code is opening a recordset solely
for the purpose of
adding a new record to the recordset's source
table/query. As such, that
recordset will not return any records or data. So, can
you explain what
you're expecting this code to do?

--

Ken Snell
<MS ACCESS MVP>

"Ramone" <[email protected]>
Click to expand...
wrote
in
message
When i add the space and and brackets. I don't
Click to expand...
get
hte
error but it doesn't return any data. If you had any
other suggetions I would appreciate it. thanks
Click to expand...
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>

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"
Click to expand...
wrote
in
message
[email protected]...
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"
wrote
in
message
news:8a0101c48542$e38ad9c0
Click to expand...
[email protected]...
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"
<[email protected]>
wrote
in
message
[email protected]...
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



.



.



.



.



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
I have only been using vb for about 2 months and
apologizes for the confusing issue.

I have one main table (tblAgentDetail) this table has
about 600 records and 20 fields.

I would like to click a button on my form that runs code
that counts the number of agents based on thier hire date
and hire type. The result should then be entered in a
seprate table called (testAG)

Thanks again for your help



-----Original Message-----
On which line of the code did the error occur? Is hiretype the name of a
control on your form?

--

Ken Snell
<MS ACCESS MVP>

When I copied your code I received the following error

method or data member not found

thanks again for all your help


-----Original Message-----
hiretype is the name of a combo box? Oh, then change your code this way
(I've deleted the Dim statement for hiretype and
Click to expand...
deleted
the line that set
it to a value):



Dim agntct As Integer
Dim dbs As Database, rst As Recordset


agntct = DCount("[Agent Hire
Date]", "tblAgentdetail", "[Hire Type]='" & Me.hiretype.Value & "'
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

--

Ken Snell
<MS ACCESS MVP>

I would like to count the number of new hires from my
agent detail report. then add a new record to my table
with this value.

It works fine when i count the records based on date
only, however when I add the second critera it doesn't
give me any data. I think it may be because the hire type
is a drop down box.

thanks again for your help



-----Original Message-----
I am confused. Your code is opening a recordset solely
for the purpose of
adding a new record to the recordset's source
table/query. As such, that
recordset will not return any records or data. So, can
you explain what
you're expecting this code to do?

--

Ken Snell
<MS ACCESS MVP>

"Ramone" <[email protected]>
Click to expand...
wrote
in
message
When i add the space and and brackets. I don't
Click to expand...
get
hte
error but it doesn't return any data. If you had any
other suggetions I would appreciate it. thanks
Click to expand...
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>

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"
Click to expand...
wrote
in
message
[email protected]...
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"
wrote
in
message
news:8a0101c48542$e38ad9c0
Click to expand...
[email protected]...
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"
<[email protected]>
wrote
in
message
[email protected]...
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



.



.



.



.



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
OK- is hiretype a field in the form's recordsource table/query? It would
appear from the error that you're getting that it's not.

--

Ken Snell
<MS ACCESS MVP>

Hire Type is the name of the field in the table and this
is where the error occured
-----Original Message-----
On which line of the code did the error occur? Is hiretype the name of a
control on your form?

--

Ken Snell
<MS ACCESS MVP>

When I copied your code I received the following error

method or data member not found

thanks again for all your help



-----Original Message-----
hiretype is the name of a combo box? Oh, then change
your code this way
(I've deleted the Dim statement for hiretype and deleted
the line that set
it to a value):



Dim agntct As Integer
Dim dbs As Database, rst As Recordset


agntct = DCount("[Agent Hire
Date]", "tblAgentdetail", "[Hire Type]='" &
Me.hiretype.Value & "'
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

--

Ken Snell
<MS ACCESS MVP>

I would like to count the number of new hires from my
agent detail report. then add a new record to my table
with this value.

It works fine when i count the records based on date
only, however when I add the second critera it doesn't
give me any data. I think it may be because the hire
type
is a drop down box.

thanks again for your help



-----Original Message-----
I am confused. Your code is opening a recordset solely
for the purpose of
adding a new record to the recordset's source
table/query. As such, that
recordset will not return any records or data. So, can
you explain what
you're expecting this code to do?

--

Ken Snell
<MS ACCESS MVP>

in
message
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>

message
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"
Click to expand...
wrote
in
message
[email protected]...
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"
<[email protected]>
wrote
in
message
[email protected]...
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"
<[email protected]>
wrote
in
message
[email protected]...
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



.



.



.



.



.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top