Calculations in SQL

[Todd K.]

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?

 

[Sylvain Lafontaine]

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.

 

[Todd K.]

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?)

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)

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?

 

[Robert Morley]

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

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?)

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)

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?

 

[Sylvain Lafontaine]

Ah, you are making multiple uses of the DSum() functions instead of using a
stored procedure for your calculation, hence my thought that you were using
ODBC linked tables instead of ADP. You should use a SP for the whole
computation if you want a good performance; otherwise, you are making
unnecessary round trips to the sql-server.

You are using things like « intSumPop = Nz(DSum("[Population]",
"TblCountyData"), 0) » so there is clearly a possibility that the value of
intSumPop might be zero.

Display the sql string in a message box to see where the error is and if you
find none, try replacing DoCmd.RunSQL with CurrentProject.Connection.Execute
:

http://www.activeserverpages.ru/ADO/dameth02_1.htm

If I remember correctly, it's also recommended to use
CurrentProject.Connection.Execute instead of DoCmd.RunSQL when using ADP for
reasons that I forgot a long time ago.

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

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?)

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)

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?

 

[Todd K.]

I displayed the SQL in a message box, it looks correct. I also switched to
CurrentProject.Connection.Execute and am still having the same problem. I
would love advice on how to do this all as a Stored Procedure, but I have
been unlucky in doing advanced calculations in stored procedures.

Ah, you are making multiple uses of the DSum() functions instead of using a
stored procedure for your calculation, hence my thought that you were using
ODBC linked tables instead of ADP. You should use a SP for the whole
computation if you want a good performance; otherwise, you are making
unnecessary round trips to the sql-server.

You are using things like « intSumPop = Nz(DSum("[Population]",
"TblCountyData"), 0) » so there is clearly a possibility that the value of
intSumPop might be zero.

Display the sql string in a message box to see where the error is and if you
find none, try replacing DoCmd.RunSQL with CurrentProject.Connection.Execute
:

http://www.activeserverpages.ru/ADO/dameth02_1.htm

If I remember correctly, it's also recommended to use
CurrentProject.Connection.Execute instead of DoCmd.RunSQL when using ADP for
reasons that I forgot a long time ago.

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

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?)

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)


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?

 

[Todd K.]

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.

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

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?)

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)


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?

 

[Robert Morley]

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

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.

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

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?)

:

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)


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?

 

[Sylvain Lafontaine]

What result are you getting if you directly set the variable intSumPop to
the value of 23 before calling DoCmd.RunSQL and what type of field is the
column [Population] ?

Also, it's strange that VBA didn't put a space between the caracters "& .
If this the original sql string or you have edited it? Can you show use the
final result of the sql string?

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

I displayed the SQL in a message box, it looks correct. I also switched to
CurrentProject.Connection.Execute and am still having the same problem. I
would love advice on how to do this all as a Stored Procedure, but I have
been unlucky in doing advanced calculations in stored procedures.

Ah, you are making multiple uses of the DSum() functions instead of using
a
stored procedure for your calculation, hence my thought that you were
using
ODBC linked tables instead of ADP. You should use a SP for the whole
computation if you want a good performance; otherwise, you are making
unnecessary round trips to the sql-server.

You are using things like « intSumPop = Nz(DSum("[Population]",
"TblCountyData"), 0) » so there is clearly a possibility that the value
of
intSumPop might be zero.

Display the sql string in a message box to see where the error is and if
you
find none, try replacing DoCmd.RunSQL with
CurrentProject.Connection.Execute
:

http://www.activeserverpages.ru/ADO/dameth02_1.htm

If I remember correctly, it's also recommended to use
CurrentProject.Connection.Execute instead of DoCmd.RunSQL when using ADP
for
reasons that I forgot a long time ago.

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

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?)

:

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)


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?

 

[Sylvain Lafontaine]

I forgot to ask what type of field is the target column PopCalc?

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

What result are you getting if you directly set the variable intSumPop to
the value of 23 before calling DoCmd.RunSQL and what type of field is the
column [Population] ?

