type Mismatch variable syntax

Karen Schaefer
Karen Schaefer used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
Hi,
maybe

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

Open in new window

Regards
Karen SchaeferBI ANALYST

Author

Commented:
no that didn't work
Top Expert 2016

Commented:
What type of error did you get?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Karen SchaeferBI ANALYST

Author

Commented:
Error 13 (Typemismatch)
Try:
" WHERE (Quarter = '" & i & "') And (Year = " & Year(Date) & ")"

Open in new window

/Tobias
try removing the & "" from the end of the string

Kelvin
Karen SchaeferBI ANALYST

Author

Commented:
get the same error

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

Open in new window

Karen SchaeferBI ANALYST

Author

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.
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.
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
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

Karen SchaeferBI ANALYST

Author

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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial