asked on

Criteria mismatch in access sql

I have a query that works in the query grid but I am having trouble making it work as a dao.recordset: assistance would be appreciated.

access says error 3464 data type mismatch

vba:

'get Capbuilds current recordcount
Set RstBuildsMinusZero = db.OpenRecordset("SELECT Count(CapBuilds.Sequence) AS CountOfSequence, CapBuilds.Sequence" _
& " FROM CapBuilds" _
& " GROUP BY CapBuilds.Sequence" _
& " HAVING (((CapBuilds.Sequence) = '" & MaxSequence & "'))" _
& " ORDER BY CapBuilds.Sequence DESC;")






With RstBuildsMinusZero
.MoveLast
    CapBuildsMinusZero = .Fields("CountOfSequence").Value
End With

Open in new window

here is the actual sql from the querygrid

SELECT Count(CapBuilds.Sequence) AS CountOfSequence, CapBuilds.Sequence
FROM CapBuilds
GROUP BY CapBuilds.Sequence
HAVING (((CapBuilds.Sequence)=4))
ORDER BY CapBuilds.Sequence DESC;

Rgonzo1971

Hi,

A remark

I do not understand ORDER BY CapBuilds.Sequence if
HAVING (((CapBuilds.Sequence)=4))

Regards

Rey Obrero (Capricorn1)

Sequence could be Number data type

Set RstBuildsMinusZero = db.OpenRecordset("SELECT Count(CapBuilds.Sequence) AS CountOfSequence, CapBuilds.Sequence" _
& " FROM CapBuilds" _
& " GROUP BY CapBuilds.Sequence" _
& " HAVING (((CapBuilds.Sequence) = " & MaxSequence & "))" _
& " ORDER BY CapBuilds.Sequence DESC;")

Anthony Berenguel

Is CapBuilds.Sequence a text field? I'm asking because you're comparing its values to a string which will result in the error you're getting if CapBuilds.Sequence is not a text field.

Rey Obrero (Capricorn1)

and you can eliminate the Order By clause, since you are filtering the field with a value

Anthony Berenguel

Is the error occurring on line 15?

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PeterBaileyUk

ASKER

and that worked are you happy for me to share the points?

PeterBaileyUk

ASKER

Dale's code slotted in and solved it

Gustav Brock

But how could:

" HAVING (((CapBuilds.Sequence) = '" & MaxSequence & "'))" _

produce this output as you stated initially:

HAVING (((CapBuilds.Sequence)=4))

Wishful thinking?

/gustav

PeterBaileyUk

ASKER

maxsequence was populated with its value in some earlier code

Gustav Brock

Then next time adjust your code for easy debugging:

Dim SQL As String

SQL = "SELECT Count(CapBuilds.Sequence) AS CountOfSequence, CapBuilds.Sequence" _
& " FROM CapBuilds" _
& " GROUP BY CapBuilds.Sequence" _
& " HAVING (((CapBuilds.Sequence) = '" & MaxSequence & "'))" _
& " ORDER BY CapBuilds.Sequence DESC;"
Debug.Print SQL

'get Capbuilds current recordcount
Set RstBuildsMinusZero = db.OpenRecordset(SQL)

Open in new window

/gustav