type Mismatch variable syntax

What is wrong with my syntax on

" WHERE (Quarter = '" & i & "') And [Year] = " & Year(Date) & "")

I doesn't seem to like the above statement - Note (i) is a varable
       For i = 1 To 4
            'rs1.MoveFirst
            If rs.Fields("FreezeQ" & (i)) = -1 Then
                If rs1.RecordCount = 0 Then
                    Dim strsql2 As String
                    strsql2 = "INSERT INTO tblSummaryQuartersFrozen" & _
                                  " SELECT tblSummary.*" & _
                                  " FROM tblSummary" & _
                                " WHERE (Quarter = '" & i & "') And [Year] = " & Year(Date) & ""
                  Debug.Print (strsql2)
                  curDB.Execute (strsql2)
                ElseIf rs1.Fields("Quarter") = (i) Then
                    rs.MoveNext
                ElseIf rs1.Fields("Quarter") <> i Then
                    curDB.Execute ("INSERT INTO tblSummaryQuartersFrozen" & _
                                    " SELECT tblSummary.*" & _
                                    " FROM tblSummary" & _
                                    " WHERE (Quarter = '" & i & "') And [Year] = " & Year(Date) & "")
                    rs.MoveNext
                End If
            End If
            Next i

Open in new window

Karen SchaeferBI ANALYSTAsked:
Who is Participating?
 
TobiasHolmCommented:
Also, try to remove one part at a time to see which one causes the error 13.
" WHERE (Quarter = '" & CStr(i) & "')"

Open in new window

" WHERE (Year = " & Year(Date) & ")"

Open in new window

0
 
Rgonzo1971Commented:
Hi,
maybe

   " WHERE (Quarter = " & i & ") And [Year] = " & Year(Date) & ""

Open in new window

Regards
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
no that didn't work
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Rgonzo1971Commented:
What type of error did you get?
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
Error 13 (Typemismatch)
0
 
TobiasHolmCommented:
Try:
" WHERE (Quarter = '" & i & "') And (Year = " & Year(Date) & ")"

Open in new window

/Tobias
0
 
Kelvin SparksCommented:
try removing the & "" from the end of the string

Kelvin
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
get the same error

that is what my original code was.  see second  version of the query statement
0
 
TobiasHolmCommented:
Or:
" WHERE (Quarter = '" & CStr(i) & "') And (Year = " & Year(Date) & ")"

Open in new window

0
 
Karen SchaeferBI ANALYSTAuthor Commented:
try removing the & "" from the end of the string
- no change

" WHERE (Quarter = '" & CStr(i) & "') And (Year = " & Year(Date) & ")"
- no change

Still getting type mismatch.
0
 
TobiasHolmCommented:
Sounds like the types ain't the same. So "Quarter"-type in the database should be compared to the same type of variable. I assumed it was a string, but it might be some other type. And same for "Year". Type in DB should match Year(Date) output.

Here you have some type conversations you can try:

CBool       Boolean       Any valid string or numeric expression.
CByte       Byte       0 to 255.
CCur       Currency       -922,337,203,685,477.5808 to 922,337,203,685,477.5807.
CDate       Date       Any valid date expression.
CDbl       Double       -1.79769313486231E308 to
-4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values.
CDec       Decimal       +/-79,228,162,514,264,337,593,543,950,335 for zero-scaled numbers, that is, numbers with no decimal places. For numbers with 28 decimal places, the range is
+/-7.9228162514264337593543950335. The smallest possible non-zero number is 0.0000000000000000000000000001.
CInt       Integer       -32,768 to 32,767; fractions are rounded.
CLng       Long       -2,147,483,648 to 2,147,483,647; fractions are rounded.
CSng       Single       -3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values.
CStr       String       Returns for CStr depend on the expression argument.
CVar       Variant       Same range as Double for numerics. Same range as String for non-numerics.
0
 
Kelvin SparksCommented:
In the line before the CurDB.Execute type Debug.print "INSERT INTO tblSummaryQuartersFrozen" & _
                                    " SELECT tblSummary.*" & _
                                    " FROM tblSummary" & _
                                    " WHERE (Quarter = '" & i & "') And [Year] = " & Year(Date) & ""

Run the code and see what you get in the Immediate window - paste that back here.


Kelvin
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
Tobais/Kelvin
That is exactly what I did and it turns out I was focusing on the wrong object (quarter)  & not the Year - is where is syntax was wrong.

                    curDB.Execute ("INSERT INTO tblSummaryQuartersFrozen" & _
                                    " SELECT tblSummary.*" & _
                                    " FROM tblSummary" & _
                                    " WHERE (Quarter = '" & i & "') And ((tblSummary.[Year])=Year(Date()))")


Thanks for the assist.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.