asked on

type Mismatch DCount value

I am getting type mismatch when I need to get a record count based on the following dCount. (see '>>>>>) I attempting do determine if records exists in the TblsummaryQuartersFrozen, where the quarter = (i) and if true then proceed to process queries..

I have tried changing the variable type, single, string, variant, etc - no luck - What am I missing:

   
Dim ExtractDate As Date
Dim curDB As DAO.Database
Dim strSQL As String, strSQL1 As String
Dim nCt As Variant
Dim rs As DAO.Recordset, rs1 As DAO.Recordset
Dim i As Integer

 strSQL = "Select Year, FreezeQ1, FreezeQ2, FreezeQ3, FreezeQ4 from tblcurrent"

    Set rs = curDB.OpenRecordset(strSQL)
   For i = 1 To 4
">>>>>>        nCt = Nz(DCount("Quarter", "tblSummaryQuartersFrozen", "Quarter = " & i And "Year =" & Year(Date)))
           If rs.Fields("CountOfQuarter") > 0 Then
           
           ' If nCt = 0 Then
                GoTo cont:
            ElseIf rs.Fields("FreezeQ" & (i)) = -1 Then
                curDB.Execute ("DELETE *" & _
                               " FROM tblSummary" & _
                               " WHERE Quarter = " & i And "Year =" & Year(Date))
                curDB.Execute ("INSERT INTO tblSummary" & _
                               "Select *" & _
                               " FROM tblSummaryQuartersFrozen" & _
                               " WHERE Quarter = " & i And "Year =" & Year(Date))
                rs.MoveNext
            End If
     Next i
cont:

Open in new window

jerryb30

nCt = Nz(DCount("Quarter", "tblSummaryQuartersFrozen", "Quarter = " & i & " And Year =" & Year(Date)))

Gustav Brock

As DCount returns 0 as a minimum, it should read:

Dim nCt As Long

nCt = DCount("*", "tblSummaryQuartersFrozen", "Quarter = " & i & " And Year = " & Year(Date) & "")

/gustav

Karen Schaefer

ASKER

I have tried both suggestions and I am still getting the same error message.

Dim curDB As DAO.Database
Dim strSQL As String, strSQL1 As String
Dim nCt As Long
Dim rs As DAO.Recordset, rs1 As DAO.Recordset
Dim i As Integer

    strSQL = "Select Year, FreezeQ1, FreezeQ2, FreezeQ3, FreezeQ4 from tblcurrent"

    Set rs = curDB.OpenRecordset(strSQL)
'    strSQL1 = "SELECT Count(Quarter) AS CountOfQuarter, Quarter" & _
'                " FROM tblSummaryQuartersFrozen" & _
'                " WHERE (((Year) = Year(Date)))" & _
'                " GROUP BY Quarter" & _
'                " HAVING (((Quarter)='1')) OR (((Quarter)='2')) OR (((Quarter)='3')) OR (((Quarter)='4'))"
'     Set rs1 = curDB.OpenRecordset(strSQL1)
   For i = 1 To 4
  ' Dim nCt As Long
nCt = DCount("*", "tblSummaryQuartersFrozen", "Quarter = " & i & " And Year = " & Year(Date) & "")

    nCt = Nz(DCount("Quarter", "tblSummaryQuartersFrozen", "Quarter = " & i & " And Year =" & Year(Date)))
   '     nCt = Nz(DCount("*", "tblSummaryQuartersFrozen", "Quarter = " & i And "Year =" & Year(Date)))

Open in new window

Karen Schaefer

ASKER

still looking for help.

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Karen Schaefer

ASKER

thanks for the input