How Can This Produce an Error?

[gemma-the-husky]

I have 2 comboboxes to select a product no, and a unit of measurement

I have a materialprices table with unique records, keyed on productno and unitno, so i should get either one or no records returned from the following SQL

However, if i compare the values of the returned records with the search values and the tests, it shows a difference, although the difference is then showing as zero when I evaluate it.

(I found this because initially I had written the code using seek, and was trying to ensure I had located the correct record)

I have actually got round this by testing the abs difference as being greater than a minuscule value.

ie
If abs(rs!pmpprodno - cboProd)>0.01 Or abs(rs!pmpunit - cboUnit)>0.01 which does not produce an error

I could understand this if I was testing real numbers, but these are all integers - any ideas anyone?


so ignoring the dims - ignore any typos - the code compiles and executes properly - its just the equality test thats the problem

function lookupmat as boolean

    strsql = "select * from tblmatprices where [pmpprodno] = " & cboProd & _
        "  and [pmpunit] = " & cboUnit
    
    Set rs = CurrentDb.OpenRecordset(strsql)
    
    If rs.eof Then
        lookupmat = False
        rs.Close
        GoTo exitproc
    End If
   
[COLOR="Blue"]'having used the above where clause to find an item, this test now seems to produce a difference, even though the figures are the same![/COLOR]
[COLOR="Red"]    If rs!pmpprodno <> cboProd Or rs!pmpunit <> cboUnit Then[/COLOR]        MsgBox ("Unexpected  - There is a difference " & vbCrLf & _
                "cboProd = " & Format(cboProd, "###.0000000000") & _
                    "    Lookup = " & Format(rs!pmpprodno, "###.0000000000") & vbCrLf & _
                "cboUnit = " & Format(cboUnit, "###.0000000000") & _
                    "    Lookup = " & Format(rs!pmpunit, "###.0000000000") & vbCrLf)
        
[COLOR="Blue"]'and now both of these tests are showing a difference! of zero[/COLOR]
        If cboProd <> rs!pmpprodno Then MsgBox ("Prod Was different " & cboProd - rs!pmpprodno)
        If cboUnit <> rs!pmpunit Then MsgBox ("Unit Was different " & cboUnit - rs!pmpunit)
        
        lookupmat = False
        rs.Close
        GoTo exitproc
    End If

 

[Steve R.]

If I understand the problem correctly: two integers (that in theory are the same number) when subtracted from each other will (for whatever reason) leave a residual value (close to zero) which causes the equality test to fail.

Even though the numbers are integers, what about experimenting with the INT function just to see what would happen? Theoretically, I realize that his should not help and won't solve the issue of why the integers are misbehaving. But if it works, would that be a solution?

If INT(rs!pmpprodno) <> INT(cboProd) Or INT(rs!pmpunit) <> INT(cboUnit) Then

 

[gemma-the-husky]

thanks - yes, thats the idea - as i say i ended testing for a small absolute difference, and an int function should achieve the same thing - but I don't understand how or why integer subtraction can generate a difference

 

[pbaldy]

As you say, it doesn't make any sense. Can you post a sample that we can play with?

 

[Steve R.]

The only theory that I would suggest is that that the integer numbers, at the time they are being evaluated by the <> test are converted to real numbers. In the process of being converted to real numbers the "final" digits of the number string are randomly generated (see below). This is pure speculation on my part to come up with an explanation for why an obvious test is failing and may have no basis in fact.

For example, Integer 1 is converted to the real number 1.00000000000000000000000023 the "23" being some sort of conversion "error". Using the INT function might work by preventing this "conversion" from occuring. Again this is all speculation.

Did you try to use the INT function to see if you still get a residual value?

The FIX and ROUND functions could also be used to limit the number to zero decimal places. Still doesn't answer the why, but may be a potential solution.

 

[gemma-the-husky]

thanks Paul, I'll strip the dbs down, and put this sample on the site

 

[RuralGuy]

Great Gemma. I'd like to play with it as well.

 

[gemma-the-husky]

It's all stripped down now, so try this

there's only one form, and the tables and queries etc that drive it.

pick a value for the product and unit, and it will generate the difference I was talking about

Thanks


View attachment m_strip.zip

 

[Steve R.]

It appears that the combobox (cboProd) is converting your integers to real numbers.

The following works:

If (rs!pmpprodno <> int(cboProd)) Or (rs!pmpunit <> Int(cboUnit))

