Runtime Error: Missing Operator

  • Thread starter Thread starter Rebecca
  • Start date Start date
R

Rebecca

Hello,

I am having a lot of problems writing a 4 quarter moving
average function. I am getting errors identified below
and I do not know if it is because access cannot compare
the quarters the way that I have them written, or if I am
doing something totally wrong. Any assistanct you can
offer would be greatly appreciated!

I wrote a function in a module to compute a moving average
for effort for the current quarter and the past 3
quarters. I then created a query and typed in the
following expression:

Expr1: Effort2MovAvg([Actual_Effort_Ratio],[Quarter],4)

When I run the query, I get the following runtime
error '3075' Syntax error (missing operator) in query
expression 'Quarter<= Quarter 4 2001'. When I debug, the
row "Set rst = db.OpenRecordset(strSQ)" is highlighted and
when I place my cursor over the rst I get rst=nothing.

Here is the data in my table, as well as in the query:
Quarter Lower Goal, Hours Goal, Actual_Effort_Ratio
Quarter 4 2001, 0.85, 1.15, 1.16
Quarter 1 2002, 0.85, 1.15, 1.21
Quarter 2 2002, 0.85, 1.15, 1.01
Quarter 3 2002, 0.85 1.15, 1.04
Quarter 4 2002, 0.85 1.15 1.10

The goal is for the moving average of Quarter 3 2003 to be
((1.04 + 1.01 + 1.21 + 1.16)/4). Quarter 4 2001, Quarter
1 2002, and Quarter 2 2002 would be null because there are
not enough values to calculate their moving average.

Here is the function that I wrote in an Access Module to
calculate the moving average:

Function Effort2MovAvg(Actual_Effort_Ratio, quarterStart,
period As Integer)

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String
Dim ma As Double
Dim n As Integer

strSQL = "Select * from tblQuarterSummary "
strSQL = strSQL & "where Quarter <= " & quarterStart & " "
strSQL = strSQL & "order by Quarter"

Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL)
rst.MoveLast
For n = 0 To period - 1
If rst.BOF Then
Effort2MovAvg = 0
Exit Function
Else
ma = ma + rst.Fields("Actual_Effort_Ratio")
End If
rst.MovePrevious
Next n
rst.Close
Effort2MovAvg = ma / period

End Function
 
Looking at the error, you may need square brackets when
building the SQL statement. try;

strSQL = strSQL & "where Quarter <= [" & quarterStart
& "] "

Hope this helps.....
 
Thanks for the suggestion, but it doesn't look like that
is going to help. Once I put in the square brackets and
run the query I get the error message:
"Run-time error '3061' Too few parameters. Expected 1."

Do you have any other ideas? Thanks so much for your
assistance, I really appreciate it!

-----Original Message-----
Looking at the error, you may need square brackets when
building the SQL statement. try;

strSQL = strSQL & "where Quarter <= [" & quarterStart
& "] "

Hope this helps.....
-----Original Message-----
Hello,

I am having a lot of problems writing a 4 quarter moving
average function. I am getting errors identified below
and I do not know if it is because access cannot compare
the quarters the way that I have them written, or if I am
doing something totally wrong. Any assistanct you can
offer would be greatly appreciated!

I wrote a function in a module to compute a moving average
for effort for the current quarter and the past 3
quarters. I then created a query and typed in the
following expression:

Expr1: Effort2MovAvg([Actual_Effort_Ratio],[Quarter],4)

When I run the query, I get the following runtime
error '3075' Syntax error (missing operator) in query
expression 'Quarter<= Quarter 4 2001'. When I debug, the
row "Set rst = db.OpenRecordset(strSQ)" is highlighted and
when I place my cursor over the rst I get rst=nothing.

Here is the data in my table, as well as in the query:
Quarter Lower Goal, Hours Goal, Actual_Effort_Ratio
Quarter 4 2001, 0.85, 1.15, 1.16
Quarter 1 2002, 0.85, 1.15, 1.21
Quarter 2 2002, 0.85, 1.15, 1.01
Quarter 3 2002, 0.85 1.15, 1.04
Quarter 4 2002, 0.85 1.15 1.10

