Year and Quarter used a query Parameters

[Joe B.]

All,

I am pretty new to Access 2007 and have a question with which I hope someone can help.

I currently have a table for Personnel Turnover. The columns are Year, Quarter, Job Title, Additions, and Subtractions.

I want the users to be able to enter a date range based on the year and quarter selected and return the additions and subtractions during that timeframe.

For example, the user enters the Start Year, Start Quarter, End Year and End Quarter as parameters. (2009, Quarter 1, 2011, Quarter 2). I need the Query to return all the activity from Q1 2009 to Q2 2011.

I have been able to use the "Between [Start Year] and [End Year]" but cannot figure out how to factor in the Quarters.

Thanking you in advance.

 

[plog]

I spent 15 minutes writing a post about why your request isn't possible in the way you have it (Quarters & Years instead of dates). Then I realized it wasn't impossible, just complicated. That sounded more fun, so I deleted what I wrote and gave it a shot.

Here's what I have:

WHERE ((IIf(4*([Year]-[Start Year])+[Quarter]-[Start Quarter]>=0 And 4*([End Year]-[Year])+[End Quarter]-[Quarter]>=0,1,0))=1);

Use that for your WHERE clause and it should work. Let me know.

Also, 'Quarter' and 'Year' are reserved words in access and shouldn't be names of fields in tables or queries.

 

[Joe B.]

Thanks. I will give it a try and let you know how it works.

 

[Joe B.]

Plog.

I received the following error message when establishing the WHERE clause:

"The expression you entered contains invalid syntax. You omitted and operand or operator, you entered an invalid character or comma, or you entered text without surrounding it in quotations."

Any ideas?

Thanks,
Joe B.

 

[plog]

Can you post your entire SQL string?

 

[Joe B.]

Here it is:


SELECT [Personnel Turnover].Year1, [Personnel Turnover].Quarter1, [Personnel Turnover].[Job Title], [Personnel Turnover].Departures
FROM [Personnel Turnover];
WHERE ((If(4*([Year1]-[Start Year])+[Quarter1]-[Start Quarter]>=0 And 4*([End Year]-[Year1])+[End Quarter]-[Quarter1]>=0,1,0))=1);

I changed the column names in the table to Year to Year1 and Quarter to Quarter1 as suggested in your earlier post.

Thanks

 

[speakers_86]

SELECT [Personnel Turnover].Year1, [Personnel Turnover].Quarter1, [Personnel Turnover].[Job Title], [Personnel Turnover].Departures
FROM [Personnel Turnover] 
WHERE ((If(4*([Year1]-[Start Year])+[Quarter1]-[Start Quarter]>=0 And 4*([End Year]-[Year1])+[End Quarter]-[Quarter1]>=0,1,0))=1);

All I did was remove the semi colon from the From clause. Semi colons indicate the end of an sql statement. Give that a try.

 

[Joe B.]

Removing the semi colon from the FROM clause worked.

I was able to run the query and enter the Start Year, the Start Quarter, the End Year and the End Quarter, but after entering the End Quarter, I received this error message:

The expression is typed incorrectly, or is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by adding parts of the expression to variables.
Here is the SQL now:

PARAMETERS [Enter Start Year] Text ( 255 ), [Enter Start Quarter] Text ( 255 ), [Enter End Year] Text ( 255 ), [Enter End Quarter] Text ( 255 );
SELECT [Personnel Turnover].Year1, [Personnel Turnover].Quarter1, [Personnel Turnover].[Job Title], [Personnel Turnover].Departures
FROM [Personnel Turnover]
WHERE (((IIf(4*([Year1]-[Enter Start Year])+[Quarter1]-[Enter Start Quarter]>=0 And 4*([Enter End Year]-[Year1])+[Enter End Quarter]-[Quarter1]>=0,1,0))=1));

Thanks so much for all the help.

 

[Joe B.]

All,

I got the query to work. The problem was I was entering "Quarter 1" rather than just "1". Now all I have to do is create a form to enter the dates.

Thanks for all the help.