By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,667 Members | 2,611 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,667 IT Pros & Developers. It's quick & easy.

Simple (?) code in Access 2000

P: n/a
Can anybody help me with some Access 2000 code?

I don't do a lot of coding in Access & so every time I come back to do
something I pretty much have to relearn the syntax from scratch so this is
taking me hours. As with most occasional programmers its the commas, colons,
inverted commas, square brackets etc etc that cause the problem! (Not to
mention the nuances of using Do While, Do until, Case statements If
statements, IIF etc etc...) .I think I have the logic of the thing sorted,
its the syntax that's holding me up.

I'm trying to do something fairly simple to update a table with several
hundred thousand records in it (its a phone bill). I do want to update the
table rather than calculate on the fly because once I have the data I need
then it won't change & there is a whole range of different tasks to carry
out afterwards using queries etc to actually break the thing down and get
some meaningful results from it (which can vary from one bill to the next).
Since there may be several hundred thousand records in the table calculating
this on the fly everytime I want to use the data would take far too long I
think so better to run this once & be done with it.

So here's the problem....

I have a table with records of individual calls - for each one I have a
start time & date and a duration in seconds. In order to rate (re-price) the
call properly I have to ascertain which if three chargebands the call falls
into - and it may cross chargebands in which case part of the call will be
charged at one price and part at another. All I am trying to do is to break
down the duration into the different chargebands. Once I have this then I
can re-price each call & summarise the bill using & I have that part
covered (using a rates table and queries).

So in my table I have three fields prepared (one for each chargeband) into
which I need to divide up the total duration of the call which I already
have in seconds (just stored as a number)

So what I thought I'd do would be

Open the table as a recordset
Declare some variables as follows:
Band1, Band2 & Band3 (to contain the number of seconds from a given call
that fall into each charge band)
sttime to contain the start time of the call
durn to contain the call duration in seconds

Go to the first record (call) in the table

Pull the call start time of the call from a field (which is a properly
formatted general date field including the time and the date) from the
record and put into "sttime"

Pull the total duration from a field in the table and put it into "durn"

Test which chargeband the "sttime" falls into using some case statements to
determine day of week and time of day etc

Add 1 to one of three Band variables based in the result
Deduct 1 from "durn"
Add 1 second to "sttime"
then loop round & keep testing till "durn" gets to zero.

Once "Durn" gets to zero then store/save the results of Band1, Band2 & Band3
back into the table in the prepared fields that are there

Move to the next record, zero all the variables, pull the next record's
start time and duration and repeat all this till it gets to the end of the
file.

At this point for each record in the table I should have the total duration
(which I had to start with) and three fields (Band 1,2 & 3) which contain
numbers of seconds which, if added together, will equal the number in the
Total duration field. For many records two of the three will be zero & the
third will contain the same number as the Total duration field (i.e. where a
call just falls into one chargeband). For some records (for calls that cross
from one band to another) I might have figures in two of the three fields &
for a few (for very long calls) I might have numbers in all three.

If I trigger this from a button on a form it'd be nice to put a counter on
the form that would show the thing working by counting up 1 for each record
it processes. I don't need to display the table as its processing & nor do I
need to see the results in a form - I just want to update the table as
quickly as possible.

thanks in advance for any help with the syntax required.

Iain

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
If you'd explain what chargebands represent, it's entirely possible that
someone might be able to suggest a method somewhat more efficient that
looping second by second.

You've told us in detail one way you see _how_ to calculate it, but not a
definition of what you want to calculate. I would assume that chargebands
are time brackets, but it would really help if you would clarify.

Larry Linson
Microsoft Access MVP

"Iain Miller" <do***@spam.me> wrote in message
news:Gx*****************@newsfe1-gui.ntli.net...
Can anybody help me with some Access 2000 code?

I don't do a lot of coding in Access & so every time I come back to do
something I pretty much have to relearn the syntax from scratch so this is taking me hours. As with most occasional programmers its the commas, colons, inverted commas, square brackets etc etc that cause the problem! (Not to
mention the nuances of using Do While, Do until, Case statements If
statements, IIF etc etc...) .I think I have the logic of the thing sorted,
its the syntax that's holding me up.

I'm trying to do something fairly simple to update a table with several
hundred thousand records in it (its a phone bill). I do want to update the
table rather than calculate on the fly because once I have the data I need
then it won't change & there is a whole range of different tasks to carry
out afterwards using queries etc to actually break the thing down and get
some meaningful results from it (which can vary from one bill to the next). Since there may be several hundred thousand records in the table calculating this on the fly everytime I want to use the data would take far too long I
think so better to run this once & be done with it.

