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"
