Solved Compile Error: Type Missmatch (1 Viewer)

[5hadow]

Good day,

Can someone identify the reason for type mismatch error I'm getting for following code:

If DCount("fldAuditDate", rstNewTable, "Format$(fldAuditDate,'yyyymm')='" & Format$(nxtInspection, "yyyymm") & "'") < intInsp Then

Not sure if it's something obvious or not. I've tried putting # everywhere and it still didn't work.

 

[Gasman]

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?

Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format

Numbers do not need anything

I have used this constant in the past, as I always forget this particular format with the backslashes.

Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.

 

[arnelgp]

you have a Variable nxtInspection there, do you have them in the form?
also do you have Null values on fldAuditDate field?

 

[MajP]

Also is rstNewTable a string table name? It looks like a name someone would give a recordset. Will not work on recordset.

 

[5hadow]

you have a Variable nxtInspection there, do you have them in the form?
also do you have Null values on fldAuditDate field?

No, nxtInspection not on form, just in the loop code
Yes, fldAuditDate is Null as this is a part of loop code which empties table and ands new values. It is attempting to calculate next fldAuditDate. Here is a line right before this one:
nxtInspection = Nz(rstNewTable!fldAuditDue, Date) 'Sets next IQA date
So, do i need to put in .Update before the code continues?

 

[jdraw]

Try enclosing "rstNewTable" in quotes.

 

[5hadow]

Also is rstNewTable a string table name? It looks like a name someone would give a recordset. Will not work on recordset.

Yes, it's a recordset.

 

[5hadow]

Here's what I'm trying to do:

Function SCHD2()
Dim bolOK As Boolean
Dim db As DAO.Database
Dim rstIQA As DAO.Recordset
Dim rstNewTable As DAO.Recordset
Dim strIQA As String
Dim nxtInspection As Date
Dim intInsp As Integer
intInsp = Forms!frmSCHED!txtInsp.value
strIQA = "SELECT tblWIUnion.fldDocID, tblWIUnion.fldWISerial, [Work Instructions].DocTitle, [Work Instructions].PA, [Work Instructions].LastAudit, tblWIUnion.fldIQADue, tblWIUnion.fldIQA " & _
    "FROM [Work Instructions] INNER JOIN tblWIUnion ON [Work Instructions].ID = tblWIUnion.fldWIID " & _
    "WHERE (((tblWIUnion.fldWISerial) Is Not Null));"
Set db = CurrentDb
Set rstIQA = db.OpenRecordset(strIQA, dbOpenDynaset)
Set rstNewTable = db.OpenRecordset("tblIQASchedule", dbOpenDynaset)
Call EmptyTable("tblIQASchedule")
With rstNewTable
    rstIQA.MoveFirst
    Do Until rstIQA.EOF
        .AddNew
            ![fldWISerial] = rstIQA![fldWISerial]
            ![fldTitle] = rstIQA![DocTitle]
            ![fldPA] = rstIQA![PA]
            ![fldLastAudit] = rstIQA![LastAudit]
            ![fldAuditDue] = rstIQA![fldIQADue]
            ![fldAuditDate] = rstIQA![fldIQA]
        bolOK = False
        nxtInspection = Nz(rstNewTable!fldAuditDue, Date) 'Sets next IQA date
        Do Until bolOK
            If DCount("fldAuditDate", "tblIQASchedule", "Format$(fldAuditDate,'yyyymm')='" & Format$(nxtInspection, "yyyymm") & "'") < intInsp Then 'If number of next IQA dates per month is less than set by user.
                If DCount("1", "tblMonth", "Month(fldMonth) = " & Month(nxtInspection) & " And fldactive = -1") <> 0 Then 'If other criteria set by user is met.
                    bolOK = True 'Date above is then accepted.
                End If
            End If
            If Not bolOK Then 'If user criteria is not met
                nxtInspection = DateAdd("m", -1, nxtInspection) 'Set next IQA date to one month less and try again.
            End If
        Loop
        If nxtInspection < Date Then
            nxtInspection = Date
        End If
        rstNewTable!fldAuditDate = nxtInspection
        .Update
        rstIQA.MoveNext
    Loop
    .Close
End With
Set rstNewTable = Nothing
db.Close
Set db = Nothing
DoCmd.Requery

Edit:

I've used the code above with a minor adjustment:
Instead of:
If DCount("fldAuditDate", rstNewTable, "Format$(fldAuditDate,'yyyymm')='" & Format$(nxtInspection, "yyyymm") & "'") < intInsp Then
This:
If DCount("fldAuditDate", "tblIQASchedule", "Format$(fldAuditDate,'yyyymm')='" & Format$(nxtInspection, "yyyymm") & "'") < intInsp Then
It works like this!

 

[The_Doc_Man]

This is the equivalent to the WHERE clause fragment (i.e. the Criteria argument) being fed to the DCount.

"Format$(fldAuditDate,'yyyymm')='" & Format$(nxtInspection, "yyyymm") & "'"

Since this is not in a query but is instead in a VBA loop, I would advise a "divide and conquer" strategy.

If IsNull( fldAuditDate ) then
    GO DO SOMETHING ELSE
End If

strAuditDate = "#" & Format$( fldAuditDate, "yyyymm" ) & "#"
strInspection = "#" & Format$( nxtInspection, "yyyymm" ) & "#"
If DCount("fldAuditDate", rstNewTable, "'" & strAuditDate & "'='" & strInspection & "'") < intInsp Then...

In SQL, this would be different, but for VBA, this should have the desired effect and would be easier to debug.

EDIT: You posted a longer code sequence that will have similar constructs in it. Divide and conquer for the VBA case.

 

[MajP]

That is different than what was posted originally. In this code you pass in the name of the table that the recorset is based. That would work.

If DCount("fldAuditDate", "tblIQASchedule", "Format$(fldAuditDate,'yyyymm')='" & Format$(nxtInspection, "yyyymm") & "'") < intInsp Then 'If number of next IQA dates per month is less than set by user.

The first example would give a datatype mismatch because the dlookup needs a string "Domain" and cannot be passed an object. Does this method also give an error?

FYI. What you are doing in the criteria should work and does not need to be a date like others are suggesting since you are converting to a string on both sides of the comparison.