Character recognition and automatic values input

[psuplat]

Hi.

I've got a table "table1" with field Serial_no and model (among others).
the serial number of the product has a following format:
A9BC1234567 - Month,Year,Factory,Model and 6 digits.

when i input the serial number on the form (field "Serial_no") i want access to recognize the string, take 4th character and based on that placed a apropriet value, like Model1 or Model2, in the field "Model".

Can somebody help me with that one.

Thanks.

 

[DCrake]

Can you provide an actual example as your explanatio is a bit vague.

David

 

[psuplat]

I don't know how to make it any simpler.

I have a table called "Table1", with 2 fields "Serial_no" and "Model", both text type.

I input values to this table through a form.

The form has to fields, "Serial_no" and "Model".
To "Serial_no" I'm inputing following values:
A9CB123865A
C9CB536765M
G9CD934763M
J9CL934576A

4th character of those numbers describes a model. Say B is model MOD2000, D is MOD3100 and L is MOD2100.

Now, i don't want to input the model types by myself all the time. I want access to, after I input the serial number, recognize 4th character and placed appropriate value in field "Model".

If I put E9CD967432A in "Serial_No", value MOD3100 should automatically appear in field "Model".

Hope that explains it.

 

[dcb]

Create a table like:

tblModelLookup
ModelID | Model
B | MOD2000

Then after update event for the serial number your code should be along the lines of:

Me.Model = DLookup ("Model", "tblModelLookup", "ModelID = """" & Right(Left(Me.Serial),4),1) & """" & )

Or

Dim strModel, strSerial, strModelID as string
strSerial = Me.Serial
strModelID = Right(Left(strSerial),4),1)
strModel = DLookup ("Model","tblModelLookup","ModelID = " & """" & strModelID & """" & )

 

[psuplat]

I assume that Me is the form name/indentifier

 

[DCrake]

The code Right(Left(strSerial),4),1)

can be simplified by

Mid(strSerial,4,1)

David

 

[psuplat]

Can this be done just with the VB code, without creating a separate table. I only need recognition for 4 different letter (D,E,T,X).