Using a form to set criteria for a query

[David McKnight]

I would like to have an input field in a form that would set the criteria for
a query.

Say on the query below I have the form update [table5] so that [name] field
= "A".

This does not work as I have written as I get an error

"The specified field [name] could could refer to more than one table in the
FROM clause in your SQL statement."

but I'm not sure why .

SELECT Table1.Year, Table1.Name, (DAvg("[Value]","Table1","Name='" & [Name]
& "' AND Year BETWEEN " & [Year]-7 & " AND " & [Year]+0)) AS [Moving Avg
Value], ((DAvg("[Value]","Table1","Name='" & [Name] & "' AND Year BETWEEN " &
[Year]-7 & " AND " & [Year]-1))+(DAvg("[Value]","Table1","Name='" & [Name] &
"' AND Year BETWEEN " & [Year]-5 & " AND " &
[Year]-1))+(DAvg("[Value]","Table1","Name='" & [Name] & "' AND Year BETWEEN "
& [Year]-3 & " AND " & [Year]-1)))/3 AS [Wt Moving Avg Value]
FROM Table5 INNER JOIN Table1 ON Table5.Name = Table1.Name;

 

[Stefan Hoffmann]

hi David,

Say on the query below I have the form update [table5] so that [name] field
= "A".
This does not work as I have written as I get an error

CurrentDb.Execute "UPDATE [table5] SET [name] = 'A'", dbFailOnError
should work.

"The specified field [name] could could refer to more than one table in the
FROM clause in your SQL statement."

How are you updating? Post your code.

but I'm not sure why .

SELECT Table1.Year, Table1.Name, (DAvg("[Value]","Table1","Name='" & [Name]
& "' AND Year BETWEEN " & [Year]-7 & " AND " & [Year]+0)) AS [Moving Avg
Value], ((DAvg("[Value]","Table1","Name='" & [Name] & "' AND Year BETWEEN " &

^^^^^
The table qualifier is missing. Is it table5 or table1?

FROM Table5 INNER JOIN Table1 ON Table5.Name = Table1.Name;

mfG
--> stefan <--

 

[Jeff Boyce]

David

When I get that (kind of) error message, it means my query has more than one
table with a field named [Name]. You may need to preface your [Name] field
something like

.[Name].

Caution! Access treats the word "name" as a reserved word. By using this
as your field name, you are confusing both Access and yourself. Consider
changing that field name to something else, like:

CustomerName

 

[David McKnight]

Good, these fix what asked for but I also want to do a similar thing to a
[dateof] field
Say I want all dates between 8/15 & 12/15.

The table will not accept an input such as >8/15 <12/15 because it is not
formatted as a date. If I change the format of [table5][dateof] field I get
an missmatch error. As[table1][dateof] is formatted as a date.
Any suggestions?

--
David McKnight

David

When I get that (kind of) error message, it means my query has more than one
table with a field named [Name]. You may need to preface your [Name] field
something like

.[Name].

Caution! Access treats the word "name" as a reserved word. By using this
as your field name, you are confusing both Access and yourself. Consider
changing that field name to something else, like:

CustomerName

--
Regards

Jeff Boyce
<Office/Access MVP>

I would like to have an input field in a form that would set the criteria for
a query.

Say on the query below I have the form update [table5] so that [name] field
= "A".

This does not work as I have written as I get an error

"The specified field [name] could could refer to more than one table in the
FROM clause in your SQL statement."

but I'm not sure why .

SELECT Table1.Year, Table1.Name, (DAvg("[Value]","Table1","Name='" & [Name]
& "' AND Year BETWEEN " & [Year]-7 & " AND " & [Year]+0)) AS [Moving Avg
Value], ((DAvg("[Value]","Table1","Name='" & [Name] & "' AND Year BETWEEN " &
[Year]-7 & " AND " & [Year]-1))+(DAvg("[Value]","Table1","Name='" & [Name] &
"' AND Year BETWEEN " & [Year]-5 & " AND " &
[Year]-1))+(DAvg("[Value]","Table1","Name='" & [Name] & "' AND Year BETWEEN "
& [Year]-3 & " AND " & [Year]-1)))/3 AS [Wt Moving Avg Value]
FROM Table5 INNER JOIN Table1 ON Table5.Name = Table1.Name;

 

[Jeff Boyce]

David

The way I've seen to handle a range of dates is to use two fields (FromDate,
ToDate).

If you try to "stuff" more than one date (or a range) into a single field,
you'll have to come up with the functions that parse the text string into
actual dates, date ranges, etc. And besides, more than one fact in a field
(e.g., a date range) is not a good design decision for a database
table/field.

--
Regards

Jeff Boyce
<Office/Access MVP>

Good, these fix what asked for but I also want to do a similar thing to a
[dateof] field
Say I want all dates between 8/15 & 12/15.

The table will not accept an input such as >8/15 <12/15 because it is not
formatted as a date. If I change the format of [table5][dateof] field I get
an missmatch error. As[table1][dateof] is formatted as a date.
Any suggestions?

--
David McKnight

David

When I get that (kind of) error message, it means my query has more than one
table with a field named [Name]. You may need to preface your [Name] field
something like

.[Name].

Caution! Access treats the word "name" as a reserved word. By using this
as your field name, you are confusing both Access and yourself. Consider
changing that field name to something else, like:

CustomerName

--
Regards

Jeff Boyce
<Office/Access MVP>

I would like to have an input field in a form that would set the

criteria
for

a query.

Say on the query below I have the form update [table5] so that [name] field
= "A".

This does not work as I have written as I get an error

"The specified field [name] could could refer to more than one table

in
the

FROM clause in your SQL statement."

but I'm not sure why .

SELECT Table1.Year, Table1.Name, (DAvg("[Value]","Table1","Name='" & [Name]
& "' AND Year BETWEEN " & [Year]-7 & " AND " & [Year]+0)) AS [Moving Avg
Value], ((DAvg("[Value]","Table1","Name='" & [Name] & "' AND Year

BETWEEN
" &

[Year]-7 & " AND " & [Year]-1))+(DAvg("[Value]","Table1","Name='" &

[Name]
&

"' AND Year BETWEEN " & [Year]-5 & " AND " &
[Year]-1))+(DAvg("[Value]","Table1","Name='" & [Name] & "' AND Year BETWEEN "
& [Year]-3 & " AND " & [Year]-1)))/3 AS [Wt Moving Avg Value]
FROM Table5 INNER JOIN Table1 ON Table5.Name = Table1.Name;