I also tested with:

Debug.Print "prodid: "; TypeName(DLookup("[prodid]", "[qryProdsAlphaLiveOnly]", "prodid = 16"))

The result was the expected long.

I don't know why a combobox would "convert" and integer to a real number. I hope that RuralGuy and pbaldy will have greater insight into this issue than I do.

 

[pbaldy]

I've only had a chance to glance at it, but I think the problem is that your values in the combo have become strings rather than numbers. Fairly easy to see in debug, where the value in the combo is:

"123"

and the value from the recordset is

123

If I get a chance later I'll try to see why your numeric fields are being treated as text, or you may see it first since you know what's going on. I've got to work on a payroll issue in our Vegas office.

 

[RuralGuy]

Paul is correct that the .Value property of the ComboBox is a string and the field is a LongInteger. I believe it is one of those cases where Access is coercing one DataType into another to "help" you rather than report an error. As others suggested, in this case it is probably better to do your own coercing.

 

[pbaldy]

I did a brief test while on the phone (still on this stupid conference call), and it seems that with the column width set to zero, the value becomes a string in VBA. If it's not, it's a number as it should be (tested by changing both).

As RG noted, my guess is that Access is trying to help, and as usual screwing up. Offhand I'd say you'll need to convert the combo value back to a number in your code, since I'd guess you don't want to display that column.

 

[gemma-the-husky]

i was tracing the code with a breakpoint, and noticed that the combo was being displayed as a string.

does that mean the equality operator is reporting inequality purely because the datatypes are different, but as soon as you examine it, or test it, the test value is converted to a number and the error goes away.

I don't see that it can be connected to the numeric value of the combobox itself, because presumably you can never get a fractional representation of an integral number.

Thanks for all your help anyway - I know where to look further now.

 

[gemma-the-husky]

Paul, thanks for your last note.

I had thought that the issue was due somehow to a real number repesentation and had fixed it with the commented out line ie testing the difference against a delta value - so now I know the real issue, I can modify the fix.

In practice it had only arisen during testing, as the lookup is definitely retrieving a match correctly anyway.

This issue came back to light anyway over the last couple of days. The database is a couple of years old, and I am converting it work with SQL as well as JET. In this bit, I had originally opened the backend to use the seek method, but I couldn't do that with a SQL backend, so I had to rework it, which prompted me to look again at this.

Still funny, though, as I am using the combo box to set numeric variables, and none of this is producing errors.

Thanks again

 

[RuralGuy]

Very interesting. Thanks for the update Paul.

 

[The_Doc_Man]

Comparison mis-matches are not unheard-of. Part of the problem may be that somewhere in the midst of the behind-the-scenes gyrations, you have a VARIANT field or object as an intermediary. If so, you have an apples and oranges comparison. (I'll admit I don't see it, but what the heck, it is still possible with Access.) The way I would approach this is to convert both numbers using the same format string and compare the strings.

I.e. if Format( [X], "#####.#####" ) <> Format( [Y], "#####.#####" ) then ....

Doing the compare in binary format SHOULD work but you have to remember that VBA isn't a compiler. It is a pseudo-compiler. There is no telling what the pseudo-code does with numeric comparisons. But if both variables are converted to their eventual display formats, you would know for a fact that they were different to the degree of significance used in that display format. I don't think Access would back-convert the numbers from string format.

By the way, it is exactly this technique that should be used to prevent your code from sending someone a bill for $0.00 and demanding payment "or we will forward this to a credit agency." People forget to test the displayed value before they send out bills, then have egg on their faces for sending out an unpayable and meaningless bill.

 

[pbaldy]

FYI, I've notified a buddy who is an Access MVP about this apparent bug. He was able to duplicate it and is posting it on the MVP site. I'll post back if I hear anything further.

 

[wrmosca]

Just to let you all know, I've posted these findings on the Access MVP newsgroup for feedback before reporting it as a bug. (I'm Paul's buddy...until I owe him money).

 

[RuralGuy]

Thanks for the update Bill (and Paul too). How did you find time away from the Yahoo forum? It seem to me they keep you pretty busy over there. Welcome to this forum as well.

 

[wrmosca]

Thanks, RG. My Yahoo group is only one place I lurk. But it is my #1 priority when it comes to forums and news groups. Paul's just as busy, if not more so. He moderates my group and at least one other that I know about.