Solved

type Mismatch variable syntax

Posted on 2014-02-26
13
336 Views
Last Modified: 2014-02-26
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

0
Comment
Question by:Karen Schaefer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
13 Comments
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 39889576
Hi,
maybe

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

Open in new window

Regards
0
 

Author Comment

by:Karen Schaefer
ID: 39889582
no that didn't work
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 39889591
What type of error did you get?
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:Karen Schaefer
ID: 39889602
Error 13 (Typemismatch)
0
 
LVL 18

Expert Comment

by:TobiasHolm
ID: 39889603
Try:
" WHERE (Quarter = '" & i & "') And (Year = " & Year(Date) & ")"

Open in new window

/Tobias
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39889607
try removing the & "" from the end of the string

Kelvin
0
 

Author Comment

by:Karen Schaefer
ID: 39889612
get the same error

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

Expert Comment

by:TobiasHolm
ID: 39889614
Or:
" WHERE (Quarter = '" & CStr(i) & "') And (Year = " & Year(Date) & ")"

Open in new window

0
 

Author Comment

by:Karen Schaefer
ID: 39889629
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
 
LVL 18

Expert Comment

by:TobiasHolm
ID: 39889646
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
 
LVL 22

Assisted Solution

by:Kelvin Sparks
Kelvin Sparks earned 250 total points
ID: 39889648
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
 
LVL 18

Accepted Solution

by:
TobiasHolm earned 250 total points
ID: 39889649
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
 

Author Closing Comment

by:Karen Schaefer
ID: 39889663
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

Featured Post

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question