Working with decimals in ms access forms

[dammy]

Hi everyone
Please I need your help
I have a form order details where u enter the quantity of items you want to purchase and it deducted it from the balance available, it is working fine for whole numbers only, it is not accepting half(0.5) ,(0.25).

Any help on how access will be able to accept both decimals and whole numbers in the quantity section will be highly appreciated.

I have already set the quantity on the tables to decimals but I still can't see the effect.

Thanks and hope to hear from you soon.

 

[CazB]

what is the data type of the base fields, and have you checked the properties of the control on the form?

 

[Galaxiom]

I have already set the quantity on the tables to decimals but I still can't see the effect.

I expect you have used the default FieldSize for Number Datatype which is Long (an integer).

Read this page:
http://www.databasedev.co.uk/fields_datatypes.html

 

[dammy]

Thanks for the prompt reply
It is on general number and the decimal column is set to 2.
But I have a vba code embedded in the after update of the quantity column
I have attached what the vba code looks like
It seems I can't find the column to attach so I copy and pasted the code
Private Sub Quantity_AfterUpdate()
Dim IT As InventoryTransaction
Dim PurchaseOrderID As Long
Dim SupplierID As Long
Dim remainder As Long
Dim X As Integer
Dim Y As Integer

X = Me![Quantity]
Y = Nz(Me![Items.PackSize])
If MyPriceBand = "pack" Or MyPriceBand = "pack wholesales" Then
If Y <> 0 Then
remainder = X - (Round(X / Y)) * Y
If remainder <> 0 Then
MsgBox "Quantity must be in multiples of " & Y, vbInformation, "Invalid quantity"
Me![Quantity] = 0
Me![Quantity].SetFocus
Exit Sub
End If
End If
End If

If CInt(Me![Item ID].Column(2)) < Me![Quantity] Then
MsgBox "You are ordering more than you have in stock", vbInformation, "Quantity reduced to available"
Me![Quantity] = CInt(Me![Item ID].Column(2))
End If

IT.DrugID = Nz(Me![Item ID], 0)
IT.Quantity = Me![Quantity]
IT.AllOrNothing = True
IT.InventoryID = Nz(Me![Inventory ID], NewInventoryID)

'Request Hold on specified Inventory
If Inventory.RequestHold(Me![Order ID], IT) Then
Me![Inventory ID] = IT.InventoryID
Me![Status ID] = OnHold_OrderItemStatus

'Insufficient Inventory
ElseIf Me![Status ID] <> None_OrderItemStatus And Me![Status ID] <> NoStock_OrderItemStatus Then
MsgBoxOKOnly InsufficientInventory
Me![Quantity] = Me.Quantity.OldValue


'Attempt to create purchase order for back ordered items
ElseIf MsgBoxYesNo(NoInventory) Then

SupplierID = Inventory.FindDrugSupplier(IT.DrugID)


End If

Done:

Me.Item_ID.SetFocus
Me.Refresh


Exit Sub

End Sub


Thanks

 

[dammy]

Thanks for your prompt reply
I actually chose a correct datatype, decimal
Thanks and hope to hear from you soon

 

[JHB]

From the MS-Access Help file:

Round Function
Description
Returns a number rounded to a specified number of decimal places.
Syntax
Round(expression [,numdecimalplaces])
The Round function syntax has these parts:
Part Description expression Required. Numeric expression being rounded. numdecimalplaces Optional. Number indicating how many places to the right of the decimal are included in the rounding. If omitted, integers are returned by the Round function.

 

[dammy]

Thanks for the reply and sorry for my late response
I don't understand your reply.
Do I place what you said in the vba code?
Thanks and hope to read from you soon