CODEWITHSUNDEEP
sqlms-accessms-access-2007
user2954166
user2954166

Reputation: 23

-3087 error in Access 2007

I have the following code:

SELECT
    Count(personnel.Position) AS countofposition,
    personnel.Position, 
    Year(DateAdd("m",-6,[Personnel.End_Date])) & "-"
        & Year(DateAdd("m",6,[Personnel.End_Date]))
FROM personnel 
GROUP BY Year(DateAdd("m",-6,[Personnel.End_Date])) & "-"
    & Year(DateAdd("m",6,[Personnel.End_Date]));

and i get the "Reserved error (-3087); there is no message for this error" message

I've checked the list of reserved words and I'm pretty sure I haven't used any. Rhe only thing I can think of is that the "Year" in "Year(DateAdd.... blahblahblahblahblah)" is messing me up

Edit: Also, personnel.position is a multi valued field Thoughts?

Upvotes: 1

Views: 547

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123549

I think this is what you're looking for:

For table [Personnel] with multi-value field [Position]:

ID  FirstName  LastName  Position        End_Date
--  ---------  --------  --------------  ----------
1   Gord       Thompson  CTO, President  2013-11-01
2   Anne       Elk       Vice-President  2013-11-01
3   P. T.      Gumby     Vice-President  2013-11-01

the query

SELECT 
    Count(Personnel.Position.Value) AS CountOfPosition_Value, 
    Personnel.Position.Value, 
    Year(DateAdd("m",-6,[Personnel.End_Date])) & "-" & Year(DateAdd("m",6,[Personnel.End_Date])) AS Expr1
FROM Personnel
GROUP BY 
    Personnel.Position.Value, 
    Year(DateAdd("m",-6,[Personnel.End_Date])) & "-" & Year(DateAdd("m",6,[Personnel.End_Date]));

returns

CountOfPosition_Value  Personnel.Position.Value  Expr1    
---------------------  ------------------------  ---------
                    1  CTO                       2013-2014
                    1  President                 2013-2014
                    2  Vice-President            2013-2014

Upvotes: 1

Related Questions