Re: Using a form to set criteria for a query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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 [Table].[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>

"David McKnight" <DavidMcKnight@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:54E1117A-F6C2-4452-B914-3F846957A8BE@xxxxxxxxxxxxxxxx
> 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;
>
> --
> David McKnight

.

Relevant Pages

  • Re: Need help with Code Please!!!
    ... I believe Case is a reserved word. ... the actual string that gets built at the end of the SQ1 build process. ... The message says Syntax error in query expression ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ...
    (microsoft.public.access.formscoding)
  • Re: OutputTo to Excel question
    ... I can't help you with the macro error message as I don't use macros. ... the result from a query. ... Save a query that pulls the same data as the report. ...
    (microsoft.public.access.macros)
  • Re: Background Web Queries flaws!
    ... There is no way to trap errors on a background query. ... There is no way to control the timeout setting. ... The basic problem is that IE spits an error message before returning control ... > hard coded a 5 minute limit into the web query system. ...
    (microsoft.public.excel.programming)
  • Re: Update Query only works first time
    ... I don't know either -- but that is what you error message says -- perhaps you are not using an updateable recordset or records were locked by something else like a form or query ... Microsoft Access MVP 2006 ... It works the first time, but then there is a very long error message with several reasons why it does not work. ...
    (microsoft.public.access.queries)
  • Re: SQL Compact Edition does not recognize parameter
    ... you might try looking in the debugger to see what the parameters collection looks like before you execute the query. ... assured that all computers have the same installation. ... It's always the same error message althought there are 35 parameters ... Only the hardware is different but on one PDA the problem exists ...
    (microsoft.public.sqlserver.ce)