Using IN operator with LIKE operator

[Regnab]

I've got a number of 'Lots' of cattle - ie 40A, 372A, W123 etc. The
letter in the Lot number indicates where the animal is traceable or
not. The animal's Lot number, along with its individual details are
recorded in a table.

I have a second table tblLotTraceability, with the fields "LotLetter"
and "TraceabilityRequired". I have built a query (with an IN operator)
that searchs the tblLotTraceability table, returning all LotLetters
that are traceable as a parameter. The problem is that the "Lots" are
not just the letter, but a number as well. Usually I use a LIKE
operator (Like "*A*").... is there some way of doing a

-- Like "*In (SELECT LotLetter FROM tblLotTraceability WHERE
(((tblLotTraceability.TraceabilityRequired)=Yes)); )*" --

Cheers

Reg

[teddysnips]

Regnab wrote:

I've got a number of 'Lots' of cattle - ie 40A, 372A, W123 etc. The
letter in the Lot number indicates where the animal is traceable or
not. The animal's Lot number, along with its individual details are
recorded in a table.

I have a second table tblLotTraceability, with the fields "LotLetter"
and "TraceabilityRequired". I have built a query (with an IN operator)
that searchs the tblLotTraceability table, returning all LotLetters
that are traceable as a parameter. The problem is that the "Lots" are
not just the letter, but a number as well. Usually I use a LIKE
operator (Like "*A*").... is there some way of doing a

-- Like "*In (SELECT LotLetter FROM tblLotTraceability WHERE
(((tblLotTraceability.TraceabilityRequired)=Yes)); )*" --

A little DDL would be helpful here. I suspect that what you want is
possible.

Edward

[Salad]

Regnab wrote:

I've got a number of 'Lots' of cattle - ie 40A, 372A, W123 etc. The
letter in the Lot number indicates where the animal is traceable or
not. The animal's Lot number, along with its individual details are
recorded in a table.

I have a second table tblLotTraceability, with the fields "LotLetter"
and "TraceabilityRequired". I have built a query (with an IN operator)
that searchs the tblLotTraceability table, returning all LotLetters
that are traceable as a parameter. The problem is that the "Lots" are
not just the letter, but a number as well. Usually I use a LIKE
operator (Like "*A*").... is there some way of doing a

-- Like "*In (SELECT LotLetter FROM tblLotTraceability WHERE
(((tblLotTraceability.TraceabilityRequired)=Yes)); )*" --

Cheers

Reg

Why not create a query and drop in your two tables?

Set a relationship on LotLetter between the two. Drag LotLetter from
your Lot table into a column. Set the criteria to Like "*A*

Drag TraceabilityRequired into another column from the
tblLotTraceability table. Set the criteria to True.

Keep it simple.

[Regnab]

Sorry Salad - I may not have explained the table structure properly

2 tables - tblLotTraceability, tblInductions

Field: tblLotTraceability - LotLetter, Grade, TraceabilityRequired
tblInductions - Lot, VisualID, Weight etc....

The link is that the "tblInductions.Lot" has the
"tblLotTraceability.LotLetter" somewhere within it - hence the need to
use like. There are about 10 different letters, and could have multiple
Lots associated with them - I might import 2 A lots (21A, 45A), a
couple of W lots (W140, W133) and so on. I'm trying to get a link
between them to check traceability... if this is the way you understood
it, then maybe I've misunderstood your solution.

Cheers

Reg

[Regnab]

Ended up just doing it in the code after the files are imported.

Private Sub UpdateLetterLotMatchup()
Dim arrLetter As Variant
Dim N As Integer

arrLetter = Array("A", "B", "C", "D", "E", "F", "G", "H", "X", "W",
"S", "T", "R", "Y")
N = 0

DoCmd.SetWarnings False

DoCmd.RunSQL "DELETE * FROM tblLetterLotNum;"

For Each I In arrLetter
DoCmd.RunSQL "INSERT INTO tblLetterLotNum (LotNumber, Letter) "
& _
"SELECT tblInductions.LotNumber, '" & arrLetter(N)
& "' " & _
"FROM tblInductions " & _
"GROUP BY tblInductions.LotNumber " & _
"HAVING (((tblInductions.LotNumber) Like '*" &
arrLetter(N) & "*'));"
N = N + 1

Next I

DoCmd.SetWarnings True

End Sub

Best way I could think of doing it - constructive critisism always
welcome....still learning ;)

Cheers

Reg