Also, it's strange that VBA didn't put a space between the caracters "& .
If this the original sql string or you have edited it? Can you show use
the final result of the sql string?

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

I displayed the SQL in a message box, it looks correct. I also switched
to
CurrentProject.Connection.Execute and am still having the same problem.
I
would love advice on how to do this all as a Stored Procedure, but I have
been unlucky in doing advanced calculations in stored procedures.

Ah, you are making multiple uses of the DSum() functions instead of
using a
stored procedure for your calculation, hence my thought that you were
using
ODBC linked tables instead of ADP. You should use a SP for the whole
computation if you want a good performance; otherwise, you are making
unnecessary round trips to the sql-server.

You are using things like « intSumPop = Nz(DSum("[Population]",
"TblCountyData"), 0) » so there is clearly a possibility that the value
of
intSumPop might be zero.

Display the sql string in a message box to see where the error is and if
you
find none, try replacing DoCmd.RunSQL with
CurrentProject.Connection.Execute
:

http://www.activeserverpages.ru/ADO/dameth02_1.htm

If I remember correctly, it's also recommended to use
CurrentProject.Connection.Execute instead of DoCmd.RunSQL when using ADP
for
reasons that I forgot a long time ago.

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


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?)

:

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)


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?

 

[Todd K.]

1) When I enter "23", I get a numeric result that varies depending on the
input number for the calculation
2) [Population] is formatted as integer
3) [PopCalc] is formatted as money
4) I did edit the formula I gave you, because it is actually much larger:
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
Dim strSQL As String

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")

CurrentProject.Connection.Execute "INSERT INTO TblHISTORY_CRA
(County_Name,Check_Name,Population,Area,Road_Miles,Address,City," & _
"Zip,Equal,PopCalc,AreaCalc,MilesCalc,Date,UFIR,Ethics,CO_OP,Release_Date,Bank_Name,Bank_Address,Bank_City,Bank_State,Bank_Zip,ACH_Routing,ACH_Check_ID,ACH_Account,ACH_Account_Type,E_Mail,Comments)
SELECT
TblCountyData.County_Name,TblCountyData.Check_Name,TblCountyData.Population,TblCountyData.Area," & _
"TblCountyData.Road_Miles,TblCountyData.Address,TblCountyData.City,TblCountyData.Zip,"
& curOneFif & "/120 AS Equal,TblCountyData.Population/" & intSumPop *
curOneFif & " AS PopCalc,TblCountyData.Area/" & intSumArea * curTwoFif & " AS
AreaCalc,0 AS MilesCalc,'1/1/01' AS Date,0 AS UFIR,0 AS Ethics,0 AS
CO_OP,'1/1/01' AS
Release_Date,TblAccounts_County.Bank_Name,TblAccounts_County.Bank_Address,TblAccounts_County.Bank_City,TblAccounts_County.Bank_State,TblAccounts_County.Bank_Zip," & _
"TblAccounts_County.ACH_Routing,TblAccounts_County.ACH_Check_ID,TblAccounts_County.ACH_Account,TblAccounts_County.ACH_Account_Type,'A'
AS E_Mail,'County Road Aid - " & datToday & "' AS Comments FROM TblCountyData
LEFT OUTER JOIN TblAccounts_County ON TblCountyData.County_Name =
TblAccounts_County.County_Name WHERE TblCountyData.County_Name not in
('Regional','Statewide') AND Acct_Fund = 'County Road Aid';"

.... and the string I get outputted is:

INSERT INTO TblHISTORY_CRA
(County_Name,Check_Name,Population,Area,Road_Miles,Address,City,Zip,Equal,PopCalc,AreaCalc,MilesCalc,Date,UFIR,Ethics,CO_OP,Release_Date,Bank_Name,Bank_Address,Bank_City,Bank_State,Bank_Zip,ACH_Routing,ACH_Check_ID,ACH_Account,ACH_Account_Type,E_Mail,Comments)
SELECT
TblCountyData.County_Name,TblCountyData.Check_Name,TblCountyData.Population,TblCountyData.Area,TblCountyData.Road_Miles,TblCountyData.Address,TblCountyData.City,TblCountyData.Zip,200000/120
AS Equal,TblCountyData.Population/808457000000 AS
PopCalc,TblCountyData.Area/15746800000 AS AreaCalc,0 AS MilesCalc,'1/1/01' AS
Date,0 AS UFIR,0 AS Ethics,0 AS CO_OP,'1/1/01' AS
Release_Date,TblAccounts_County.Bank_Name,TblAccounts_County.Bank_Address,TblAccounts_County.Bank_City,TblAccounts_County.Bank_State,TblAccounts_County.Bank_Zip,TblAccounts_County.ACH_Routing,TblAccounts_County.ACH_Check_ID,TblAccounts_County.ACH_Account,TblAccounts_County.ACH_Account_Type,'A'
AS E_Mail,'County Road Aid - 2/23/2007' AS Comments FROM TblCountyData LEFT
OUTER JOIN TblAccounts_County ON TblCountyData.County_Name =
TblAccounts_County.County_Name WHERE TblCountyData.County_Name not in
('Regional','Statewide') AND Acct_Fund = 'County Road Aid';

What result are you getting if you directly set the variable intSumPop to
the value of 23 before calling DoCmd.RunSQL and what type of field is the
column [Population] ?

Also, it's strange that VBA didn't put a space between the caracters "& .
If this the original sql string or you have edited it? Can you show use the
final result of the sql string?

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

I displayed the SQL in a message box, it looks correct. I also switched to
CurrentProject.Connection.Execute and am still having the same problem. I
would love advice on how to do this all as a Stored Procedure, but I have
been unlucky in doing advanced calculations in stored procedures.

Ah, you are making multiple uses of the DSum() functions instead of using
a
stored procedure for your calculation, hence my thought that you were
using
ODBC linked tables instead of ADP. You should use a SP for the whole
computation if you want a good performance; otherwise, you are making
unnecessary round trips to the sql-server.

You are using things like « intSumPop = Nz(DSum("[Population]",
"TblCountyData"), 0) » so there is clearly a possibility that the value
of
intSumPop might be zero.

Display the sql string in a message box to see where the error is and if
you
find none, try replacing DoCmd.RunSQL with
CurrentProject.Connection.Execute
:

http://www.activeserverpages.ru/ADO/dameth02_1.htm

If I remember correctly, it's also recommended to use
CurrentProject.Connection.Execute instead of DoCmd.RunSQL when using ADP
for
reasons that I forgot a long time ago.

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


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?)

