Access query criteria using Like, wildcard and fieldname

[DanD76]

Access 2002:
I need to query a database with criteria that selects rows from a single table where the value in a computed field is contained in the value of a different field in the row.
--------------
Example of values: table1.path = h:\users\dand\my docs
LoggedInUser = dand
Using these values, I would want to select all rows where the field table1.path contained "dand".
--------------
Something like the following statement but I can't get the wildcards to work.

SELECT table1.path, Environ("username&quot AS LoggedInUser
FROM table1
WHERE (((table1.path) Like *[LoggedInUser]*))
ORDER BY table1.path;

Appreciate any help. Thanks

 

[ETID]

Depending on what db you connect to... it's something like this

SELECT table1.path, Environ("username&quot AS LoggedInUser
FROM table1
WHERE (((table1.path) Like "*"&[LoggedInUser]&"*&quot)
ORDER BY table1.path;


or maybe just single qoutes

 

[DanD76]

Thanks for the reply.

I entered in your query statement but when I run the statement it prompts me for input for "LoggedInUser". If I enter a valid response to the prompt the query runs as I want it to. So I'm one step closer.

I was hoping to get this to run without having to enter the username. Any further suggestions?
Thanks

 

[DanD76]

But this works...

By using Environ("username&quot instead of LoggedInUser in the where clause it now works. Not sure why. Maybe the computed column LoggedInUser isn't calculated until after the query executes??

SELECT table1.path, Environ("username&quot AS LoggedInUser
FROM table1
WHERE (((table1.path) Like "*"&Environ("username&quot&"*&quot)
ORDER BY table1.path;

Thanks for your help.

 

[DanD76]

I've run into another snag. This Access 2002 query works on some computers but not others even though they have the same version of Office. On the computers that fail, I receive the error "Undefined function 'Environ' in expression." when I try to run the query.

The help file says this is "Error 3085 - You entered an SQL expression that includes a Function procedure name that cannot be recognized..."

This is the query:
SELECT table1.path, Environ("username&quot AS LoggedInUser
FROM table1
WHERE ((table1.path) = Environ("username&quot)
ORDER BY table1.path;

Why would this work on some computers and not others?
Any help would be apprecitated. Thanks