The goal is for the moving average of Quarter 3 2003 to be
((1.04 + 1.01 + 1.21 + 1.16)/4). Quarter 4 2001, Quarter
1 2002, and Quarter 2 2002 would be null because there are
not enough values to calculate their moving average.

Here is the function that I wrote in an Access Module to
calculate the moving average:

Function Effort2MovAvg(Actual_Effort_Ratio, quarterStart,
period As Integer)

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String
Dim ma As Double
Dim n As Integer

strSQL = "Select * from tblQuarterSummary "
strSQL = strSQL & "where Quarter <= " & quarterStart & " "
strSQL = strSQL & "order by Quarter"

Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL)
rst.MoveLast
For n = 0 To period - 1
If rst.BOF Then
Effort2MovAvg = 0
Exit Function
Else
ma = ma + rst.Fields("Actual_Effort_Ratio")
End If
rst.MovePrevious
Next n
rst.Close
Effort2MovAvg = ma / period

End Function
.
Click to expand...
.
Click to expand...
 
as quater is a text field - value should be in double quotes

strSQL = strSQL & "Quarter <= " & """" & quarterStart & """"" & " "

"""" - is 4 times ", this will produce one " in final string

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com




Rebecca said:
Thanks for the suggestion, but it doesn't look like that
is going to help. Once I put in the square brackets and
run the query I get the error message:
"Run-time error '3061' Too few parameters. Expected 1."

Do you have any other ideas? Thanks so much for your
assistance, I really appreciate it!

-----Original Message-----
Looking at the error, you may need square brackets when
building the SQL statement. try;

strSQL = strSQL & "where Quarter <= [" & quarterStart
& "] "

Hope this helps.....
-----Original Message-----
Hello,

I am having a lot of problems writing a 4 quarter moving
average function. I am getting errors identified below
and I do not know if it is because access cannot compare
the quarters the way that I have them written, or if I am
doing something totally wrong. Any assistanct you can
offer would be greatly appreciated!

I wrote a function in a module to compute a moving average
for effort for the current quarter and the past 3
quarters. I then created a query and typed in the
following expression:

Expr1: Effort2MovAvg([Actual_Effort_Ratio],[Quarter],4)

When I run the query, I get the following runtime
error '3075' Syntax error (missing operator) in query
expression 'Quarter<= Quarter 4 2001'. When I debug, the
row "Set rst = db.OpenRecordset(strSQ)" is highlighted and
when I place my cursor over the rst I get rst=nothing.

Here is the data in my table, as well as in the query:
Quarter Lower Goal, Hours Goal, Actual_Effort_Ratio
Quarter 4 2001, 0.85, 1.15, 1.16
Quarter 1 2002, 0.85, 1.15, 1.21
Quarter 2 2002, 0.85, 1.15, 1.01
Quarter 3 2002, 0.85 1.15, 1.04
Quarter 4 2002, 0.85 1.15 1.10

The goal is for the moving average of Quarter 3 2003 to be
((1.04 + 1.01 + 1.21 + 1.16)/4). Quarter 4 2001, Quarter
1 2002, and Quarter 2 2002 would be null because there are
not enough values to calculate their moving average.

Here is the function that I wrote in an Access Module to
calculate the moving average:

Function Effort2MovAvg(Actual_Effort_Ratio, quarterStart,
period As Integer)

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String
Dim ma As Double
Dim n As Integer

strSQL = "Select * from tblQuarterSummary "
strSQL = strSQL & "where Quarter <= " & quarterStart & " "
strSQL = strSQL & "order by Quarter"

Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL)
rst.MoveLast
For n = 0 To period - 1
If rst.BOF Then
Effort2MovAvg = 0
Exit Function
Else
ma = ma + rst.Fields("Actual_Effort_Ratio")
End If
rst.MovePrevious
Next n
rst.Close
Effort2MovAvg = ma / period

End Function
.
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top