:

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)


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?

 

[Todd K.]

See my reply to Sylvain for the whole formula, but let's say I enter
$1,000,000 as the total to be allocated. Most of the counties have a
population of 20,000 or so and the total is around 2,400,000. The actual
calculation (depending on the county) divides 20,000 by 2,400,000 (which is
..0083333) multiplied by 1/5 of $1,000,000 (which is 200,000) to equal
$1,666.67 - which is the number I expect to get.

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

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.

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

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?)

:

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)


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?

 

[Todd K.]

I think I see the problem in the SQL output. The intSumPop shouldn't be
anywhere near 808,457,000,000. That would give a final output of less than 1
for sure.

1) When I enter "23", I get a numeric result that varies depending on the
input number for the calculation
2) [Population] is formatted as integer
3) [PopCalc] is formatted as money
4) I did edit the formula I gave you, because it is actually much larger:
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
Dim strSQL As String

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")

CurrentProject.Connection.Execute "INSERT INTO TblHISTORY_CRA
(County_Name,Check_Name,Population,Area,Road_Miles,Address,City," & _
"Zip,Equal,PopCalc,AreaCalc,MilesCalc,Date,UFIR,Ethics,CO_OP,Release_Date,Bank_Name,Bank_Address,Bank_City,Bank_State,Bank_Zip,ACH_Routing,ACH_Check_ID,ACH_Account,ACH_Account_Type,E_Mail,Comments)
SELECT
TblCountyData.County_Name,TblCountyData.Check_Name,TblCountyData.Population,TblCountyData.Area," & _
"TblCountyData.Road_Miles,TblCountyData.Address,TblCountyData.City,TblCountyData.Zip,"
& curOneFif & "/120 AS Equal,TblCountyData.Population/" & intSumPop *
curOneFif & " AS PopCalc,TblCountyData.Area/" & intSumArea * curTwoFif & " AS
AreaCalc,0 AS MilesCalc,'1/1/01' AS Date,0 AS UFIR,0 AS Ethics,0 AS
CO_OP,'1/1/01' AS
Release_Date,TblAccounts_County.Bank_Name,TblAccounts_County.Bank_Address,TblAccounts_County.Bank_City,TblAccounts_County.Bank_State,TblAccounts_County.Bank_Zip," & _
"TblAccounts_County.ACH_Routing,TblAccounts_County.ACH_Check_ID,TblAccounts_County.ACH_Account,TblAccounts_County.ACH_Account_Type,'A'
AS E_Mail,'County Road Aid - " & datToday & "' AS Comments FROM TblCountyData
LEFT OUTER JOIN TblAccounts_County ON TblCountyData.County_Name =
TblAccounts_County.County_Name WHERE TblCountyData.County_Name not in
('Regional','Statewide') AND Acct_Fund = 'County Road Aid';"

... and the string I get outputted is:

INSERT INTO TblHISTORY_CRA
(County_Name,Check_Name,Population,Area,Road_Miles,Address,City,Zip,Equal,PopCalc,AreaCalc,MilesCalc,Date,UFIR,Ethics,CO_OP,Release_Date,Bank_Name,Bank_Address,Bank_City,Bank_State,Bank_Zip,ACH_Routing,ACH_Check_ID,ACH_Account,ACH_Account_Type,E_Mail,Comments)
SELECT
TblCountyData.County_Name,TblCountyData.Check_Name,TblCountyData.Population,TblCountyData.Area,TblCountyData.Road_Miles,TblCountyData.Address,TblCountyData.City,TblCountyData.Zip,200000/120
AS Equal,TblCountyData.Population/808457000000 AS
PopCalc,TblCountyData.Area/15746800000 AS AreaCalc,0 AS MilesCalc,'1/1/01' AS
Date,0 AS UFIR,0 AS Ethics,0 AS CO_OP,'1/1/01' AS
Release_Date,TblAccounts_County.Bank_Name,TblAccounts_County.Bank_Address,TblAccounts_County.Bank_City,TblAccounts_County.Bank_State,TblAccounts_County.Bank_Zip,TblAccounts_County.ACH_Routing,TblAccounts_County.ACH_Check_ID,TblAccounts_County.ACH_Account,TblAccounts_County.ACH_Account_Type,'A'
AS E_Mail,'County Road Aid - 2/23/2007' AS Comments FROM TblCountyData LEFT
OUTER JOIN TblAccounts_County ON TblCountyData.County_Name =
TblAccounts_County.County_Name WHERE TblCountyData.County_Name not in
('Regional','Statewide') AND Acct_Fund = 'County Road Aid';

What result are you getting if you directly set the variable intSumPop to
the value of 23 before calling DoCmd.RunSQL and what type of field is the
column [Population] ?

Also, it's strange that VBA didn't put a space between the caracters "& .
If this the original sql string or you have edited it? Can you show use the
final result of the sql string?

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

I displayed the SQL in a message box, it looks correct. I also switched to
CurrentProject.Connection.Execute and am still having the same problem. I
would love advice on how to do this all as a Stored Procedure, but I have
been unlucky in doing advanced calculations in stored procedures.

:

Ah, you are making multiple uses of the DSum() functions instead of using
a
stored procedure for your calculation, hence my thought that you were
using
ODBC linked tables instead of ADP. You should use a SP for the whole
computation if you want a good performance; otherwise, you are making
unnecessary round trips to the sql-server.

You are using things like « intSumPop = Nz(DSum("[Population]",
"TblCountyData"), 0) » so there is clearly a possibility that the value
of
intSumPop might be zero.

Display the sql string in a message box to see where the error is and if
you
find none, try replacing DoCmd.RunSQL with
CurrentProject.Connection.Execute
:

http://www.activeserverpages.ru/ADO/dameth02_1.htm

If I remember correctly, it's also recommended to use
CurrentProject.Connection.Execute instead of DoCmd.RunSQL when using ADP
for
reasons that I forgot a long time ago.

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


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?)

