PeterBaileyUk
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:
SELECT Count(CapBuilds.Sequence) AS CountOfSequence, CapBuilds.Sequence
FROM CapBuilds
GROUP BY CapBuilds.Sequence
HAVING (((CapBuilds.Sequence)=4))
ORDER BY CapBuilds.Sequence DESC;
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
SELECT Count(CapBuilds.Sequence) AS CountOfSequence, CapBuilds.Sequence
FROM CapBuilds
GROUP BY CapBuilds.Sequence
HAVING (((CapBuilds.Sequence)=4))
ORDER BY CapBuilds.Sequence DESC;
Last Comment
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;")
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;")
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.
and you can eliminate the Order By clause, since you are filtering the field with a value
Is the error occurring on line 15?
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
and that worked are you happy for me to share the points?
ASKER
Dale's code slotted in and solved it
But how could:
" HAVING (((CapBuilds.Sequence) = '" & MaxSequence & "'))" _
produce this output as you stated initially:
HAVING (((CapBuilds.Sequence)=4))
Wishful thinking?
/gustav
" HAVING (((CapBuilds.Sequence) = '" & MaxSequence & "'))" _
produce this output as you stated initially:
HAVING (((CapBuilds.Sequence)=4))
Wishful thinking?
/gustav
ASKER
maxsequence was populated with its value in some earlier code
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)
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
A remark
I do not understand ORDER BY CapBuilds.Sequence if
HAVING (((CapBuilds.Sequence)=4))
Regards