Inventory Control in ms access

I think that pretty much you have it..
About the expire Date and the current date you can work with Dates or DateSerial to get a numeric representation  or with custom Date mapping

My problem is how to substitute the wording below to make a readable computer code

ExpNumber = (1200 to 1300) is equal to between 01/05/2019 to 18/05/2019
ContrDate = 01/05/2019 to 18/05/2019

How do I substitute (1200 to 1300) is equal to between 01/05/2019 to 18/05/2019

And 01/05/2019 to 18/05/2019

Regards
Chris

I am afraid i have made a small mistake - DateSerial is not you need...i am on the phone now away from my workstation - the point is that date is a long underneath..so you just need to do the conversion...in order to get to your example you just need to extract a predefined value...

Without knowing more about your database structure, this is a bit of a stab in the dark, but in addition to the product code, you are going to have to have some other identifier on each product in the database.

Is this more of a point of sale system, or an inventory control system?  from the description, it sounds like more of a Point of Sale system than simply inventory control.

Most inventory systems track product codes or sku or some other code that is provided by the manufacturer and is already on each item.  When that product is received, the quantity of that product received is entered into an Inventory table.  And when the product is sold, a quantity is entered into a some form of SaleDetail table.

In your case, you are going to need some additional field, that will represent the expiration date, which would need to be printed onto a label , along with the original product code, and applied to each item (overtop of the original sku), so that when that item is scanned the scanner would receive both the sku and the expiration date.  

Challenges I see with this system:
1.  You will have to print and apply these bar-code labels to each item.  I have not had to deal with bar-codes in a long time, but this will be a time consuming process.  I think this is why most grocery stores do not bother with tracking expiration dates.  Has your client really thought this through?  
2.  The scanning software (or your POS system) will have to recognize this bar code as different from a basic sku and would then have to do the date comparison.

Dale

Very good analysis, I do agree, I just want to fix this mathematics  first the rest will come later

My problem is how to substitute the wording below to make a readable computer code

ExpNumber = (1200 to 1300) is equal to between 01/05/2019 to 18/05/2019
ContrDate = 01/05/2019 to 18/05/2019

How do I substitute (1200 to 1300) is equal to between 01/05/2019 to 18/05/2019

And 01/05/2019 to 18/05/2019

Without knowing where the "1200 to 1300" values are coming from, or where the range dates are coming from I could only guess that these values would be stored in a table somewhere, ProductExpirationCodes or something like that.  and you would store all 4 or maybe 3 (you really don't need the first date field) of those fields in a table.

Then, in your code you would get the ExpNumber, and filter this ProductExpirationCodes table for the sku, ExpNumber, and do a search to find the Expiration Date.

You are correct sir, I want to use this code below , the dlookup do not worry I will fix them there is already an update unbound form I'm currently working on to used to update both the code ( 1200 to 1300) the dates , the only issue is how to fix the VBA below assuming we the following controls are in a table (tblControl)

Codeone
CodeTwo
FinControlDate

Dim ExpNumbers As integer
Dim ContrDate  As Date
Dim Cancel As integer
ExpNumber = (1200 to 1300) is equal to between 01/05/2019 to 18/05/2019
ContrDate = 01/05/2019 to 18/05/2019
If (ExpNumber <>  ContrDate) Then
Beep
MsgBox “Please note that your product are expired and cannot be sold”
Cancel = True
Exit Sub
End If

Well i am back on my workstation...so to get a clearer picture...
If you convert Date to Long you get

Clng("01/05/209)  = 43586
Clng("18/05/209)  = 43603 

Select allOpen in new window

If you want to map 43586 to 1200 is just you have to do
(43586-A) * x = 1200
(43603-A) * x = 1300
Leave A for now
The date difference between (Datediff) = 17 days..
So we need to resolve the equation
When 17 days equals 100 (1300 - 1200) then how much = 1200
and also for 100 ==> x = 100/17
The result  equals around 204
So A = 43586-204 = 43382
Substituting to A we get
(43586-43382) * x = 1200
(43603-43382) * x = 1300
==>
204* 100/17 = 1200 (43586)
221 * 100/17 = 1300 (43603

Okay thank you so much , I'm almost there, any Idea how to reference a list box see below:

Forms!frmCustomersKeys!listChkey.Value

The name of list box is called listChkey


Regards

Chris