Weird Microsoft Access Error

Hello everyone. I have a Microsoft Access form that is behaving in the strangest manner. I set up a macro as the Before_Update event of an unbound control so as to validate the quantity (of an item) being sold. One part of the validation is to ensure that the quantity being sold is not greater than the available quantity of the item. However I notice the strangest thing:

If the AvailableQty is say: 45  ,   I cannot enter '5' as the quantity sold. For some reason Access is evaluating only the first digit of the AvailableQty. So as 5 is greater than 4, I get a false negative error stating that the Qty Sold cannot be higher then the AvailableQty. However, if I enter any number of which its first digit is lower than 4, it accepts the number. In this light, it will accept the following numbers: 1 to 4, 10 to 45.

In other words, if the Available Qty is 500, it won't accept the numbers 60 to 99 because their first digit is greater than the 5 in 500.

I am running Office Professional Plus 2010 (Service Pack 2).  What is causing this? I have attached screenshots of both the form (and the error message) as well as a screenshot of the macro. Pls help. Thanks.
Qty-Problem.JPG
Qty-Problem---Macro---2.JPG
LVL 1
Theo FitchnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MacroShadowCommented:
Can you upload a sample db displaying this behavior?
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Do you have other conditions in the macro? I see an "ElseIf" statement, which typically means you have an "If" statement somewhere else.

Otherwise, try converting the values to numeric:

If CInt(frm.yourcombo) > CInt(frm.YourTextbox) Then
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
A combobox always returns text, so change your expression to:

    ElseIf Val([Form]![cmb...]) > Val([Form]![txt...])

/gustav
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Get the Guide
Theo FitchnerAuthor Commented:
@Gustav & @Scott
------------------------------
I figured that Access was seeing the contents of the combo box as text. To counter this, I set its After_Update event to convert the content to an integer. Like this: CInt([cmbQtySoldDrinks])

I'll try the methods both of you suggested.
0
Theo FitchnerAuthor Commented:
@Scott
---------

This is the full If statement. See the attached screenshot.
Qty-Problem---Macro.JPG
0
Gustav BrockCIOCommented:
Yes, but that won't influence on your macro which picks the value directly off the combobox.

Besides, CInt and CLng (and CByte for that matter) don't like empty strings, and large(r) values.
Val eats anything but Null.

/gustav
0
Theo FitchnerAuthor Commented:
@MacroShadow
------------------------
It's a production db with live data. I'll need to create a 'neutral version' with just the relevant tables and forms. If the other suggestions don't work, I'll do that. I apologize for any inconveniences.
0
Theo FitchnerAuthor Commented:
Converting both values to an integer before doing the comparison worked.  This is the conditional statement:

If CInt([Form].[cmbQtySoldDrinks])>CInt([Form].[txtQtyAvailableDrinks]), Then ...


Gustav's suggestion of using the Val function also worked but I think the Val function would be more appropriate when there are spaces in the value. For instance, a phone number. As regards, nulls, there's a conditional statement that ensures that nulls are not accepted. I didn't know about the Val function until Gustav suggested it.

Thanks both of you. God bless.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.