Using Left and Like in the same query

[guamlet]

I have a form that asks users to type an identifying catalog string in a field. There are other fields below the name that contain drop down boxes for age, species, etc. When a user enters an catalog number, I want access to look at the characters before a period and compare them to all the names in a table. If the first four characters match the first four in that table, I want it to copy the age, species, etc from the table into the appropriate fields in the form.

Example:
Samples H004a. and H004.a.2b came from the same animal (H004a), so if a user takes a new sample from that same animal, they would write in the field H004.a.2b.3c. After they exit that field, I want access to compare all the samples in the table holding the sample names and look for the H004a and copy the age and species to the appropriate boxes in the form.

I was thinking of using a separate query for each field (age, species, etc) that would return the value when the criteria was met. I can't figure out how to use something similar to Left(

,#)=Like(Left([form],#)) but instead of a number, I want it to look left up to a "." . Any help?

Guamlet

 

[guamlet]

I solved my problem thanks to http://www.rogersaccesslibrary.com/TableOfContents2k.asp


The trick is to create a combo box bound to a column in the table where you want the information copied from. The column used must be unique!

The code for the combo box is as follows

Option Compare Database
Private Sub ParentLine_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[LineID] = " & Str(Me![ParentLine])
LineName = rs!LineName
Gender = rs!Gender
Race = rs!Race
Age = rs!Age
Fraction = rs!Fraction
CreationDate = ""
Project = Null
ProjectLeader = Null
EmployeeID = Null
LabPage = Null
Action = Null
ParentLine = Null
End Sub

Private Sub Form_Current()
Me!ParentLine = Null
End Sub

This code basically copies the information that is wanted and keeps everything else as Null, and then clears the parent combo box. It's quite handy!