Re: [MS_AccessPros] Not getting correct values on form with different inputs

 

Duane,

Yes! it is now OK using Round function. Up to me it is fine, i will discuss the output with my boss if still he has any reservations i'll come again for this thread.

Thank you very much.

Regards,

Khalid

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

Consider using the Round() function.

InvoiceRate = Round([InvoiceRateLong],2)

Duane

---

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Thursday, January 19, 2017 9:38 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Not getting correct values on form with different inputs

 

Duane,

As the user of this database is my boss. He had these observations and was annoyed (before inclusion of Field "InvoiceRateLong") with the problem that "InvoiceRate" was getting the value as it shows on calculator and i was displaying it in 2 decimals, but when "InvoiceRate" was clicked it showed the long value after decimal, so i included field "InvoiceRateLong" and kept it hidden and did get 2 decimal value in "InvoiceRate".

Private Sub TotalNetWt_AfterUpdate()

In the After Update event of "TotalNetWt"

       InvoiceRateLong = TotalNetWt * ValueOfCustom / TotalProductQty

       InvoiceRate = Left([InvoiceRateLong], 4)

       TotalValue = (InvoiceRate) * TotalProductQty

'       TotalValue = Left([InvoiceRate], 4) * TotalProductQty <--- i just tried this line, actual is above line

End Sub

Regards,

Khalid

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

Khalid,

If the fields and variables are numeric, don't use string functions on them. You need to explain what you are attempting to accomplish with:

Left([InvoiceRateLong], 4)

Left([InvoiceRate], 4) * TotalProductQty

Regards,

Duane Hookom

Vevey, Switzerland

---

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Thursday, January 19, 2017 6:33 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Not getting correct values on form with different inputs

 

Duane,

Yes the all fields in question are numeric.

Khalid

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

Khalid,

The issue that stands out to me is you are using a string function Left() on a numeric value. I expect you should be using numeric functions to return parts of the number.

Regards,

Duane Hookom

Vevey, Switzerland

---

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Khalid Tanweer khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Thursday, January 19, 2017 5:43 AM
To: Access Professionals Yahoo Group
Subject: [MS_AccessPros] Not getting correct values on form with different inputs

 

Hi John,

I have a Form "Export Invoice And Packing List" having only one Unbound control "ConsignmentNo" . Its Record Source SQL is:

SELECT DISTINCT PrepareInvoiceAndPackingListQuery.ConsignmentNo

FROM PrepareInvoiceAndPackingListQuery

ORDER BY PrepareInvoiceAndPackingListQuery.ConsignmentNo DESC;

On the sub form "Export Invoice And Packing List subform" data is entered, its Record Source SQL is:

SELECT [Invoice Values PakCaspian Ammended].*, Products.CustomValue

FROM Products INNER JOIN [Invoice Values PakCaspian Ammended] ON Products.ProductID = [Invoice Values PakCaspian Ammended].ProductID;

On Txt Box "TotalNetWt" After Update is:

Private Sub TotalNetWt_AfterUpdate()

       InvoiceRateLong = TotalNetWt * ValueOfCustom / TotalProductQty ç(This is hidden field)

       InvoiceRate = Left([InvoiceRateLong], 4)

'       TotalValue = (InvoiceRate) * TotalProductQty

       TotalValue = Left([InvoiceRate], 4) * TotalProductQty

End Sub

The issue and problem which I am facing is that if "TotalProductQty" is smaller or may be upto 40,000 thousands figure I get correct "Invoice Rate" and "TotalValue". But if it is higher value like 7,700 I get wrong result. Take an example:

Total Cartons	Total Product Qty	Custom Value	Total Gross Weight	Total Net Weight	Invoice Rate	Total Value
50	40,000	4.00	105	102	0.01	400
500	7,700	1.10	500	492	7.02	54,054

 

In we check first entry on calculator 102 * 4 / 40,000 = 0.0102 (Invoice Rate) and we are displaying 2 decimals,  and TotalValue on our form is calculated by InvoiceRate 0.01 * TotalProductQty 40,000 = 400

Now in 2^nd entry if we check on calculator TotalNetWt 492 * ValueOfCustom 1.10 = 0.0702857142857143, We need to get here on our form InvoiceRate = 0.07 instead of 7.02

I cannot figure out where I am wrong, wether my update event is wrong or defined field size wrong.

TotalProductQtyàDoubleàStandardà0

TotalGrossWtàDoubleàStandardà0

TotalNetWtàDoubleàStandardà0

InvoiceRateLongàDoubleàStandardà2

InvoiceRateàDoubleàStandardà2

TotalValueàDoubleàStandardà2

Help required please.

Regards,

Khalid

 

__._,_.___

---

Posted by: khalidtanweerburrah@yahoo.com

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (8)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• New Members 2

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___