access 2003: division operator problem

[JMCN]

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]

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

[MacDermott]

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

[Damien]

[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 <[e-mail]> 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

[JMCN]

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 <[e-mail]> 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