asked on

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

Rgonzo1971

Hi,
maybe

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

Open in new window

Regards

Karen Schaefer

ASKER

no that didn't work

Rgonzo1971

What type of error did you get?

Karen Schaefer

ASKER

Error 13 (Typemismatch)

TobiasHolm

Try:

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

Open in new window

/Tobias

Kelvin Sparks

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

Kelvin

Karen Schaefer

ASKER

get the same error

that is what my original code was. see second version of the query statement

TobiasHolm

Or:

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

Open in new window

Karen Schaefer

ASKER

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.

TobiasHolm

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.

SOLUTION

Kelvin Sparks

membership

This solution is only available to members.

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

Start Free Trial

ASKER CERTIFIED SOLUTION

TobiasHolm

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

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.