Checking serial number validity

[Sara Overton]

Hope you can help!

I have a form which the users enters a serial number and a product code into separate input boxes.

The product codes are held in a separate table with 3 fields -the start serial number, the end serial number, the increments they are counted in i.e. 3.

I need the database to check the serial number is within the start and end range bearing in mind each product may have a different increment.

If start is: 100
End is: 200
Increment is 5
The number 177 would be invalid.

Any ideas how to set the vb code up to check these 3 fields?

Thanks in advance

 

[joeyreyma]

not sure if this is what you mean. however, you can change the numbers (basing on your example) to the proper fields on your form.

Validity = iif((100 <= 177 and 200 >= 177) and (177 mod 5) = 0, "Valid","Invalid")

 

[Sara Overton]

Thanks alot for the quick response.

Problem is the Serial number always changes per entry.

So the code needs to look at the start and end Serial number and work out if the Serial Number input by the user falls into the incremental sum.

ie
Start 100
Increment 5
105 is valid and 137 is invalid.

Kind Regards

 

[axa]

Hi Sara,

you could use a version the following function to test the serial number:

____________________________________________

Function IsValidSerialNum(lngProdCode As Long, _
                            lngSerialNum As Long _
                             ) As Boolean
                            
    Dim rst As Recordset
    Dim strSQL As String
    Dim blnValid As Boolean    '-- by default, value = false
    
    '-- run query to get parameters for given product code
    strSQL = "SELECT [MinNum],[MaxNum],[Increment] FROM [ProductCodes] WHERE [ProductCode]=" & lngProdCode
    Set rst = CurrentDb.CreateQueryDef("", strSQL).OpenRecordset
    
    '-- check to make sure we have a return record for given product code
    If Not rst.EOF Then
        '-- check serial number falls within min and max limits
        If lngSerialNum >= rst("MinNum") And lngSerialNum <= rst("MaxNum") Then
            '-- check serial number is completly divisible by given increment
            If lngSerialNum Mod rst("Increment") = 0 Then
                '-- all tests passed; serial number is valid
                blnValid = True
            End If
        End If
    End If
    
    '-- clean up recordset objects
    rst.Close
    Set rst = Nothing
    
    '-- return result
    IsValidSerialNum = blnValid
End Function

____________________________________________

this function could be used like so:

If IsValidSerialNum(txtProdCode.value,txtSerialNum.value) = True Then
    '-- code to run for valid serial number
Else
    '-- code to run for _invalid_serial number
End If

Please Note: the above function assumes a table called ProductCodes exists with the fields ProductCode, MinNum, MaxNum, and Increment. It also assumes the ProductCode is a long integer. The function is simple enough to adapt to suit your needs if you require a different setup.

Hope that helps

axa




[This message has been edited by axa (edited 07-31-2001).]

 

[Sara Overton]

Thanks Axa

Problem: I'm not sure where to put the code.

I've tried putting against afterupdate on my serial number field rather than doing an input box for the time being.

But when I try to call the procedure it doesn't work!

Any ideas

 

[Ken Grubb]

Sara,

If the Start is 102, the End is 200, and the Increment is 5, which list of valid Serial Numbers do you want? Reason I ask is that the formula is slightly different for each list.

a) 102, 107, 112, 117 ...

b) 105, 110, 115, 120 ...

Ken Grubb
Burlington, NC, USA

 

[Sara Overton]

Ken

a) 102 107 etc

Thanks

 

[Ken Grubb]

This Function will validate SerialNum.

Public Function IsValidSerialNum(MinNum As Integer, MaxNum As Integer, _
Increment As Integer, SerialNum As Integer) As Boolean
If SerialNum >= MinNum And SerialNum <= MaxNum _
And (SerialNum - MinNum) Mod Increment = 0 Then
IsValidSerialNum = True
Else
IsValidSerialNum = False
End If
End Function

HTH,

Ken Grubb
Burlington, NC, USA


[This message has been edited by Ken Grubb (edited 08-02-2001).]

 

[Sara Overton]

Thankis alot.

I'm going on holiday now so I'll be back in touch if I need anymore help in 2 weeks!

Sara