Access doesn't like my coding but gives the right answer

[Bob Blooms]

Attempting to determine what the actual cost of a salary step increase will be during the current fiscal year. The approach used is to determine the current salary, then the new salary. Their difference would be the annualized increase. However, the increase will not be in effect for the complete fiscal year. To determine the percentage of the year that it will be in force I use DateDiff using the salary increase date (Sal Con Date) and subtract it from the fiscal year end date which is expressed as 41455 (06/30/2013). The query run ok, but I get that nasty inbox asking for a parameter. How do I overcome this issue? Or better how do I write good code to meet my objective?

SELECT tblCS3Compilation.Name, tblCS3Compilation.[Sal Con Date], DateDiff("d",[Sal Con Date],41455) AS Days, [Days]/365 AS StepIncreasePercent
FROM tblCS3Compilation INNER JOIN tblSalaryTables ON (tblCS3Compilation.Step = tblSalaryTables.Step) AND (tblCS3Compilation.JobClass = tblSalaryTables.JobClass)
GROUP BY tblCS3Compilation.Name, tblCS3Compilation.[Sal Con Date], DateDiff("d",[Sal Con Date],41455), [Days]/365;

[Rod]

Which term does the SQL think is a parameter: [Sal Con Date] or [Days]? I'd be surprised if it's the former as I guess this is the name of a table field.

If it's not too late do not use field names with special characters, particularly spaces. I know it's a 'selling point' for Access that you can but you will rue the fact later in development.

[pbaldy]

I assume it's Days, because you use it in the GROUP BY clause. I'd take that clause out since you don't appear to be aggregating anything.

[Bob Blooms]

Rod, I agree "NO SPACES" But they make it look better! Will spend some time on cleaning up the field names

Paul, got rid of the Group by clause. It works.

Thanks gentlemen

Now I've got to figure out how to make the end of the fiscal year a variable.

[pbaldy]

I'd probably use a form for the user to enter a date to make it more flexible. It's fairly easy to calculate a fiscal year end from a given date. You can use a formula or a function following similar logic. Here's a function I used in an old db for a university:

Public Function SchoolYearStart(TestDate As Date) As Date
  If TestDate < DateSerial(Year(TestDate), 7, 1) Then
    SchoolYearStart = DateSerial(Year(TestDate) - 1, 7, 1)
  Else
    SchoolYearStart = DateSerial(Year(TestDate), 7, 1)
  End If
End Function

[Rod]

I too wondered about the aggregate query but thought that you were doing this for many people at the same time.

Now I've got to figure out how to make the end of the fiscal year a variable.

Well, you can make it a parameter! Where does the end-of-year value exist now?

[Bob Blooms]

Paul, I am trying to keep user intervention at a minimal. I was thinking of a table of fiscal-year-end-dates. When the application is activated the user inputs the FY with the analysis will be performed. The way I created the formula it requires a serial date.