Access Query Criteria

[rjm13]

Hi All,

I have a query in access and I am having trouble with the syntax for the criteria. What I want to do is reference a field in another table. The table only contains one row with one value (I have about 20 queries and want to be able to just change tthis value once in the table and have it change in all of the queries). The value in the table contains a wildcard as well. Here is the syntax that doesn't work: Like "[table1].[value]"

I know this is wrong but don't know what it should be. Any help is greatly appreciated.

thanks,

R

 

[lespaul]

first off, you need a wild card character to use like correctly:

LIKE "AC*" - finds all records that start with AC
Account

LIKE "*AC" - finds all records that end with AC
Lilac

LIKE "*AC*" - finds all records that contain AC
Account
Lilac
Tractor

Using a subquery is a good way to get what you need:

SELECT * From OneTable Where matchingValue = (SELECT EnteredValueInTable FROM TableWithOneRecord)



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[rjm13]

Thanks Leslie,

What I would like to do is just be able to put syntax on the criteria line in the access query grid. the value I have in the table currently is G6*, because I only want records that start with G6 in each query. So can I reference that field in the table on the criteria line like I mentioned in my previous post ex. Like "[table1].[value]"

Thanks again,

R

 

[lespaul]

Sorry I don't work in the Query Design Grid, maybe if you put this in your criteria it would work:

(SELECT Value FROM Table1) & "*"

you said there is only one record in this table right?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Golom]

Like [table1].[value]

without the quotes should work.

With the quotes SQL regards "[table1].[value]" as a literal string rather than as the value of field "Value" in table "Table1".

 

[randy700]

Try using dlookup.

LIKE DLookup("FieldName","TableName")

Randy

 

[rjm13]

Thank you all very much for your help. This is going to save alot of time in the future!

R