Parameter query - table lookup?

[Ann Scharpf via AccessMonster.com]

I want to know if I can use a TABLE lookup in parameter query. Two field
table: 1-city, 2-wildcard search string that relates to that city.

I would like a user to be able to select the city and have the wildcard
search string get passed as the parameter. Is this possible? I've been
reading the help and AccessMonster pages and so far don't see a way.

Thanks for any help you can give me.

Ann

 

[Marshall Barton]

I want to know if I can use a TABLE lookup in parameter query. Two field
table: 1-city, 2-wildcard search string that relates to that city.

I would like a user to be able to select the city and have the wildcard
search string get passed as the parameter. Is this possible? I've been
reading the help and AccessMonster pages and so far don't see a way.

It's a little ugly, but I think this criteria will do that:

Like DLookup("wildcard", "thetable", "city = """ & cityfield
& """")

 

[Ann Scharpf via AccessMonster.com]

Thanks, Marshall. Looks like this might work. I am going to try it now...

 

[Ann Scharpf via AccessMonster.com]

I'm trying to get this to work. I seem to have the DLOOKUP syntax correct
because it works if I use it as a text field (I just create a field and the
definition is to type the DLOOKUP text.) But, when I use it as the selection
criterion, I get no results. I have confirmed that, if the DLOOKUP worked
correctly, I should get 27 records.

Here is my SQL. Can you tell me what I did wrong?

SELECT tbl.Fld, DLookUp("[SiteWildcards]","LocalPrograms","[ID] = 1") AS test
FROM tbl
WHERE (((tbl.Fld) Like DLookUp("[SiteWildcards]","LocalPrograms","[ID] = 1")))
;

The "AS test" field is where I added the field to see if I could get the
DLOOKUP to work at all. I tested it by removing my selection criteria.

 

[Marshall Barton]

Adding the DLookup as a field to see what it found was an
excellent idea. Would you mind sharing what that was ;-)

Your query looks like I would expect so I'm thinking there
may be something about the SiteWildcards value??? A sample
of a couple of records it should match might also help me
spot something.
--
Marsh
MVP [MS Access]

I'm trying to get this to work. I seem to have the DLOOKUP syntax correct
because it works if I use it as a text field (I just create a field and the
definition is to type the DLOOKUP text.) But, when I use it as the selection
criterion, I get no results. I have confirmed that, if the DLOOKUP worked
correctly, I should get 27 records.

Here is my SQL. Can you tell me what I did wrong?

SELECT tbl.Fld, DLookUp("[SiteWildcards]","LocalPrograms","[ID] = 1") AS test
FROM tbl
WHERE (((tbl.Fld) Like DLookUp("[SiteWildcards]","LocalPrograms","[ID] = 1")))
;

The "AS test" field is where I added the field to see if I could get the
DLOOKUP to work at all. I tested it by removing my selection criteria.

It's a little ugly, but I think this criteria will do that:

Like DLookup("wildcard", "thetable", "city = """ & cityfield
& """")

 

[Ann Scharpf via AccessMonster.com]

The DLOOKUP will find only one matching record because each site (city) is in
the lookup table only one time. The results of the DLOOKUP are like this:

Like "*-to-*" or like "*santx*"

At first, I thought my problem was I was ending up with two "Like" adjacent
to each other because I had the "LIke" in the where statement and then the
like came up as part of the DLOOKUP string. So I tried changing the the
wildcard field to eliminate the first "like" and it had no effect.

"*-to-*" or like "*santx*"

This did not work either. Is it possible that you just can't use the DLOOKUP
in the criteria row of the query? If I type the string directly in the
criteria row instead of using the DLOOKUP, the database finds the target
records.

Thanks again for trying to help me, Marshall. I really appreciate it.

Adding the DLookup as a field to see what it found was an
excellent idea. Would you mind sharing what that was ;-)

Your query looks like I would expect so I'm thinking there
may be something about the SiteWildcards value??? A sample
of a couple of records it should match might also help me
spot something.

I'm trying to get this to work. I seem to have the DLOOKUP syntax correct
because it works if I use it as a text field (I just create a field and the

[quoted text clipped - 23 lines]

 

[Marshall Barton]

OK, that clarifies everything. Unfortunately, you are
trying to do something that can not work. The criteria that
the DLookup retruns can only be a single value (number or,
in this case, a string). It can not include syntactic
elements such as punctuation or operators. If you changed
the table so the DLookup only returned *-to-* it should
have worked fine.

So, now the problem is how to have multiple wildcard matches
for a single city. Obniously, DLookup can not deal with
multiple values, so we have to come up with another way. I
did something like this by using a non-equi join between the
two tables. (Note that a non-equi join can not be done in
the query design grid, you must use SQL view.)

First, change the LocalPrograms table so each city can have
more than one entry. Something like
ID SiteWildcards
----------------------------
1 *-to-&
1 *santx*
2 *aaa*
2 *bbb*
2 *ccc*
. . .

SELECT tbl.Fld, tbl.City, . . .
FROM tbl INNER JOIN LocalPrograms
ON LocalPrograms.ID = tbl.City
AND tbl.Fld Like LocalPrograms.SiteWildcards
--
Marsh
MVP [MS Access]

The DLOOKUP will find only one matching record because each site (city) is in
the lookup table only one time. The results of the DLOOKUP are like this:

Like "*-to-*" or like "*santx*"

At first, I thought my problem was I was ending up with two "Like" adjacent
to each other because I had the "LIke" in the where statement and then the
like came up as part of the DLOOKUP string. So I tried changing the the
wildcard field to eliminate the first "like" and it had no effect.

"*-to-*" or like "*santx*"

This did not work either. Is it possible that you just can't use the DLOOKUP
in the criteria row of the query? If I type the string directly in the
criteria row instead of using the DLOOKUP, the database finds the target
records.

Adding the DLookup as a field to see what it found was an
excellent idea. Would you mind sharing what that was ;-)

Your query looks like I would expect so I'm thinking there
may be something about the SiteWildcards value??? A sample
of a couple of records it should match might also help me
spot something.

I'm trying to get this to work. I seem to have the DLOOKUP syntax correct
because it works if I use it as a text field (I just create a field and the

[quoted text clipped - 23 lines]

Like DLookup("wildcard", "thetable", "city = """ & cityfield
& """")