working with date ranges in recordset

  • Thread starter Thread starter Linda
  • Start date Start date
L

Linda

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


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.
 
Back
Top