I have the following routine:

Set rstin = dbs.OpenRecordset("Select * From
detailsmobile Where CallDate Between #" & varDateBeg & "#
And #" & varDateEnd & "#")
Set rstsplit = dbs.OpenRecordset("tblTenants")
Set rstout = dbs.OpenRecordset("tblresult")

Note: vardatebeg contains the value #01/01/2004#
and vardateend contains the value #02/01/2004#

tbltenants contains the Telephonenrs and the startdates
and the enddates.

rstsplit.MoveFirst
Do Until rstsplit.EOF

If rstsplit!EndDate >= varDateBeg And rstsplit!EndDate <=
varDateEnd Then

searchnumber = rstsplit!TelephoneNumber
FindFirst "CallingNumber='" & searchnumber & "'"
If rstin.NoMatch Then
rstin.Edit
Else
rstout.AddNew
rstout!TelephoneNumber = rstin!CallingNumber
rstout!OccupantID = rstsplit!OccupantID
rstout.Update
End If
End If
rstsplit.MoveNext
Loop

what I want to do here is that when the end date from
tbltenants fall with the period of vardatebeg and
enddatebeg that the telephonennr and occupantid gets
added to the table tblresult.

In my table tbltenants I have some enddates that meet
that criteria and for some strange reason my routine does
not pick up these rows.meaning they don't get added to
the tblresult.also in the query detailsmobile for each
telephonenr and calldate there is an amount, a sum should
made of the amounts that fall within the condition:
If rstsplit!EndDate >= varDateBeg And rstsplit!EndDate <=
varDateEnd, so for example if the enddate is 01/10/2004
then a total should be made for all calls made between
01/01/2004 and 01/10/2004. Any help is most appreciated.

Linda wrote:

>I have the following routine:
>
>Set rstin = dbs.OpenRecordset("Select * From
>detailsmobile Where CallDate Between #" & varDateBeg & "#
>And #" & varDateEnd & "#")
>Set rstsplit = dbs.OpenRecordset("tblTenants")
>Set rstout = dbs.OpenRecordset("tblresult")
>
>Note: vardatebeg contains the value #01/01/2004#
>and vardateend contains the value #02/01/2004#
>
>tbltenants contains the Telephonenrs and the startdates
>and the enddates.
>
>rstsplit.MoveFirst
>Do Until rstsplit.EOF
>
>If rstsplit!EndDate >= varDateBeg And rstsplit!EndDate <=
>varDateEnd Then
>
>searchnumber = rstsplit!TelephoneNumber
>FindFirst "CallingNumber='" & searchnumber & "'"
>If rstin.NoMatch Then
> rstin.Edit
>Else
> rstout.AddNew
> rstout!TelephoneNumber = rstin!CallingNumber
> rstout!OccupantID = rstsplit!OccupantID
> rstout.Update
>End If
>End If
>rstsplit.MoveNext
>Loop
>
>what I want to do here is that when the end date from
>tbltenants fall with the period of vardatebeg and
>enddatebeg that the telephonennr and occupantid gets
>added to the table tblresult.
>
>In my table tbltenants I have some enddates that meet
>that criteria and for some strange reason my routine does
>not pick up these rows.meaning they don't get added to
>the tblresult.also in the query detailsmobile for each
>telephonenr and calldate there is an amount, a sum should
>made of the amounts that fall within the condition:
>If rstsplit!EndDate >= varDateBeg And rstsplit!EndDate <=
>varDateEnd, so for example if the enddate is 01/10/2004
>then a total should be made for all calls made between
>01/01/2004 and 01/10/2004. Any help is most appreciated.


Beyond any errors in your code, I think there just might be
a more fundamental design problem here. It seems like you
are duplicating data as well as trying to store calculated
values. I can't seem to grasp what your objective is with
this, but it sure feels like you took a left turn somewhere
along the road to where ever you want to go.

As for the code you posted, there are at least a couple of
serious errors. One is that the FindFirst method must be
used with a recordset object:
rstin.FindFirst "CallingNumber='" & searchnumber & "'"

Another problem is that when there's NoMatch you use:
rstin.Edit
without any editing operations or a corresponding
rstin.Update. I just can't figure out what that may or may
not have to do with anything.

You also say you want to calculate a total of the amounts,
but I think that should be done when you display a report of
the data. As far as I can tell, calculating a total has
nothing to do with whatever is supposed to happen in the
code you posted.
--
Marsh
MVP [MS Access]

No-one can really answer this, unless you tell us what is the Primary Key of
each table.

If you do not know what a Primary Key is, you have some study to do, before
you do more coding. Maybe start here:
http://support.microsoft.com/support...es/Q100139.ASP

HTH,
TC


"Linda" <(E-Mail Removed)> wrote in message
news:07a401c42529$1d99c4c0$(E-Mail Removed)...
> I have the following routine:
>
> Set rstin = dbs.OpenRecordset("Select * From
> detailsmobile Where CallDate Between #" & varDateBeg & "#
> And #" & varDateEnd & "#")
> Set rstsplit = dbs.OpenRecordset("tblTenants")
> Set rstout = dbs.OpenRecordset("tblresult")
>
> Note: vardatebeg contains the value #01/01/2004#
> and vardateend contains the value #02/01/2004#
>
> tbltenants contains the Telephonenrs and the startdates
> and the enddates.
>
> rstsplit.MoveFirst
> Do Until rstsplit.EOF
>
> If rstsplit!EndDate >= varDateBeg And rstsplit!EndDate <=
> varDateEnd Then
>
> searchnumber = rstsplit!TelephoneNumber
> FindFirst "CallingNumber='" & searchnumber & "'"
> If rstin.NoMatch Then
> rstin.Edit
> Else
> rstout.AddNew
> rstout!TelephoneNumber = rstin!CallingNumber
> rstout!OccupantID = rstsplit!OccupantID
> rstout.Update
> End If
> End If
> rstsplit.MoveNext
> Loop
>
> what I want to do here is that when the end date from
> tbltenants fall with the period of vardatebeg and
> enddatebeg that the telephonennr and occupantid gets
> added to the table tblresult.
>
> In my table tbltenants I have some enddates that meet
> that criteria and for some strange reason my routine does
> not pick up these rows.meaning they don't get added to
> the tblresult.also in the query detailsmobile for each
> telephonenr and calldate there is an amount, a sum should
> made of the amounts that fall within the condition:
> If rstsplit!EndDate >= varDateBeg And rstsplit!EndDate <=
> varDateEnd, so for example if the enddate is 01/10/2004
> then a total should be made for all calls made between
> 01/01/2004 and 01/10/2004. Any help is most appreciated.
>