500 > 465 is false!

[mgpa]

Environment: Acc2k3 (SP2), SQL2000 MSDE (SQ4) and XP SP2

I have a simple table displayed on a subform. With 3 fields, one is derived from a combo and 2 text fields (currency) values, called PremiumRef, Claimed and Offered.

If I select a Premium for the PremiumRef combo (3 columns - a unique identifier, text for the user and a currency amount). The currency amount is for my premium is £465. I then enter £500 for Claimed and £100 for Offered.

In the AfterUpdate clauses for each prompt I have validation on each field, most of it calls various common routines (e.g Offered must be <= Claimed, etc).

The problem I have is that "Me.Claimed > Me.PremiumRef.Column(2)" is false.

MsgBox that tells me:

Me.Claimed = 500
Me.Offered = 100
Me.PremiumRef.Column(2) = 465
(Me.Claimed > Me.PremiumRef.Column(2)) = false

The last line is telling me that 500 > 465 is false!!

Given that the basic values are correct, it can not be an issue with "Me.Refresh", "Me.Requery", etc. as it is pointing to the correct record.

Any ideas where this is going wrong?

TIA
Marcus.

 

[ColinEssex]

If Me.Claimed > Me.PremiumRef.Column(2) Then

MsgBox "True"

Else

MsgBox "False"

End If

 

[mgpa]

Thanks for your prompt reply, but that's how I found the problem as my next code is:

If Me.Claimed > Me.PremiumRef.Column(2) And Me.Claimed > Me.Offered Then
' do something
' this code never runs
ElseIf Me.Claimed <= Me.PremiumRef.Column(2) And Me.Claimed > Me.Offered Then
' do something else
' this seams to run most times
ElseIf ....
EndIf

Incidentally, my code prevents all 3 from being NULL.

 

[Rich]

Do you have three columns in your combo, because you're referring to the third column in your code?

 

[mgpa]

Thanks for your prompt, but as I said it has 3 columns.

Does anyone have any ideas? This is doing my nut!

 

[mgpa]

I have managed to determine the problem. It appears to be an issue with Access casting the data from SQL Server indeterminately, specifically the columns in a combo box (incidentally all affected fields are declared within SQL Server as a "money" type).

Me.Claimed > Me.PremiumRef.Column(2) does not produce the correct result.

However, Me.Claimed - Me.PremiumRef.Column(2) > 0 does.

Also, consider
Dim curCap as Currency
curCap = Me.PremiumRef.Column(2)

The comparison Me.Claimed > curCap then works as desired.

Thanks for any input and I hope that the above helps anyone else.