Cannot find source of datatype error

[GraeagleBill]

I have in a forms module, a sub that is looking to count the instances of use of a book name use within the main table for a particular year.



Main table fields (QSermons): "SDate) string "yyyy.mm.dd"; SBook string

Books table (tblBooks): "BookName" string

Years table (tblSermonYrs): "SermonYear" string

The datatype error occurs with the DCount statement, where I'm looking at records in the main table for instances of use of the current book name in records for the specified year.

Private Sub cmdImport_Click()
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
'  Gather some stats
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Dim rsBks As DAO.Recordset
Dim rsYrs As DAO.Recordset


Dim intcount As Integer


Open "c:\TMS\SermonStats.txt" For Output As #1


Print #1, "Below are the stats for the sermons based"
Print #1, "on Bible Books by year of sermon."
Print #1, ""


Set rsYrs = DBEngine(0)(0).OpenRecordset("tblSermonYrs")
With rsYrs
.MoveFirst
    While Not .EOF
    Print #1, "SERMONS FOR YEAR " & !SermonYear
    
    Set rsBks = DBEngine(0)(0).OpenRecordset("tblbooks")
    rsBks.MoveFirst


        While Not rsBks.EOF       'Loop through the list of books to see if used in our current year of interest
        intcount = DCount("SermonID", "QSermons", "[SBook] = """ & rsBks!BookName & """" And "Left([SDate],4) = """ & !SermonYear & """")
        If intcount > 0 Then Print #1, right("   " & intcount, 3) & " - " & rsBks!BookName
        rsBks.MoveNext
        Wend
        
    rsBks.Close
    Set rsBks = Nothing
    Print #1, ""
    .MoveNext
    Wend
    
End With


rsYrs.Close
Set rsYrs = Nothing


Print #1, "Total sermon files inventory: " & Me.RecordsetClone.RecordCount
Close #1


MsgBox "Done"


End Sub

[June7]

Too many quote marks. Could use apostrophes and be easier to read. If you really want to use doubled quotes, replace each apostrophe with a pair.

intcount = DCount("SermonID", "QSermons", "[SBook] = '" & rsBks!BookName & "' And Left([SDate],4) = '" & !SermonYear & "'")

Why is SermonYear a text field? Even if a string, can probably do without delimiters as Access should do implicit conversion when number-like values are compared: = " & !SermonYear)
In the immediate window:
?Left("2004.12.01",4)=2004
True
?Left("2004.12.01",4)="2004"
True

Or force explicit conversion by wrapping in CInt() or Val() function.

Are you sure SBook has book name and not book ID? Searching on ID would be better.

[GraeagleBill]

Yes, I originally coded using apostrophes, as all the double quotes seemed silly and, as you say, apostrophes easier to read. Anyway, all of that didn't make a difference. I must have not even coded the apostrophes correctly, as your corrected DCount statement worked just fine.

And yes, normally searching on a Book's record ID would be preferable given a normalized DB, too bad I didn't have that to start with........... SIGH!

Thank you,
Bill

[June7]

Yes, apostrophes easier to read, however, will be an issue if your data has apostrophes as in Sophie's Choice. This is where doubled quotes have advantage. Now if your data has quote marks, there are ways to deal with that as well.

You may have read my earlier post before I edited with more info.

[Welshgasman]

Put the criteria into a string variable and debug.print it until you get it correct.