Recordset Failed Error

[RayMilhon]

I have a report in MS Access 2010 that uses some VBA Code to rank certain Accumulations in my Report. This is the error I'm getting



This is the VBA producing the error

        strtest = "Select star from tbl_stars Where submeasure = """ & "MRP" & """ and " & _
        CInt(Val(Me.txtbmi3) * 100) & " Between low and high "
        rsstars.Open strtest, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
        overallstar(1) = rsstars!star
        PartC(1) = rsstars!star
        Me.txtbmi4 = fStars(rsstars!star)
        rsstars.Close

The reason I'm having trouble with this is that in my report I have 14 different groupings all 14 run the same code. The only thing that changes is the text between "Where submeasure = """ and """ and ". In the sample above it's the "MRP". The code works 13 out of 14 times and only fails for this one section.

Here's the SQL for one that works



Select star from tbl_stars Where submeasure = "ABA - BMI within measure year or prior year" and 98 Between low and high

the result should be 5 and is

Here's the SQL for the one that doesn't

Select star from IStar_Matrix Where submeasurecode = "MRP" and 1 Between beg and end

the result should be 1 but I get the above error instead. If I run it in Query Analyzer it gives me the expected result. Any thoughts?

[RuralGuy]

Is there a [submeasurecode] field? Does it contain at least 1 record with "MRP" as a value?

[ranman256]

have you tried a query, not sql?

[John_G]

It may be because "End" is a reserved word and should not be used as a field (or control) name. Try changing it to something you know is not a reserved word.

[RayMilhon]

Yes there is a submeasurecode field as I stated the same query works 13 times out of 14 It only doesn't work when submeasure code is = "MRP" and yes there are records that equal that. The query works fine in Query Analyzer just won't work in code.

[Micron]

some thoughts:
- for one case, maybe Val is not returning an expected number (in which case, it would return 0)
- I would print the sql and check it, but I also favor single quotes over doubling up on doubles.

strtest = "Select star from tbl_stars Where submeasure = '" & "MRP" & "'" and " & _
CInt(Val(Me.txtbmi3) * 100) & " Between low and high"
debug.print strtest

You could restrict the print to when a certain criteria value (MRP?) is being used, or just scan 14 outputs in the immediate window. If nothing obvious shows, I'd test the variables and expressions in the immediate window in break mode, such as
?CInt(Val(Me.txtbmi3) * 100)

[RayMilhon]

I've checked the Value of Cint(Val(me.txtbmi3) * 100) = 1.44 low is 0 and high is 21.00 So the Query in Query Analyzer returns the value 1 as it's supposed to. It's just in the VBA Code I get the error.

[RuralGuy]

Hmm...there is always a chance there is some sort of corruption. You might try *importing* everything into a fresh new db and see if the issue follows.
https://btabdevelopment.com/how-to-i...-new-mdb-file/
This process tend to leave any corruption behind. Just a thought.

[Micron]

I've checked the Value of Cint(Val(me.txtbmi3) * 100) = 1.44 low is 0 and high is 21.00 So the Query in Query Analyzer returns the value 1 as it's supposed to. It's just in the VBA Code I get the error.

But does the entire sql statement look right when it's debugged.

[John_G]

The only thing that changes is the text between "Where submeasure = """ and """ and ".

But that is not the only thing that changes, from what you've shown. The SQL that doesn't work is selecting the field 'star' from a different table (IStar_Matrix vs. tblStars), uses a different field following WHERE (submeasurecode vs. submeasure), and uses different fields in the comparison (beg & end vs. low & high).

So, your two SQL statements are actually completely different, and there could be any number of things wrong.

It only doesn't work when submeasure code is = "MRP"

Is it submeasurecode (no space) or submeasure code (with a space), in which case use [submeasure code]?

Can you post a list of the fields in each table (IStar_Matrix & tblStars)? It might help us narrow down the problem.

I can't see why it would make a difference, but you could try DAO instead of ADO:

Dim rsstars as recordset
set rsstars = currentdb.openrecordset(sql)

(where using a field called 'end' DOES work)