Syntax error with "Between" function in VBA/Access

[npatil]

I am getting a syntax error for the If statement in the foll. code.
where rs_cluster is a recordset. I am not able to figure out how to use the Between function to check for values of Field36 between a certain range shown below.

Dim curF15_2, curF31, curF35, curF36, curF46, curF57, curF58, curF61, curF62, curF110, curF117_2, curF124 As Variant

Dim i As Integer
i = 0

rs_cluster.MoveFirst
curF15_2 = rs_cluster!Field15_2
curF31 = rs_cluster!Field31
curF35 = rs_cluster!Field35
curF36 = rs_cluster!Field36
curF46 = rs_cluster!Field46
curF57 = rs_cluster!Field58
curF58 = rs_cluster!Field61
curF61 = rs_cluster!Field61
curF62 = rs_cluster!Field62
curF110 = rs_cluster!Field110
curF117_2 = rs_cluster!Field117_2
curF124 = rs_cluster!Field124

Do While Not rs_cluster.EOF
If rs_cluster!Field15_2 = currF15_2 And Left(rs_cluster!Field31, 1) = Left(curF31, 1) And rs_cluster!Field35 = curF35 And (((rs_cluster!Field36) Between((curF36 - 5) And ((curF36) + 5))) And (rs_cluster!Field46 Between(curF46 - 5) And (curF46 + 5)) And rs_cluster!Field58 = curF58 And rs_cluster!Field61 = curF61 And (rs_cluster![Field62] Between(curF62 - 10) And (curF62 + 10)) And (rs_cluster!Field110 = (Between(curF110 - 5) And (curF110 + 5))) And (rs_cluster!Field117_2 = (Between(curF117_2 - 5) And (curF117_2 + 5))) And rs_cluster!Field124 = curF124 Then


rs_cluster!Count = i
rs_cluster.MoveNext
Else
curF15_2 = rs_cluster!Field15_2
curF31 = rs_cluster!Field31
curF35 = rs_cluster!Field35
curF36 = rs_cluster!Field36
curF46 = rs_cluster!Field46
curF57 = rs_cluster!Field58
curF58 = rs_cluster!Field61
curF61 = rs_cluster!Field61
curF62 = rs_cluster!Field62
curF110 = rs_cluster!Field110
curF117_2 = rs_cluster!Field117_2
curF124 = rs_cluster!Field124

i = i + 1
End If
Loop

Thanks!

 

[DJkarl]

I don't believe the Between function is valid in VBA, only in SQL. You would need to either write your own version or use >= and <= for your comparisons.

 

[boblarson]

Between is not a function in Access. It is simply an operator. So you can use

Between x And y


But it looks like you have missing spaces if this

(curF62 - 10)

means subtract 10 from curF62. If that's the case then you should be able to use:

And (rs_cluster![Field62] Between (curF62 - 10) And (curF62 + 10))

Note the space where I put the red. That has to be there.