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

access 2003: division operator problem

P: n/a
hello
i am not getting the correct percentage results from the following
code. the "prelimpercent" variable = 0? why would that be the case
when i try to divide numrecs by numrecs2?

any advise would be appreciated. thanks in advance - jung
************************************************** ***********************

Dim rst, rst2 As DAO.Recordset
Dim db As DAO.Database
Dim numrecs, numrecs2 As Integer
Dim percent, prelimpercent As Integer
Dim x As String

Set db = CurrentDb
'counts current records
x = Me![SQLStatement]
Set rst = db.OpenRecordset(x)
With rst
If rst.EOF = False Then
rst.MoveLast
numrecs = rst.RecordCount
Debug.Print numrecs '(19)
Else
numrecs = 0
End If
End With

'count records in qryAllOpenItems
Set rst2 = db.OpenRecordset("SELECT [Exception Tracking].*, [Loan
Tracking].[LT DB Database], [Loan Tracking].[LT Internal Number] FROM
[Loan Tracking] INNER JOIN [Exception Tracking] ON [Loan Tracking].[LT
Loan Shortname] = [Exception Tracking].[ET LN Shortname] WHERE
((((((Not [ET Closed]=-1))) AND ((Not [ET Amount]=0))) AND ((Not [ET
Erroneous Entry]=-1))))")
With rst2
If rst2.EOF = False Then
rst2.MoveLast
numrecs2 = rst2.RecordCount
Debug.Print numrecs2 '(74 records)
Else
numrecs2 = 0
End If
End With

prelimpercent = numrecs / numrecs2 '19/74
percent = prelimpercent * 100
Debug.Print percent
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Don't know if this is the solution, just saw it right-off:

Change this:

Dim rst, rst2 As DAO.Recordset
Dim db As DAO.Database
Dim numrecs, numrecs2 As Integer
Dim percent, prelimpercent As Integer

to this:

Dim rst as DAO.Recordset, rst2 As DAO.Recordset
Dim db As DAO.Database
Dim numrecs As Integer, numrecs2 As Integer
Dim percent As Integer, prelimpercent As Integer

Variables dimmed w/o their data type default to Variants.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQYahTYechKqOuFEgEQLD1QCfegG1dIdt0cioN5RuSKoWN9 yaEOcAoLZX
eYnPuUemIPGtXfXcSp6Xg4TJ
=Dukc
-----END PGP SIGNATURE-----
JMCN wrote:
hello
i am not getting the correct percentage results from the following
code. the "prelimpercent" variable = 0? why would that be the case
when i try to divide numrecs by numrecs2?

any advise would be appreciated. thanks in advance - jung
************************************************** ***********************

Dim rst, rst2 As DAO.Recordset
Dim db As DAO.Database
Dim numrecs, numrecs2 As Integer
Dim percent, prelimpercent As Integer
Dim x As String

Set db = CurrentDb
'counts current records
x = Me![SQLStatement]
Set rst = db.OpenRecordset(x)
With rst
If rst.EOF = False Then
rst.MoveLast
numrecs = rst.RecordCount
Debug.Print numrecs '(19)
Else
numrecs = 0
End If
End With

'count records in qryAllOpenItems
Set rst2 = db.OpenRecordset("SELECT [Exception Tracking].*, [Loan
Tracking].[LT DB Database], [Loan Tracking].[LT Internal Number] FROM
[Loan Tracking] INNER JOIN [Exception Tracking] ON [Loan Tracking].[LT
Loan Shortname] = [Exception Tracking].[ET LN Shortname] WHERE
((((((Not [ET Closed]=-1))) AND ((Not [ET Amount]=0))) AND ((Not [ET
Erroneous Entry]=-1))))")
With rst2
If rst2.EOF = False Then
rst2.MoveLast
numrecs2 = rst2.RecordCount
Debug.Print numrecs2 '(74 records)
Else
numrecs2 = 0
End If
End With

prelimpercent = numrecs / numrecs2 '19/74
percent = prelimpercent * 100
Debug.Print percent


Nov 13 '05 #2

