Error 3071 when trying to do query

[Elizabeth- MDC]

Here's the code:

Public Function Points_Formula(CustomerID As Long, StoreID As Long, Dollars
As Long) As Integer
Dim AddPoints As QueryDef
Dim db As Database
Dim CurrentRules As Recordset
Set db = CurrentDb

Set CurrentRules = db.OpenRecordset("SELECT [RuleID], [RuleName],
[PointperDollar], [PointLimit], [StoreSpecific], " & _
"[StoreID], [StartDate], [EndDate] FROM Rules " & _
"WHERE ([StartDate] <= #" & Date & "# AND [EndDate] >= #" & Date & "# AND
([StoreSpecific] = False OR [StoreID] = " & StoreID & "));")

With CurrentRules
.MoveLast
.MoveFirst
Do While Not .EOF

MsgBox !RuleName
.MoveNext
Loop
.Close
End With
End Function

I know it's sloppy, I'm just trying to figure the basics out. I get the
error at .MoveFirst. If I remove that line, I get the same error at
..MoveNext.

No spaces in names of fields. Table only has two rows in it. It works if
the second row is filtered out by the StartDate and/or EndDate.

 

[Dale Fye]

Elizabeth,

I'm not sure what error 3071 says, so you might want to include the message
along with the number when you run into these errors (so those of us trying
to help don't have to look it up).

You don't even need the .movelast and .movefirst commands. All that really
does for you is move to the last record in the recordset, then move back to
the first (which is fine if you need to use the .recordCount property of the
recordset, but not necessary).

The first thing I would do is build your SQL string as a string, then put it
in the Open Recordset method. The advantage of this technique is that you
can print out the SQL and then copy it into a query and test it in the Query
window. I've also replaced "[StoreSpecific] = False" with "[StoreSpecific] =
0"

Dim strSQL as String
strSQL = "SELECT [RuleID], [RuleName], [PointperDollar], " _
& "[PointLimit], [StoreSpecific], [StoreID], " _
& "[StartDate], [EndDate] " _
& "FROM Rules " _
& "WHERE ([StartDate] <= #" & Date() & "# AND " _
& "[EndDate] >= #" & Date() & "# AND " _
& "([StoreSpecific] = 0 OR " _
& "[StoreID] = " & StoreID & ")" _
& ")"

debug.print strSQL
Set CurrentRules = db.OpenRecordset(strsql, , dbFailOnError)

Do While Not CurrentRules.EOF

MsgBox currentRules.RuleName
CurrentRules.MoveNext

Loop

CurrentRules.Close
Set CurrentRules = Nothing

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Douglas J. Steele]

?AccessError(3071)
This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables.

That having been said, I can't see anything too complex in that SQL. The
only thing that occurs to me is might StoreID be a text field in the table?

It also might be a good idea to use

Dim CurrentRules As DAO.Recordset

although that's not likely the error (if it were, the error message would
likely be about a missing parameter)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Elizabeth,

I'm not sure what error 3071 says, so you might want to include the
message
along with the number when you run into these errors (so those of us
trying
to help don't have to look it up).

You don't even need the .movelast and .movefirst commands. All that
really
does for you is move to the last record in the recordset, then move back
to
the first (which is fine if you need to use the .recordCount property of
the
recordset, but not necessary).

The first thing I would do is build your SQL string as a string, then put
it
in the Open Recordset method. The advantage of this technique is that you
can print out the SQL and then copy it into a query and test it in the
Query
window. I've also replaced "[StoreSpecific] = False" with
"[StoreSpecific] =
0"

Dim strSQL as String
strSQL = "SELECT [RuleID], [RuleName], [PointperDollar], " _
& "[PointLimit], [StoreSpecific], [StoreID], " _
& "[StartDate], [EndDate] " _
& "FROM Rules " _
& "WHERE ([StartDate] <= #" & Date() & "# AND " _
& "[EndDate] >= #" & Date() & "# AND " _
& "([StoreSpecific] = 0 OR " _
& "[StoreID] = " & StoreID & ")" _
& ")"

debug.print strSQL
Set CurrentRules = db.OpenRecordset(strsql, , dbFailOnError)

Do While Not CurrentRules.EOF

MsgBox currentRules.RuleName
CurrentRules.MoveNext

Loop

CurrentRules.Close
Set CurrentRules = Nothing

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Here's the code:

Public Function Points_Formula(CustomerID As Long, StoreID As Long,
Dollars
As Long) As Integer
Dim AddPoints As QueryDef
Dim db As Database
Dim CurrentRules As Recordset
Set db = CurrentDb

Set CurrentRules = db.OpenRecordset("SELECT [RuleID], [RuleName],
[PointperDollar], [PointLimit], [StoreSpecific], " & _
"[StoreID], [StartDate], [EndDate] FROM Rules " & _
"WHERE ([StartDate] <= #" & Date & "# AND [EndDate] >= #" & Date & "# AND
([StoreSpecific] = False OR [StoreID] = " & StoreID & "));")

With CurrentRules
.MoveLast
.MoveFirst
Do While Not .EOF

MsgBox !RuleName
.MoveNext
Loop
.Close
End With
End Function

I know it's sloppy, I'm just trying to figure the basics out. I get the
error at .MoveFirst. If I remove that line, I get the same error at
.MoveNext.

No spaces in names of fields. Table only has two rows in it. It works
if
the second row is filtered out by the StartDate and/or EndDate.