So here's the problem....

I have a table with records of individual calls - for each one I have a
start time & date and a duration in seconds. In order to rate (re-price) the call properly I have to ascertain which if three chargebands the call falls into - and it may cross chargebands in which case part of the call will be
charged at one price and part at another. All I am trying to do is to break down the duration into the different chargebands. Once I have this then I
can re-price each call & summarise the bill using & I have that part
covered (using a rates table and queries).

So in my table I have three fields prepared (one for each chargeband) into
which I need to divide up the total duration of the call which I already
have in seconds (just stored as a number)

So what I thought I'd do would be

Open the table as a recordset
Declare some variables as follows:
Band1, Band2 & Band3 (to contain the number of seconds from a given call
that fall into each charge band)
sttime to contain the start time of the call
durn to contain the call duration in seconds

Go to the first record (call) in the table

Pull the call start time of the call from a field (which is a properly
formatted general date field including the time and the date) from the
record and put into "sttime"

Pull the total duration from a field in the table and put it into "durn"

Test which chargeband the "sttime" falls into using some case statements to determine day of week and time of day etc

Add 1 to one of three Band variables based in the result
Deduct 1 from "durn"
Add 1 second to "sttime"
then loop round & keep testing till "durn" gets to zero.

Once "Durn" gets to zero then store/save the results of Band1, Band2 & Band3 back into the table in the prepared fields that are there

Move to the next record, zero all the variables, pull the next record's
start time and duration and repeat all this till it gets to the end of the
file.

At this point for each record in the table I should have the total duration (which I had to start with) and three fields (Band 1,2 & 3) which contain
numbers of seconds which, if added together, will equal the number in the
Total duration field. For many records two of the three will be zero & the
third will contain the same number as the Total duration field (i.e. where a call just falls into one chargeband). For some records (for calls that cross from one band to another) I might have figures in two of the three fields & for a few (for very long calls) I might have numbers in all three.

If I trigger this from a button on a form it'd be nice to put a counter on
the form that would show the thing working by counting up 1 for each record it processes. I don't need to display the table as its processing & nor do I need to see the results in a form - I just want to update the table as
quickly as possible.

thanks in advance for any help with the syntax required.

Iain


Nov 13 '05 #2

P: n/a

"Larry Linson" <bo*****@localhost.not> wrote in message
news:rY******************@nwrddc01.gnilink.net...
If you'd explain what chargebands represent, it's entirely possible that
someone might be able to suggest a method somewhat more efficient that
looping second by second.

You've told us in detail one way you see _how_ to calculate it, but not a
definition of what you want to calculate. I would assume that chargebands
are time brackets, but it would really help if you would clarify.


The Chargebands are indeed time brackets

CB1 - 8am-6pm Monday to Friday
CB2 - 6pm - 8am Monday to Friday
CB3 (over-rides CB1 & 2) Midnight on Friday night (Sat morning) to Midnight
on Sunday

I suppose another way to do it would be to use Datediff() (?) if I could get
it to return an answer in seconds as a simple integer.

rgds & thanks for your help

Iain
Nov 13 '05 #3

P: n/a
"Iain Miller" wrote
The Chargebands are indeed time brackets

CB1 - 8am-6pm Monday to Friday
CB2 - 6pm - 8am Monday to Friday
CB3 (over-rides CB1 & 2) Midnight on Friday night (Sat morning) to Midnight on Sunday

I suppose another way to do it would be to use Datediff() (?) if I could get it to return an answer in seconds as a simple integer.


Have you looked at Help on DateDiff? If your starttime and stoptime are
stored as valid dates, you should be able to use it -- "s" is the interval
for seconds. If I understand, the band to which the call is assigned depends
only on the start time, so it should be simple -- if you parceled out the
number of seconds if it crossed bands, that would make it a bit more
complicated. You should be able to do the calculation in a query using
DateDiff for a calculated field. DateDiff returns a Variant of type Long
Integer, so the data type isn't likely to be a big problem.

The best way is to calculate these values in the query when/where you are
going to use them (in a form or report) rather than store redundant data in
the record. Unless, of course, the record has to be exported to some other
software that requires both the time and the band seconds.

Larry Linson
Microsoft Access MVP

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.