If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
returning current month and year
I have a query that has a document date field [DocDate]. I created a new
field using a query that is: YearPurchase: Year([DOCDATE]) and the critera is Year(Now()). I'm pulling from 3 different tables all of which have [DocDate] fields. When I run the query without a table selected I get an error that states the field could reference more than one table. Then I select the table and I get any error stating: Extra ) in query expression. I run this with a query that has one table and it works fine. Any idea why this dosen't??? |
#2
|
|||
|
|||
returning current month and year
As I read your question, you have three tables all with DateDoc as fields????
I think that is where your problems lies, the query doesn't know which table to pull from. It will of course work when you have only one table as the source for the query. I think convension would mean re-naming the fields in two of the tables to something like DateDoc1 DateDoc2. Hope this helps and that I have read your question correctly??? Regards -- Advice to Posters. Check your post for replies or request for more information. Consider providing some feed back to the response you have recieved. Kindest Regards Mike B "John C." wrote: I have a query that has a document date field [DocDate]. I created a new field using a query that is: YearPurchase: Year([DOCDATE]) and the critera is Year(Now()). I'm pulling from 3 different tables all of which have [DocDate] fields. When I run the query without a table selected I get an error that states the field could reference more than one table. Then I select the table and I get any error stating: Extra ) in query expression. I run this with a query that has one table and it works fine. Any idea why this dosen't??? |
#3
|
|||
|
|||
returning current month and year
On Tue, 9 Dec 2008 10:23:06 -0800, John C.
wrote: I have a query that has a document date field [DocDate]. I created a new field using a query that is: YearPurchase: Year([DOCDATE]) and the critera is Year(Now()). I'm pulling from 3 different tables all of which have [DocDate] fields. When I run the query without a table selected I get an error that states the field could reference more than one table. Then I select the table and I get any error stating: Extra ) in query expression. I run this with a query that has one table and it works fine. Any idea why this dosen't??? Change Year([DOCDATE]) to Year([tablename].[DOCDATE]) If the docdate is (redundantly!!) the same in all three tables, pick any table. Actually for searching criteria, it is more efficient to use a criterion on the actual date field, rather than using the Year() function and searching that. To get all records in the current year use a criterion on DOCDATE of = DateSerial(Year(Date()), 1, 1) AND DateSerial(Year(Date()) + 1, 1, 1) Omit the part after the AND if there will never be DOCDATES in the future. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|