P: n/a
You have declared prelimpercent as an integer -
so it will round any value less than .5 to 0.

HTH
- Turtle

"JMCN" <pi******@yahoo.fr> wrote in message
news:27**************************@posting.google.c om...
hello
i am not getting the correct percentage results from the following
code. the "prelimpercent" variable = 0? why would that be the case
when i try to divide numrecs by numrecs2?

any advise would be appreciated. thanks in advance - jung
************************************************** ***********************

Dim rst, rst2 As DAO.Recordset
Dim db As DAO.Database
Dim numrecs, numrecs2 As Integer
Dim percent, prelimpercent As Integer
Dim x As String

Set db = CurrentDb
'counts current records
x = Me![SQLStatement]
Set rst = db.OpenRecordset(x)
With rst
If rst.EOF = False Then
rst.MoveLast
numrecs = rst.RecordCount
Debug.Print numrecs '(19)
Else
numrecs = 0
End If
End With

'count records in qryAllOpenItems
Set rst2 = db.OpenRecordset("SELECT [Exception Tracking].*, [Loan
Tracking].[LT DB Database], [Loan Tracking].[LT Internal Number] FROM
[Loan Tracking] INNER JOIN [Exception Tracking] ON [Loan Tracking].[LT
Loan Shortname] = [Exception Tracking].[ET LN Shortname] WHERE
((((((Not [ET Closed]=-1))) AND ((Not [ET Amount]=0))) AND ((Not [ET
Erroneous Entry]=-1))))")
With rst2
If rst2.EOF = False Then
rst2.MoveLast
numrecs2 = rst2.RecordCount
Debug.Print numrecs2 '(74 records)
Else
numrecs2 = 0
End If
End With

prelimpercent = numrecs / numrecs2 '19/74
percent = prelimpercent * 100
Debug.Print percent

Nov 13 '05 #3

P: n/a
[re-arranged for readability, bottom posting]
JMCN wrote:
hello
i am not getting the correct percentage results from the following
code. the "prelimpercent" variable = 0? why would that be the case
when i try to divide numrecs by numrecs2?

any advise would be appreciated. thanks in advance - jung
************************************************** ***********************

Dim rst, rst2 As DAO.Recordset
Dim db As DAO.Database
Dim numrecs, numrecs2 As Integer
Dim percent, prelimpercent As Integer
Dim x As String

Set db = CurrentDb
'counts current records
x = Me![SQLStatement]
Set rst = db.OpenRecordset(x)
With rst
If rst.EOF = False Then
rst.MoveLast
numrecs = rst.RecordCount
Debug.Print numrecs '(19)
Else
numrecs = 0
End If
End With

'count records in qryAllOpenItems
Set rst2 = db.OpenRecordset("SELECT [Exception Tracking].*, [Loan
Tracking].[LT DB Database], [Loan Tracking].[LT Internal Number] FROM
[Loan Tracking] INNER JOIN [Exception Tracking] ON [Loan Tracking].[LT
Loan Shortname] = [Exception Tracking].[ET LN Shortname] WHERE
((((((Not [ET Closed]=-1))) AND ((Not [ET Amount]=0))) AND ((Not [ET
Erroneous Entry]=-1))))")
With rst2
If rst2.EOF = False Then
rst2.MoveLast
numrecs2 = rst2.RecordCount
Debug.Print numrecs2 '(74 records)
Else
numrecs2 = 0
End If
End With

prelimpercent = numrecs / numrecs2 '19/74
percent = prelimpercent * 100
Debug.Print percent

MGFoster <me@privacy.com> wrote in message news:<hj****************@newsread3.news.pas.earthl ink.net>... -----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Don't know if this is the solution, just saw it right-off:

Change this:

Dim rst, rst2 As DAO.Recordset
Dim db As DAO.Database
Dim numrecs, numrecs2 As Integer
Dim percent, prelimpercent As Integer

to this:

Dim rst as DAO.Recordset, rst2 As DAO.Recordset
Dim db As DAO.Database
Dim numrecs As Integer, numrecs2 As Integer
Dim percent As Integer, prelimpercent As Integer

