If Condition Issue! 2

[VBUser77]

I am trying to run the following function. I am testing two variables (whcih I have declared as public in the general declaration section): SFsum and rcount.

I want to warn the user If SFsum <> rcount. It works fine when I have SFsum and rcount both equal to 1, 2 or 4. But When both of them are equal to 3, it still thinks they are not equal and warn the user. Below is my code.

It's driving me nuts because it works fine for 1, 2 or 4but doesn't work at all when the value is 4.

Any ideas/thoughts will be highly appreciated. Thanks!

' *****************************************************

Public Function SFSummation()

Dim k As Integer

Dim ObjMyDB As Database
Dim r As Recordset
Dim r1 As Recordset

Set ObjMyDB = DBEngine.Workspaces(0).Databases(0)

Set r = ObjMyDB.OpenRecordset("Graph_tbl", DB_OPEN_DYNASET)
Set r1 = ObjMyDB.OpenRecordset("OutPut_tbl", DB_OPEN_DYNASET)


SFsum = 0
rcount = 0


r.MoveFirst
Do While Not r.EOF
SFsum = SFsum + r![Factor]
r.MoveNext
Loop

r1.MoveFirst
For k = 1 To 4
rcount = rcount + Abs(r1.Fields(1 + k) > 0)
Next k


r.Close
r1.Close
ObjMyDB.Close

Set r = Nothing
Set r1 = Nothing
Set ObjMyDB = Nothing

Debug.Print SFsum
Debug.Print rcount


If SFsum <> rcount Then
Debug.Print "Test Not Equal"
End If

End Function

***********************************************************

 

[Mike555]

Maybe...

For k = 0 To 4

 

[TheAceMan1]

How are VBUser77 . . . . .

What are the [blue]data types[/blue] of the fields?

Are they all the same?

Could be a problem if one is integer and one is say . . . single . . .

See Ya! . . . . . .

 

[Darrylles]

Hi,

From your code, you are only testing the final content of both variables - why? Your test only tests for inequality after both variables have exited the loops.

Right-click the line: "r.MoveFirst" and select 'Toggle->Breakpoint'.

Run the code, it'll stop at r.movefirst, step thru' each iteration of each loop noting all results (click F8 to step thru' the code).

I think that your logic is incorrect.

Also, I would use this:
[tt]
r.MoveFirst
r1.MoveFirst
k = 1

Do While Not r.EOF
SFsum = SFsum + r![Factor]
rcount = rcount + Abs(r1.Fields(1 + k) > 0)
k=k+1
r.MoveNext
loop
[/tt]
N.B. You do agree that you only ever have 4 records - yes?
In this case, use this code (and toggle breakpoint on the 'Do While..' statement) - this way, you can look at each variable value by hovering your mouse pointer over the variable - comparing SFsum and rcount as they change together.

ATB

Darrylle









Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[RoyVidar]

Seems the logic here is to loop the Graph_tbl, and per each row, sum the Factor field into the variable SFsum, and for the OutPut_tbl to count the number of fields being checked, or having a ABS(numeric) value greater than 0 in the ordinal position 2 through 5 in the first (only?) record of the table.

Hint, hint;-) to allow us a bit easier access to the logic, AND perhaps more important, allow you to understand the logic yourself, when you're coming back to this code in a year or two - use a naming convention both giving a bit of information on what variables are intended to be used for, their datatype, and perhaps also their scope. If both these where declared as long, the declaration would probably look something like this found in my code:
[tt]
dim m_lngSFSum as long
dim m_lngRCount as long[/tt]

See - easy to spot both scope and datatype wherever it's used...

When using only one recordset variable in a routine, it is called rs in my code, when using more than one, they are named after their usage (rsEmployee, rsDepartment...)

Comments?

All this aside, how you've declared those variables are probably what is causing this, as it seems to be result of floating point comparision. If either of your variables are single or double, they may not store 3, but the closest approximation to 3 not being entirely 3, making a comparision evaluate to false.

If the comparision would be between "integer" values, there should be some functions to apply, don't know but perhaps the int or fix functions, or perhaps round? Or you could have a go at comparing a range, for instance like this

[tt]If ABS(FSsum - rcount) < 0.001 Then
' close 'nough to be called equal
End If[/tt]

Some hints, tips, explanations - thread222-894973, thread709-864370.

Basically, non integer datatypes (floating point/approximates) aren't what you would like to compare with eachother. Rather use integer datatypes, or scaled integers, like for instance the data type Currency. It seems at least the rcount variable, since it's only summing "checked" values, could easily be an integer/long. But what about SFsum?

Roy-Vidar