asked on
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
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
Microsoft Access
Last Comment
Theo Fitchner
Can you upload a sample db displaying this behavior?
ASKER CERTIFIED SOLUTION
Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a questionSOLUTION
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
@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.
--------------------------
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.
ASKER
@Scott
---------
This is the full If statement. See the attached screenshot.
Qty-Problem---Macro.JPG
---------
This is the full If statement. See the attached screenshot.
Qty-Problem---Macro.JPG
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
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
Besides, CInt and CLng (and CByte for that matter) don't like empty strings, and large(r) values.
Val eats anything but Null.
/gustav
ASKER
@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.
------------------------
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.
ASKER
Converting both values to an integer before doing the comparison worked. This is the conditional statement:
If CInt([Form].[cmbQtySoldDri
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.
If CInt([Form].[cmbQtySoldDri
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.