:

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)


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?

 

[Todd K.]

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?

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

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.

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

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?)

:

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)


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?

 

[Todd K.]

SUCCESS! I had to change the [Population] datatype from int to numeric, and
give it a scale of 20 and precision of 8, but it works now.

Thanks for your help.

I forgot to ask what type of field is the target column PopCalc?

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

What result are you getting if you directly set the variable intSumPop to
the value of 23 before calling DoCmd.RunSQL and what type of field is the
column [Population] ?

Also, it's strange that VBA didn't put a space between the caracters "& .
If this the original sql string or you have edited it? Can you show use
the final result of the sql string?

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

I displayed the SQL in a message box, it looks correct. I also switched
to
CurrentProject.Connection.Execute and am still having the same problem.
I
would love advice on how to do this all as a Stored Procedure, but I have
been unlucky in doing advanced calculations in stored procedures.

:

Ah, you are making multiple uses of the DSum() functions instead of
using a
stored procedure for your calculation, hence my thought that you were
using
ODBC linked tables instead of ADP. You should use a SP for the whole
computation if you want a good performance; otherwise, you are making
unnecessary round trips to the sql-server.

You are using things like « intSumPop = Nz(DSum("[Population]",
"TblCountyData"), 0) » so there is clearly a possibility that the value
of
intSumPop might be zero.

