querying records using wildcards and a table field

  • Thread starter Thread starter Todd
  • Start date Start date
T

Todd

I need to query a access table text field (100 char),
using a text field in another table (7 char).

I've a query using Like "*[TableName]![Field]*". But it
doesn't work.

I'm sure it's easy.

Thank you.
 
Hi Todd,

You need to be clearer about what you mean by
[TableName]![Field]

If you want to get records that match a value from a field in a record
from another table which is currently displayed on a form, use something
like

Like "*" & [Forms]![XXX]![YYY] & "*"

where XXX is the name of the form and YYY the name of the textbox
displaying the field (which is by default the same as the name of the
field). (If this is in VBA code, you'll need to double up some of the
quote marks.

If you want to get records that match a value from a field in a
particular record in another table, you can use the DLookup() function
to look up that record (based on the contents of one or more other
fields) and return the value of the field. In that case the query
expression would be something like

Like "*" & DLookup("FFF", "TTT", "KeyField = 99") & "*"

where TTT is the name of the table, and FFF the field.

I need to query a access table text field (100 char),
using a text field in another table (7 char).

I've a query using Like "*[TableName]![Field]*". But it
doesn't work.

I'm sure it's easy.

Thank you.
Click to expand...
 
Hi John,

Thanks for the reply. I'll try to clarify.

I have a long text field in a table. A smaller text
field in another table.

I need to find all the records in the long text field,
that contain the small text field somewhere in it's
content.

So what work's for me, is if I do each manually, in an
access query. Using
like "*thespecifictextI'mlookingfor*", in the criteria
field, one record at a time for each entry in the smaller
text table. However I need a way to do this other than
one record at a time, hence I tried [TableName]![Field]
in the criteria field, it doesn't work.

For each match I'm need to return the long text field and
another field in the same table.

So I'm working with tables and queries.

I hope this clarifies what I'm talking about.

Todd
-----Original Message-----
Hi Todd,

You need to be clearer about what you mean by
[TableName]![Field]

If you want to get records that match a value from a field in a record
from another table which is currently displayed on a form, use something
like

Like "*" & [Forms]![XXX]![YYY] & "*"

where XXX is the name of the form and YYY the name of the textbox
displaying the field (which is by default the same as the name of the
field). (If this is in VBA code, you'll need to double up some of the
quote marks.

If you want to get records that match a value from a field in a
particular record in another table, you can use the DLookup() function
to look up that record (based on the contents of one or more other
fields) and return the value of the field. In that case the query
expression would be something like

Like "*" & DLookup("FFF", "TTT", "KeyField = 99") & "*"

where TTT is the name of the table, and FFF the field.

I need to query a access table text field (100 char),
using a text field in another table (7 char).

I've a query using Like "*[TableName]![Field]*". But it
doesn't work.

I'm sure it's easy.

Thank you.
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Hi Todd,

I need to clarify too<g>. Setting aside questions of syntax, the problem
with [TableName]![FieldName] is that it doesn't actually specify a
particular value because a table can contain umpteen records each of
which has a value for that field. You have to explain just what you mean
by it. I guess it could be this:

"The table TableName is open in datasheet view. Take the value of field
FieldName in the currently selected record, and return matching records
from the other table." If that's what you want, it can't be done from a
table, at least not without extraordinary contortions. Instead, use a
form bound to (a query on) TableName and the
[Forms]![FormName]![ControlName] syntax.


Hi John,

Thanks for the reply. I'll try to clarify.

I have a long text field in a table. A smaller text
field in another table.

I need to find all the records in the long text field,
that contain the small text field somewhere in it's
content.

So what work's for me, is if I do each manually, in an
access query. Using
like "*thespecifictextI'mlookingfor*", in the criteria
field, one record at a time for each entry in the smaller
text table. However I need a way to do this other than
one record at a time, hence I tried [TableName]![Field]
in the criteria field, it doesn't work.

For each match I'm need to return the long text field and
another field in the same table.

So I'm working with tables and queries.

I hope this clarifies what I'm talking about.

Todd
-----Original Message-----
Hi Todd,

You need to be clearer about what you mean by
[TableName]![Field]

If you want to get records that match a value from a field in a record
from another table which is currently displayed on a form, use something
like

Like "*" & [Forms]![XXX]![YYY] & "*"

where XXX is the name of the form and YYY the name of the textbox
displaying the field (which is by default the same as the name of the
field). (If this is in VBA code, you'll need to double up some of the
quote marks.

If you want to get records that match a value from a field in a
particular record in another table, you can use the DLookup() function
to look up that record (based on the contents of one or more other
fields) and return the value of the field. In that case the query
expression would be something like

Like "*" & DLookup("FFF", "TTT", "KeyField = 99") & "*"

where TTT is the name of the table, and FFF the field.

I need to query a access table text field (100 char),
using a text field in another table (7 char).

I've a query using Like "*[TableName]![Field]*". But it
doesn't work.

I'm sure it's easy.

Thank you.
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
Hi John,
So I guess that's the underlying problem. I need to
somehow, get a text field from one table to be queried,
that includes any part of a record (that's in another
table).

I have one table that has a text field of 155 char. This
table is 14000 records.

I have a second table that has another text field, where
the char length is 7 char. It has about 2000 records.

I need to figure out how to query table one (14000) that
include any part of the text records in the second table.

I know I can do them one at a time. But I don't want to
do 2000 like that.

It's basically pedigree type data. Where the two parents
are listed in one text field that is very messy (table
one above). I have a list of mom and dads (second
table). That are included in this messy pedigree field.
I'm just trying to find where the mom and dads are in the
parentage of table one.

Let me know if you have any other ideas. Maybe I
explained myself better this time. Thanks very much for
your input and help!

Best regards,
Todd

-----Original Message-----
Hi Todd,

I need to clarify too<g>. Setting aside questions of syntax, the problem
with [TableName]![FieldName] is that it doesn't actually specify a
particular value because a table can contain umpteen records each of
which has a value for that field. You have to explain just what you mean
by it. I guess it could be this:

"The table TableName is open in datasheet view. Take the value of field
FieldName in the currently selected record, and return matching records
from the other table." If that's what you want, it can't be done from a
table, at least not without extraordinary contortions. Instead, use a
form bound to (a query on) TableName and the
[Forms]![FormName]![ControlName] syntax.


Hi John,

Thanks for the reply. I'll try to clarify.

I have a long text field in a table. A smaller text
field in another table.

I need to find all the records in the long text field,
that contain the small text field somewhere in it's
content.

So what work's for me, is if I do each manually, in an
access query. Using
like "*thespecifictextI'mlookingfor*", in the criteria
field, one record at a time for each entry in the smaller
text table. However I need a way to do this other than
one record at a time, hence I tried [TableName]![Field]
in the criteria field, it doesn't work.

For each match I'm need to return the long text field and
another field in the same table.

So I'm working with tables and queries.

I hope this clarifies what I'm talking about.

Todd
-----Original Message-----
Hi Todd,

You need to be clearer about what you mean by
[TableName]![Field]

If you want to get records that match a value from a field in a record
from another table which is currently displayed on a form, use something
like

Like "*" & [Forms]![XXX]![YYY] & "*"

where XXX is the name of the form and YYY the name of the textbox
displaying the field (which is by default the same as the name of the
field). (If this is in VBA code, you'll need to double up some of the
quote marks.

If you want to get records that match a value from a field in a
particular record in another table, you can use the DLookup() function
to look up that record (based on the contents of one
Click to expand...
or
more other
fields) and return the value of the field. In that
Click to expand...
case
the query
expression would be something like

Like "*" & DLookup("FFF", "TTT", "KeyField = 99") & "*"

where TTT is the name of the table, and FFF the field.
Click to expand...
wrote:

I need to query a access table text field (100 char),
using a text field in another table (7 char).

I've a query using Like "*[TableName]![Field]*". But it
doesn't work.

I'm sure it's easy.

Thank you.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
I think I understand the situation now. You've got a field in one table
that (drawing from a different problem domain) contains values like
this:

1 milk eggs salt dripping
2 butter eggs milk salt pepper
3 beef onions garlic wine stock carrots mushrooms bacon butter
4 lamb barley potatoes onions dripping
5 beef eggs wheat garlic oil
6 eggplant zucchini onion tomatoes
7 beef potatoes

and so on, and a second table with a field like this
beef
potatoes
lamb
garlic
and you want to return every record that includes any of these items.

I don't know any way of doing this in straight SQL. The problem is the
non-normalised "pedigree" field.

The best approach IMO would be to restructure the data, replacing this
non-normalised field with records (one per parent or ancestor) in a
related table.

If this isn't possible, I fear you'll need to use VBA code that creates
a recordset on the second table and then iterates through the records.
With fewer records you could use this to build up a long SQL WHERE
clause, but with 2000 you'd probably run up against Access's limits on
length and or complexity of SQL statements. So I expect you'd need to
run a query 2000 times and use a temporary table to store the primary
keys of the selected records. Something like this air code:

Dim rsR As DAO.Recordset
Dim dbD as DAO.Database

Set dbD = CurrentDB()
Set rsR = dbD.OpenRecordset("TableTwo")
dbd.Execute "DELETE FROM TempTable;"
With rsR
Do Until .EOF
dbD.Execute "INSERT INTO TempTable (AnimalID) " _
& "SELECT AnimalID FROM TableOne WHERE " _
& "Pedigree LIKE """ & .Fields("AnimalID") _
& """;"
.MoveNext
Loop
End With
rsR.Close
Set rsR = Nothing
Set dbD = Nothing

But I'm not a SQL wizard. It may be worth posting in the
microsoft.public.access.queries newsgroup where the real experts hang
out.

Hi John,
So I guess that's the underlying problem. I need to
somehow, get a text field from one table to be queried,
that includes any part of a record (that's in another
table).

I have one table that has a text field of 155 char. This
table is 14000 records.

I have a second table that has another text field, where
the char length is 7 char. It has about 2000 records.

I need to figure out how to query table one (14000) that
include any part of the text records in the second table.

I know I can do them one at a time. But I don't want to
do 2000 like that.

It's basically pedigree type data. Where the two parents
are listed in one text field that is very messy (table
one above). I have a list of mom and dads (second
table). That are included in this messy pedigree field.
I'm just trying to find where the mom and dads are in the
parentage of table one.

Let me know if you have any other ideas. Maybe I
explained myself better this time. Thanks very much for
your input and help!

Best regards,
Todd

-----Original Message-----
Hi Todd,

I need to clarify too<g>. Setting aside questions of syntax, the problem
with [TableName]![FieldName] is that it doesn't actually specify a
particular value because a table can contain umpteen records each of
which has a value for that field. You have to explain just what you mean
by it. I guess it could be this:

"The table TableName is open in datasheet view. Take the value of field
FieldName in the currently selected record, and return matching records
from the other table." If that's what you want, it can't be done from a
table, at least not without extraordinary contortions. Instead, use a
form bound to (a query on) TableName and the
[Forms]![FormName]![ControlName] syntax.


Hi John,

Thanks for the reply. I'll try to clarify.

I have a long text field in a table. A smaller text
field in another table.

I need to find all the records in the long text field,
that contain the small text field somewhere in it's
content.

So what work's for me, is if I do each manually, in an
access query. Using
like "*thespecifictextI'mlookingfor*", in the criteria
field, one record at a time for each entry in the smaller
text table. However I need a way to do this other than
one record at a time, hence I tried [TableName]![Field]
in the criteria field, it doesn't work.

For each match I'm need to return the long text field and
another field in the same table.

So I'm working with tables and queries.

I hope this clarifies what I'm talking about.

Todd
-----Original Message-----
Hi Todd,

You need to be clearer about what you mean by
[TableName]![Field]

If you want to get records that match a value from a
field in a record
from another table which is currently displayed on a
form, use something
like

Like "*" & [Forms]![XXX]![YYY] & "*"

where XXX is the name of the form and YYY the name of
the textbox
displaying the field (which is by default the same as
the name of the
field). (If this is in VBA code, you'll need to double
up some of the
quote marks.

If you want to get records that match a value from a
field in a
particular record in another table, you can use the
DLookup() function
to look up that record (based on the contents of one or
more other
fields) and return the value of the field. In that case
the query
expression would be something like

Like "*" & DLookup("FFF", "TTT", "KeyField = 99")
& "*"

where TTT is the name of the table, and FFF the field.

On Fri, 16 Jan 2004 14:24:20 -0800, "Todd"
<[email protected]>
wrote:

I need to query a access table text field (100 char),
using a text field in another table (7 char).

I've a query using Like "*[TableName]![Field]*". But
it
doesn't work.

I'm sure it's easy.

Thank you.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
Thanks John.

I'll try to post something in there.

Todd
-----Original Message-----
Hi Todd,

I need to clarify too<g>. Setting aside questions of syntax, the problem
with [TableName]![FieldName] is that it doesn't actually specify a
particular value because a table can contain umpteen records each of
which has a value for that field. You have to explain just what you mean
by it. I guess it could be this:

"The table TableName is open in datasheet view. Take the value of field
FieldName in the currently selected record, and return matching records
from the other table." If that's what you want, it can't be done from a
table, at least not without extraordinary contortions. Instead, use a
form bound to (a query on) TableName and the
[Forms]![FormName]![ControlName] syntax.


Hi John,

Thanks for the reply. I'll try to clarify.

I have a long text field in a table. A smaller text
field in another table.

I need to find all the records in the long text field,
that contain the small text field somewhere in it's
content.

So what work's for me, is if I do each manually, in an
access query. Using
like "*thespecifictextI'mlookingfor*", in the criteria
field, one record at a time for each entry in the smaller
text table. However I need a way to do this other than
one record at a time, hence I tried [TableName]![Field]
in the criteria field, it doesn't work.

For each match I'm need to return the long text field and
another field in the same table.

So I'm working with tables and queries.

I hope this clarifies what I'm talking about.

Todd
-----Original Message-----
Hi Todd,

You need to be clearer about what you mean by
[TableName]![Field]

If you want to get records that match a value from a field in a record
from another table which is currently displayed on a form, use something
like

Like "*" & [Forms]![XXX]![YYY] & "*"

where XXX is the name of the form and YYY the name of the textbox
displaying the field (which is by default the same as the name of the
field). (If this is in VBA code, you'll need to double up some of the
quote marks.

If you want to get records that match a value from a field in a
particular record in another table, you can use the DLookup() function
to look up that record (based on the contents of one
Click to expand...
or
more other
fields) and return the value of the field. In that
Click to expand...
case
the query
expression would be something like

Like "*" & DLookup("FFF", "TTT", "KeyField = 99") & "*"

where TTT is the name of the table, and FFF the field.
Click to expand...
wrote:

I need to query a access table text field (100 char),
using a text field in another table (7 char).

I've a query using Like "*[TableName]![Field]*". But it
doesn't work.

I'm sure it's easy.

Thank you.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Back
Top