Re: Microsoft Access Expression Problem

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Since you have two tables with a field named Date then you need to qualify the reference to the Date field by adding the table name

Year(DateAdd("m",3,T1.[Date]))

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


j.boswell@xxxxxxxxxxx wrote:
When I try the following:

SELECT T1.SITE_NO, T1.DATE, T1.FLOW, Avg(T2.FLOW) AS
Avg7Day,Year(DateAdd("m",3,[DATE])) AS FY
FROM [USGS RAW] AS T1 INNER JOIN [USGS RAW] AS T2 ON T1.SITE_NO =
T2.SITE_NO
WHERE (((T1.SITE_NO)="03345000") AND ((T1.DATE)>=([T2].[DATE]-3) And
(T1.DATE)<=([T2].[DATE]+3)))
GROUP BY T1.SITE_NO, T1.DATE, T1.FLOW, Year(DateAdd("m",3,[DATE])) AS
FY;

I receive this error: "Syntax error (missing operator) in query
expression 'Year(DateAdd("m",3,[DATE])) AS FY'.

I think this is referring to my GROUP BY statement. So I removed the
'Year(DateAdd("m",3,[DATE])) AS FY' in the GROUP BY statement. I've
tried replacing it with a few things but keep receiving this error

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


.

Relevant Pages

  • Re: Passing variable values between code modules.
    ... When you reference the "public" variable that is another form's module, ... must qualify it by including the reference to the module in which it's ... intNewContestID = intNewContestID + 1 ... MsgBox ("You can not move this team deffnition to the " & ...
    (microsoft.public.access.formscoding)
  • Re: Another tricky selection query
    ... that is one advantage of a newsgroup. ... John Spencer ... I need a query in which the data from the previous record is referenced. ... I need to reference the end date of the ...
    (microsoft.public.access.queries)
  • Re: Of mice and men
    ... browsers do not. ... you have to qualify everything up to the document element. ... which you can use to reference your various input elements - ...
    (comp.lang.cobol)
  • Re: Public Variables
    ... > When I go to another form and use a msgbox to display the variable's ... qualified by a reference to the instance of the object that contains it. ... If you don't want to have to qualify your references that way, ...
    (microsoft.public.access.formscoding)
  • Re: Access 2002 queries
    ... I can speculate that Access is counting one for the reference ... I don't have access to the code that is used to convert the query to machine ... My question is does access count the table name in brackets as well as the ... "John Spencer" wrote: ...
    (microsoft.public.access.queries)