returning current month and year

[John C.]

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???

 

[MikeJohnB]

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

 

[John W. Vinson]

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.