Variables dimmed w/o their data type default to Variants.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQYahTYechKqOuFEgEQLD1QCfegG1dIdt0cioN5RuSKoWN9 yaEOcAoLZX
eYnPuUemIPGtXfXcSp6Xg4TJ
=Dukc
-----END PGP SIGNATURE-----


What you didn't spot is that he's storing his prelimpercent (a number
between 0 and 1) into an INTEGER - which can only be 0 or 1, and so
the result will always be either 0% or 100%, after the multiplication.

Option 1) Store the prelimpercent into a Double,

Option 2) Multiply numrecs by 100 BEFORE doing the division.

Either will work, whatever floats your boat really (although,
obviously, if you want fractional percentages, option 1 is the way to
go).

Damien
Nov 13 '05 #4

P: n/a
thank you for your advise! i realized that after writing it that i
should have declared the prelimpercent as double!

thanks again!!!! jung

Da*******************@hotmail.com (Damien) wrote in message news:<ac**************************@posting.google. com>...
[re-arranged for readability, bottom posting]
JMCN wrote:
hello
i am not getting the correct percentage results from the following
code. the "prelimpercent" variable = 0? why would that be the case
when i try to divide numrecs by numrecs2?

any advise would be appreciated. thanks in advance - jung
************************************************** ***********************

Dim rst, rst2 As DAO.Recordset
Dim db As DAO.Database
Dim numrecs, numrecs2 As Integer
Dim percent, prelimpercent As Integer
Dim x As String

Set db = CurrentDb
'counts current records
x = Me![SQLStatement]
Set rst = db.OpenRecordset(x)
With rst
If rst.EOF = False Then
rst.MoveLast
numrecs = rst.RecordCount
Debug.Print numrecs '(19)
Else
numrecs = 0
End If
End With

'count records in qryAllOpenItems
Set rst2 = db.OpenRecordset("SELECT [Exception Tracking].*, [Loan
Tracking].[LT DB Database], [Loan Tracking].[LT Internal Number] FROM
[Loan Tracking] INNER JOIN [Exception Tracking] ON [Loan Tracking].[LT
Loan Shortname] = [Exception Tracking].[ET LN Shortname] WHERE
((((((Not [ET Closed]=-1))) AND ((Not [ET Amount]=0))) AND ((Not [ET
Erroneous Entry]=-1))))")
With rst2
If rst2.EOF = False Then
rst2.MoveLast
numrecs2 = rst2.RecordCount
Debug.Print numrecs2 '(74 records)
Else
numrecs2 = 0
End If
End With

prelimpercent = numrecs / numrecs2 '19/74
percent = prelimpercent * 100
Debug.Print percent


MGFoster <me@privacy.com> wrote in message news:<hj****************@newsread3.news.pas.earthl ink.net>...
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Don't know if this is the solution, just saw it right-off:

Change this:

Dim rst, rst2 As DAO.Recordset
Dim db As DAO.Database
Dim numrecs, numrecs2 As Integer
Dim percent, prelimpercent As Integer

to this:

Dim rst as DAO.Recordset, rst2 As DAO.Recordset
Dim db As DAO.Database
Dim numrecs As Integer, numrecs2 As Integer
Dim percent As Integer, prelimpercent As Integer

Variables dimmed w/o their data type default to Variants.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQYahTYechKqOuFEgEQLD1QCfegG1dIdt0cioN5RuSKoWN9 yaEOcAoLZX
eYnPuUemIPGtXfXcSp6Xg4TJ
=Dukc
-----END PGP SIGNATURE-----


What you didn't spot is that he's storing his prelimpercent (a number
between 0 and 1) into an INTEGER - which can only be 0 or 1, and so
the result will always be either 0% or 100%, after the multiplication.

Option 1) Store the prelimpercent into a Double,

Option 2) Multiply numrecs by 100 BEFORE doing the division.

Either will work, whatever floats your boat really (although,
obviously, if you want fractional percentages, option 1 is the way to
go).

Damien

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.