Criteria mismatch in access sql

PeterBaileyUk
PeterBaileyUk used Ask the Experts™
on
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;
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Rgonzo1971
Top Expert 2016

Commented:
Hi,

A remark

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

Regards
Rey Obrero (Capricorn1)
Top Expert 2016

Commented:
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

Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Find out now!
Rey Obrero (Capricorn1)
Top Expert 2016

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

Commented:
Is the error occurring on line 15?
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
I would start out by separating the sql string from the Openrecordset method.  I would also replace the HAVING clause with a WHERE clause
Dim strSQL as string
strSQL = "SELECT Count(CapBuilds.Sequence) AS CountOfSequence, CapBuilds.Sequence" _
       & " FROM CapBuilds" _
       & " WHERE (CapBuilds.Sequence = '" & MaxSequence & "')" _
       & " GROUP BY CapBuilds.Sequence"
debug.print strsql
Set RstBuildsMinusZero = db.OpenRecordset(strSQL, , dbfailonerror)

Open in new window

This will allow you to cut the SQL string from the immediate window, paste it into a new query and determine the problem.  Based upon the error you provided, I'm inclined to agree with Rey that [Sequence] is a number, in which case you would use:
strSQL = "SELECT Count(CapBuilds.Sequence) AS CountOfSequence, CapBuilds.Sequence" _
       & " FROM CapBuilds" _
       & " WHERE (CapBuilds.Sequence = " & MaxSequence & ")" _
       & " GROUP BY CapBuilds.Sequence"

Open in new window

PeterBaileyUk

Author

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

Author

Commented:
Dale's code slotted in and solved it
Gustav BrockCIO
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
But how could:

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

produce this output as you stated initially:

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

Wishful thinking?

/gustav
PeterBaileyUk

Author

Commented:
maxsequence was populated with its value in some earlier code
Gustav BrockCIO
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial