Re: Calculations in SQL

Tech-Archive recommends: Fix windows errors by optimizing your registry

I think I've found the problem, but I don't know how to fix it. In the
calculation for PopCalc, I have the Population (of the county) / Population
(of the state) * allocation amount. If it divides the county Pop by the
state Pop first, it gets a fraction and sees it as zero. If it multiplies
the state Pop times the allocation amount first, the divisor is too big and
the end result is another fraction. Somehow it is losing the 'county
population divided by the state population' before it gets to the allocation
multiplication, and anything divided by zero is zero. How do I make it keep
the fraction throughout the calculation?

"Robert Morley" wrote:

Are you sure that you should be getting a PopCalc between 25 and 250? To
me, it looks like you're taking a portion of your population divided by your
entire population...that would typically yield numbers between 0 and 1.



Rob

"Todd K." <todd.kirby-delete-@xxxxxx> wrote in message
news:D111C5DB-E16B-468D-830C-E60CA116212A@xxxxxxxxxxxxxxxx
Key variable debug fine, switched to CurrentProject.Connection.Execute and
still have same problem. Population is indeed integer, but calculations
with
current data should yield a PopCalc of between $25 and $250.

"Robert Morley" wrote:

I'm not sure why Sylvain thought you were dealing with an ODBC link, but
no
matter. He was right in the sense that all your code looks fine on the
surface as far as I can see. The first thing I would try is doing
Debug.Print (or MsgBox, whichever you prefer) on some of your key
variables,
like intSumPop, and the entire INSERT string, just to make sure that you
don't have some kind of logic or command error and don't realize it.

Second, instead of DoCmd.RunSQL, try CurrentProject.Connection.Execute.
In
theory, they should produce the same results, but the CurrentProject
method
is a more direct route to the same place. The syntax is otherwise the
same:
CurrentProject.Connection.Execute "INSERT INTO..."

Third, check the data type of Population. It's possible that the math
you're doing is fine, but if population is an integer data type and the
final result is actually less than one (which seems likely, given the
calculations you're doing), it's getting rounded down to 0.

Try those first, then get back to us.


Rob

"Todd K." <todd.kirby-delete-@xxxxxx> wrote in message
news:5C0B293D-052E-410C-BA16-8101E102E9F3@xxxxxxxxxxxxxxxx
First, the value of intSumPop is not zero, every row in the table has a
value
for Population.

Second, this involves SQL on an Access Project (that's what .adp means,
right?)

"Sylvain Lafontaine" wrote:

Probably because the value of intSumPop is zero (0) and you cannot
divide
by
zero.

Second, this newsgroup is about ADP and SQL-Server and not about ODBC
linked
tables and/or SQL-Server; for which there are already other officially
assigned newsgroups.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Todd K." <todd.kirby-delete-@xxxxxx> wrote in message
news:4C6C395B-DF57-469B-A52C-03DB5E35226D@xxxxxxxxxxxxxxxx
I am trying to maintain a historical table with monthly calculations.
In
the
form where you enter the monthly total for allocating, I declare
variables
for most of the calculations:

Dim curTotal As Currency
Dim curOneFif As Currency
Dim curTwoFif As Currency
Dim intSumPop As Long
Dim intSumArea As Long
Dim intSumMiles As Long
Dim datToday As Date

curTotal = Me.RoadAidText
curOneFif = curTotal / 5
curTwoFif = curTotal * 2 / 5
intSumPop = Nz(DSum("[Population]", "TblCountyData"), 0)
intSumArea = Nz(DSum("[Area]", "TblCountyData"), 0)
intSumMiles = Nz(DSum("[Road_Miles]", "TblCountyData"), 0)
datToday = Format(Now(), "mm/dd/yyyy")

The I use a SQL statement to update the table TblHISTORY_CRA:

DoCmd.RunSQL "INSERT INTO TblHISTORY_CRA (County_Name, Check_Name,
PopCalc)
SELECT TblCountyData.County_Name, TblCountyData.Check_Name,
TblCountyData.Population /"& intSumPop &" AS PopCalc FROM
TblCountyData
WHERE
County_Name not in ('Regional','Statewide');"

The problem is, it won't calculate the TblCountyData.Population/"&
intSumPop
&" AS PopCalc - it just puts zeros in those fields. I have tried
using
a
constant like TblCountyData.Population/23 (which works) and I have
tried
using just the variable "& intSumPop & " AS PopCalc (which also
works).
Why
won't the calculation TblCountyData.Population/"& intSumPop &" AS
PopCalc
work?










.

Relevant Pages

  • Re: Calculations in SQL
    ... Debug.Print on some of your key variables, ... calculations you're doing), it's getting rounded down to 0. ... Dim curOneFif As Currency ... Dim intSumPop As Long ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Excel VBA - Compressing a Workbook
    ... Dim SH As Worksheet ... >> Currently, I run an automated daily report, that is started up by ... >> and spits out an Excel Spreadsheet based on some comparison numbers. ... >> Excel do all of the percentage calculations etc etc and just throwing out ...
    (microsoft.public.excel.programming)
  • Re: Request help with a custom date field - ? 4 Greg...
    ... Greg Maxey wrote: ... Is it due to my version of VBA or how can I fix this? ... Dim oFF As Word.FormFields ... calculations later in the form, such as with the code you've ...
    (microsoft.public.word.vba.beginners)
  • Re: Request help with a custom date field - ? 4 Greg...
    ... Greg Maxey wrote: ... Is it due to my version of VBA or how can I fix this? ... Dim oFF As Word.FormFields ... calculations later in the form, such as with the code you've provided? ...
    (microsoft.public.word.vba.beginners)
  • Re: Request help with a custom date field - ? 4 Greg...
    ... Is it due to my version of VBA or how can I fix this? ... Dim oFF As Word.FormFields ... Dim InputDate As Date ... calculations later in the form, such as with the code you've provided? ...
    (microsoft.public.word.vba.beginners)