Avatar of PeterBaileyUk
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:

'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;
Microsoft AccessSQL

Avatar of undefined
Last Comment
Gustav Brock
Avatar of Rgonzo1971
Rgonzo1971

Hi,

A remark

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

Regards
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;")
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
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Blurred text
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.
See Pricing Options
Start Free Trial
Avatar of PeterBaileyUk
PeterBaileyUk

ASKER

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

ASKER

Dale's code slotted in and solved it
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

But how could:

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

produce this output as you stated initially:

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

Wishful thinking?

/gustav
Avatar of PeterBaileyUk
PeterBaileyUk

ASKER

maxsequence was populated with its value in some earlier code
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

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
Microsoft Access
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
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo