USING TABLE VALUE IN "BETWEEN...AND" EXPRESSION

[pauld]

Is it possible to refer to two specific field values in a single-record table
when searching for a range of values in a query?

I want to use the values in one table to update a field in another table

For example, the search criteria I've tried is "Between [table.fieldname1]
And [table.fieldname2]

I've tried a number of different combinations of the Between...And
expression and nothing seems to work.

Do I need to "join" the tables? There are no common values or fields between
the 2 tables

 

[Douglas J. Steele]

What's the entire SQL statement you're trying to run?

 

[Duane Hookom]

If you have a single record table in your query that is not related to
another table, your query will not be updateable. You might get a solution if
you post your current update query SQL and/or other significant information.
Typically you may need to use a domain aggregate function such as DLookup().

 

[John W. Vinson]

Is it possible to refer to two specific field values in a single-record table
when searching for a range of values in a query?

I want to use the values in one table to update a field in another table

For example, the search criteria I've tried is "Between [table.fieldname1]
And [table.fieldname2]

I've tried a number of different combinations of the Between...And
expression and nothing seems to work.

Do I need to "join" the tables? There are no common values or fields between
the 2 tables

As noted, you'll need to post more information - but one possible problem is
your bracketing. Each object must be in its own brackets -

.[fieldname1] for example, rather than putting the period within the
brackets.

 

[pauld]

I am not at all converstant in SQL.

I am trying to do an Update Query that updates a field called "class" in a
table called "Assign Class".

I have a second table called "Values" where I have two fields called
"lovalue" and "hivalue".

I change the value of the fields in the table called Values via a form.

After I change the values, I want to run an Update Query to update the
"class" field in the Assign Class table.

I am looking for a way to write an expresstion in the Update Query criteria
like... if the value is between the lovalue and the hivalue in the Values
table, update the class field in the Assign Class table

I thought I could write the expression as....Between [Values].[lovalue] And
[Values].[hivalue], but it doesn't seem to work.

One thing I think is wrong is that I don't have the tables joined in the
query, but there are no common fields on which to create the relationship. Do
I need to join the tables. How?

Like I said, I'm not SQL conversant.

I appreciate everyone's help

Is it possible to refer to two specific field values in a single-record table
when searching for a range of values in a query?

I want to use the values in one table to update a field in another table

For example, the search criteria I've tried is "Between [table.fieldname1]
And [table.fieldname2]

I've tried a number of different combinations of the Between...And
expression and nothing seems to work.

Do I need to "join" the tables? There are no common values or fields between
the 2 tables

As noted, you'll need to post more information - but one possible problem is
your bracketing. Each object must be in its own brackets -

.[fieldname1] for example, rather than putting the period within the
brackets.

 

[KARL DEWEY]

Like I said, I'm not SQL conversant.
Open your update query in design view, click on VIEW - SQL View, highlight
all, copy, and paste in a post.

--
Build a little, test a little.

I am not at all converstant in SQL.

I am trying to do an Update Query that updates a field called "class" in a
table called "Assign Class".

I have a second table called "Values" where I have two fields called
"lovalue" and "hivalue".

I change the value of the fields in the table called Values via a form.

After I change the values, I want to run an Update Query to update the
"class" field in the Assign Class table.

I am looking for a way to write an expresstion in the Update Query criteria
like... if the value is between the lovalue and the hivalue in the Values
table, update the class field in the Assign Class table

I thought I could write the expression as....Between [Values].[lovalue] And
[Values].[hivalue], but it doesn't seem to work.

One thing I think is wrong is that I don't have the tables joined in the
query, but there are no common fields on which to create the relationship. Do
I need to join the tables. How?

Like I said, I'm not SQL conversant.

I appreciate everyone's help

Is it possible to refer to two specific field values in a single-record table
when searching for a range of values in a query?

I want to use the values in one table to update a field in another table

For example, the search criteria I've tried is "Between [table.fieldname1]
And [table.fieldname2]

I've tried a number of different combinations of the Between...And
expression and nothing seems to work.

Do I need to "join" the tables? There are no common values or fields between
the 2 tables

As noted, you'll need to post more information - but one possible problem is
your bracketing. Each object must be in its own brackets -

.[fieldname1] for example, rather than putting the period within the
brackets.