Display the sql string in a message box to see where the error is and if
you
find none, try replacing DoCmd.RunSQL with
CurrentProject.Connection.Execute
:

http://www.activeserverpages.ru/ADO/dameth02_1.htm

If I remember correctly, it's also recommended to use
CurrentProject.Connection.Execute instead of DoCmd.RunSQL when using ADP
for
reasons that I forgot a long time ago.

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


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?)

:

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)


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?

 

[Todd K.]

SUCCESS! I had to change the [Population] datatype from int to numeric, and
give it a scale of 20 and precision of 8, but it works now.

Thanks for your help.

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

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.

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

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?)

:

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)


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?

 

[Robert Morley]

Glad to hear you figured it out. In the edited version you sent initially,
the multiplier was left out, which made the population calculation wrong (or
at least incomplete). Now that I see the full calculation, it makes more
sense. And yes, as you figured out, the answer to your problem is to cast
it to one of the floating-point types.


Rob

I think I see the problem in the SQL output. The intSumPop shouldn't be
anywhere near 808,457,000,000. That would give a final output of less
than 1
for sure.

1) When I enter "23", I get a numeric result that varies depending on the
input number for the calculation
2) [Population] is formatted as integer
3) [PopCalc] is formatted as money
4) I did edit the formula I gave you, because it is actually much larger:
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
Dim strSQL As String

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")

