Newbie ? about queries

[pv44]

I have 2 tables. Table1 is for CustomerInfo, table2 contains reference data concerning various models. There is 1 form, CustomerInfo form. There is a field (textbox) on the CustomerInfo form for serial number. There is also a field (textbox) for ModelName.

The serial number (12 digits/letters) contains info as to which model it is (digit/letter 3,4,5).
My reference table (table2) has a column for Model_No (digit/letter 3,4 and5) and a column for Model (what name that digit/letter 3,4 and5 represents).
I already know the various model numbers and what model name that particular model number stands for. When a serial number is entered I want the ModelName field to be filled in from a query.

How do you write a query that would: Parse the serial number, then use that parsed number to find a model number(Model_No column table2) and then a model name(Model column table2), then insert the model name into the CustomerInfo form in the ModelName field. Also would this query go into AfterUpdate event or where?

Thanks in advance, Van

 

[Topher]

if the serial # is say 10 characters long and you want characters 3,4,5 you could use

mid (SerialNo,3,2) - so it starts at the 3rd characters and ends two characters to the right - #5

in a query you would then say

select modelname
from table2
where modelno = mid (SerialNo,3,2)

then you would get the model name.

to put it in the text box using the afterupdate feature you could use a recordset based on the query which would contain the model number, then just assign the value to the text box. if more than 1 model name comes up you may want to use a listbox. i find this the easiest way. but you may be able to do it based on forms and subforms but im not too familiar with those.

- T