dcount problem

[Guest]

I'm doing a count on a table and it's not got the criteria right. It's just
doing a count on the whole table.


strSql_count = "([SDNTCWRK.UNIUCASC] = '" & Uni_UCAS_Code & "')" & _
" AND ([SDNTCWRK.INTRNCOD] = '" & Dep_Code & "')" & _
" AND ([SDNTCWRK.CID] = '" &
..Fields("STUDENT.CID").Value & "')" & _
" AND ([SDNTCWRK.SUBJCODE] = '" & Subject_Code & "')" & _
" AND ([SDNTCWRK.CWRKNAME] = '" & Coursework_Name &
"')" & _
" AND ([SDNTCWRK.YEAR] = '" & YEAR & "')"
MsgBox (strSql_count)
rec_count = DCount("*", "SDNTCWRK", strSql_count)
MsgBox (rec_count)

Any ideas?

 

[John W. Vinson]

I'm doing a count on a table and it's not got the criteria right. It's just
doing a count on the whole table.


strSql_count = "([SDNTCWRK.UNIUCASC] = '" & Uni_UCAS_Code & "')" & _
" AND ([SDNTCWRK.INTRNCOD] = '" & Dep_Code & "')" & _
" AND ([SDNTCWRK.CID] = '" &
.Fields("STUDENT.CID").Value & "')" & _
" AND ([SDNTCWRK.SUBJCODE] = '" & Subject_Code & "')" & _
" AND ([SDNTCWRK.CWRKNAME] = '" & Coursework_Name &
"')" & _
" AND ([SDNTCWRK.YEAR] = '" & YEAR & "')"
MsgBox (strSql_count)
rec_count = DCount("*", "SDNTCWRK", strSql_count)
MsgBox (rec_count)

Any ideas?

This is not a query - it's some VBA code building up some incomplete pieces of
a query. Try changing the line

MsgBox (strSQL_Count)

to

Debug.Print strSQL_Count

Run the code, and copy and paste the SQL string that gets generated into the
SQL view of a new query. Take a look at the criteria - do they look right?
Does the query get your desired results? If you need help, post the contents
of strSQL_Count to a message here.

John W. Vinson [MVP]