CurrentProject.Connection.Execute "INSERT INTO TblHISTORY_CRA
(County_Name,Check_Name,Population,Area,Road_Miles,Address,City," & _
"Zip,Equal,PopCalc,AreaCalc,MilesCalc,Date,UFIR,Ethics,CO_OP,Release_Date,Bank_Name,Bank_Address,Bank_City,Bank_State,Bank_Zip,ACH_Routing,ACH_Check_ID,ACH_Account,ACH_Account_Type,E_Mail,Comments)
SELECT
TblCountyData.County_Name,TblCountyData.Check_Name,TblCountyData.Population,TblCountyData.Area,"
& _
"TblCountyData.Road_Miles,TblCountyData.Address,TblCountyData.City,TblCountyData.Zip,"
& curOneFif & "/120 AS Equal,TblCountyData.Population/" & intSumPop *
curOneFif & " AS PopCalc,TblCountyData.Area/" & intSumArea * curTwoFif &
" AS
AreaCalc,0 AS MilesCalc,'1/1/01' AS Date,0 AS UFIR,0 AS Ethics,0 AS
CO_OP,'1/1/01' AS
Release_Date,TblAccounts_County.Bank_Name,TblAccounts_County.Bank_Address,TblAccounts_County.Bank_City,TblAccounts_County.Bank_State,TblAccounts_County.Bank_Zip,"
& _
"TblAccounts_County.ACH_Routing,TblAccounts_County.ACH_Check_ID,TblAccounts_County.ACH_Account,TblAccounts_County.ACH_Account_Type,'A'
AS E_Mail,'County Road Aid - " & datToday & "' AS Comments FROM
TblCountyData
LEFT OUTER JOIN TblAccounts_County ON TblCountyData.County_Name =
TblAccounts_County.County_Name WHERE TblCountyData.County_Name not in
('Regional','Statewide') AND Acct_Fund = 'County Road Aid';"

... and the string I get outputted is:

INSERT INTO TblHISTORY_CRA
(County_Name,Check_Name,Population,Area,Road_Miles,Address,City,Zip,Equal,PopCalc,AreaCalc,MilesCalc,Date,UFIR,Ethics,CO_OP,Release_Date,Bank_Name,Bank_Address,Bank_City,Bank_State,Bank_Zip,ACH_Routing,ACH_Check_ID,ACH_Account,ACH_Account_Type,E_Mail,Comments)
SELECT
TblCountyData.County_Name,TblCountyData.Check_Name,TblCountyData.Population,TblCountyData.Area,TblCountyData.Road_Miles,TblCountyData.Address,TblCountyData.City,TblCountyData.Zip,200000/120
AS Equal,TblCountyData.Population/808457000000 AS
PopCalc,TblCountyData.Area/15746800000 AS AreaCalc,0 AS
MilesCalc,'1/1/01' AS
Date,0 AS UFIR,0 AS Ethics,0 AS CO_OP,'1/1/01' AS
Release_Date,TblAccounts_County.Bank_Name,TblAccounts_County.Bank_Address,TblAccounts_County.Bank_City,TblAccounts_County.Bank_State,TblAccounts_County.Bank_Zip,TblAccounts_County.ACH_Routing,TblAccounts_County.ACH_Check_ID,TblAccounts_County.ACH_Account,TblAccounts_County.ACH_Account_Type,'A'
AS E_Mail,'County Road Aid - 2/23/2007' AS Comments FROM TblCountyData
LEFT
OUTER JOIN TblAccounts_County ON TblCountyData.County_Name =
TblAccounts_County.County_Name WHERE TblCountyData.County_Name not in
('Regional','Statewide') AND Acct_Fund = 'County Road Aid';

What result are you getting if you directly set the variable intSumPop
to
the value of 23 before calling DoCmd.RunSQL and what type of field is
the
column [Population] ?

Also, it's strange that VBA didn't put a space between the caracters "&
.
If this the original sql string or you have edited it? Can you show
use the
final result of the sql string?

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


I displayed the SQL in a message box, it looks correct. I also
switched to
CurrentProject.Connection.Execute and am still having the same
problem. I
would love advice on how to do this all as a Stored Procedure, but I
have
been unlucky in doing advanced calculations in stored procedures.

:

Ah, you are making multiple uses of the DSum() functions instead of
using
a
stored procedure for your calculation, hence my thought that you
were
using
ODBC linked tables instead of ADP. You should use a SP for the
whole
computation if you want a good performance; otherwise, you are
making
unnecessary round trips to the sql-server.

You are using things like « intSumPop = Nz(DSum("[Population]",
"TblCountyData"), 0) » so there is clearly a possibility that the
value
of
intSumPop might be zero.

Display the sql string in a message box to see where the error is
and if
you
find none, try replacing DoCmd.RunSQL with
CurrentProject.Connection.Execute
:

http://www.activeserverpages.ru/ADO/dameth02_1.htm

If I remember correctly, it's also recommended to use
CurrentProject.Connection.Execute instead of DoCmd.RunSQL when using
ADP
for
reasons that I forgot a long time ago.

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


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?)

:

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)


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?