Number comparison help

[kgriff]

I've attached a piece of my code that I'm hoping someone can help with. I'm running Access 2010.
What I'm trying to do is this:
I have a table listing sizes
4
3
2
1
.9
.8
.7
.6
.5
.4
.3
.2
.1

The array gbArray() in the code has been assigned the values from the table. The array is then sorted in descending order. I have a form with a target number (i.e. 1.1), which is assigned to the variable "target".
I want to sum each combination of numbers from the array and return combinations that equal the target value.
My problem is, for example, if 1.1 is my target value, when gbArray(one)=1 and gbArray(two)=.1, Access does not recognize that 1+.1=1.1. However, when gbArray(one)=.1 and gbArray(two)=1, then Access can recognize that .1+1=1.1. This doesn't make any sense to me.
The reason for the six decimal rounding of gbArray() is because that's the only way I could get any remotely valid math. Without this, I would get things like 1+1=1.999999995. I've regularly had math issues like this in Excel and rounding has usually solved the problem.
Help, please.



Code:

DIM gbArray() As Single, target As Single


For one = 1 To UBound(gbArray)
If gbArray(one) > target Then GoTo nextone
For two = 1 To UBound(gbArray)
If two >= one Then GoTo skip_stepone
If Round(gbArray(one), 6) + Round(gbArray(two), 6) = Round(target, 6) Then
Debug.Print gbArray(one) & "; " & gbArray(two)
done = 1
End If
skip_stepone:
Next two
nextone:
Next one
If done = 1 Then Exit Sub

[June7]

You have declared Single data type. Try Double instead.

[kgriff]

Double didn't fix the issue. Now instead of results such as:
0.5; 0.6
0.45; 0.65
0.4; 0.7
etc.

I get (copied directly from the Immediate window):
0.5; 0.600000023841858
0.449999988079071; 0.649999976158142
0.400000005960464; 0.699999988079071
0.349999994039536; 0.75
0.300000011920929; 0.800000011920929
0.25; 0.850000023841858
0.200000002980232; 0.899999976158142
0.100000001490116; 1

What I'm trying to get to is to get the biggest number first, because that is the first number used as gbArray(one). For example, my result should be:
1; 0.1
0.9; 0.2
etc.

[crismroman]

Establish the number of decimals that you are using.
Use Round function with number of decimals setted everywhere - even you are comparing or print the result. Ex: instead of If gbArray(one) > target if you will use 2 decimals then replace with: if round(gbarray(one),2)> round(target,2)

[kgriff]

Unfortunately, that also does not solve the problem.
Anyone with other ideas?

[June7]

Did you declare Option Base 1 in the module header?

Step debug. The issue is with: If two >= one Then GoTo skip_stepone

I commented it and also removed all the Round functions. This is the output:
1; 0.1
0.9; 0.2
0.8; 0.3
0.7; 0.4
0.6; 0.5
0.5; 0.6
0.4; 0.7
0.3; 0.8
0.2; 0.9
0.1; 1

If you want to exclude the mirrored pairs, use:

For one = 1 To UBound(gbArray)
    For two = 1 To UBound(gbArray)
        If gbArray(one) + gbArray(two) = Target And gbArray(one) > gbArray(two) Then
            Debug.Print gbArray(one) & "; " & gbArray(two)
        End If
    Next
Next

[kgriff]

Thank you, Oh Exalted One. (Hope you don't mind the honorific) This did the trick, though you already knew that.
I did not declare Option Base 1, as this is a Sub Procedure, not a module. Do you think this will cause other problems?

[June7]

Yes, array indexes default to start with 0 unless you declare Option Base 1 in the header of the module that holds the procedure (or use the To clause in declaring an array). Your code references first array element as index 1, not 0, in the For Next loops. The first value in your array was 'skipped over' and ignored.

Declaring a single dimension array with To clause: Dim gbArray(1 To 13) As Single

With Base 0 the LBound is 0 and UBound is 12 (0 to 12 for 13 elements). With Base 1 the LBound is 1 and UBound is 13 (1 to 13 for 13 elements).

http://msdn.microsoft.com/en-us/libr...(v=vs.60).aspx

[kgriff]

I understand the issue. I addressed this problem thus:
The table can have a varying number of entries, typically it will be somewhere between 25 and 250, but could be anything.
I initially declare the arrays
Dim gbArray() as Single
...
A bit later I count the number of records in the table (variable reccount)
Then:
redim gbarray(1 to reccount) as single

Finally, I populate the array with all of the values from the table.

I will still declare Option Base 1, but unless there's a good reason not to, I would intend to leave the redim, as it specifically defines the size of the array and, as I was taught years ago, I try to program as much as possible to conserve resources and preserve speed of execution.

This was the cleanest way I could come up with to do this, but I'm definitely open to any alternatives. Always something to learn.

[June7]

Redim is appropriate.

Use just a DCount on the table or open a recordset?

[kgriff]

Open recordset.
I would use DCount, but I open the recordset in order to assign each value to the array.
Or are you going to tell me there's a better way to do this? Here's my code snippet for populating the array.

rs.MoveFirst
For x = 1 To reccount
gbArray(x) = rs![Size]
rs.MoveNext
Next
rs.Close

As you can probably tell, I'm not a terribly advanced VB guy. Everything I have learned has been self taught. When I need to do something, I figure out how. I'm sure there are many things I could do better. Each project makes me reevaluate past projects and I always find something I did last time that, from a new perspective, was not very efficient.

[June7]

Looks like just what I would do. I was only asking because you said you did a count on the table. So I was curious.

I was lucky we had a paid consultant help mentor me when I was assigned to finish development of our lab database 2 years into it. After about 6 months I quit asking him questions. That project was a 4 year development effort and implemented 3 years ago. That means I have only about 5 years of Access/VBA. So you see I am no expert either. I had a couple of computer classes (Basic and Fortran programming) behind me and that was it. It helps that I like doing it.

[kgriff]

I learned programming on an Apple II, when they were new. I did an old Star Office database for inventory tracking something like 20 years ago, or so. Then nothing for a while. Probably 15 years ago I built a calibration database, I think that was in Access 2.0. More recently, I've spent the past few years developing Access "applications" to interact with a purchased calibration database. My applications fill in some of the gaps left open by the purchased software.
This particular problem is for a "fun" project. This one is just something I'm working on at home that seemed like fun.
Like you, I also like doing this, so it's easy (sometimes too easy) to spend time at it.

[June7]

Dating ourselves are we? I seem to remember Star software. And owned a slightly used Apple IIe or something when my kid was 8. Wow, has it been 20 years!! Was glad to contribute to your effort. Happy Programming!