Delivered QTY Cancel order - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Delivered QTY Cancel order

Mohei Eldin Fouad

3 years ago

Hello,
I've build a good database for my department to record orders and the quantities delivered to the customers.
But after almost a complete one year of using this database.

When I designed the table of order details I made the delivery field as text to let the me record the delivered quantity and if the order cancelled then I can add the word cancelled.
So, when I tried to make a new query to sum the delivered quantity access for sure refused to sum the numbers and ignore the cancel word.
Is there someway to solve this problem? and how to fix it in next years of use to the database?

	Kevin Robertson @Reply 3 years ago
I would make the Delivered Quantity a number and have a a Check Box to determine if the delivery has been cancelled or not. A second option would be to make any non-numeric data a zero using the IIF Function. `X: CInt(IIf(Not IsNumeric([Delivered]),0,[Delivered]))`

Adam Schwanz @Reply

3 years ago

First you'll have to ommit the non-numeral entries with a query where criteria. Then take those results and change the value to CDbl (if decimals) or CInt (if whole numbers). Then you can use those as numbers for a quantity.

Or you could make a cancelled checkbox, use an update query to mark true for all the ones with the word cancelled on that field, then use an update query to change "cancelled" to either blank or 0 and change the field to a number field.

	Mohei Eldin Fouad OP @Reply 3 years ago
I can make this query monthly or quarterly or even yearly. Would be much easier to me to train my colleagues to use the check box for the cancelled order lol I think I'll use CDbl(IIF(Not IsNumeric([DeliveredQuantity]),0, [DeliveredQuantity])) Thanks Kevin and thanks Adam

	Mohei Eldin Fouad OP @Reply 3 years ago
I know it might be a bad question from me!!! But.... Those 0 instead of "cancelled" will only shown on the result of my select query and will not affect the table and other queries in the database depends on the same field, or table!!! Right?

	Mohei Eldin Fouad OP @Reply 3 years ago
Also, there is one more question.... What about the blank fields which is still didn't delivery yet? Am I going to convert the Null fields to 0 for the purpose of this query only?

	Kevin Robertson @Reply 3 years ago
It will just show up in the Query it's used in for the purpose of summing up the numeric values.

	Kevin Robertson @Reply 3 years ago
Since the Null is not a numeric value it should become 0 as well. Give a go.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View