Solved

type Mismatch variable syntax

Posted on 2014-02-26
13
334 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
  • 5
  • 4
  • 2
  • +1
13 Comments
 
LVL 49

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 49

Expert Comment

by:Rgonzo1971
ID: 39889591
What type of error did you get?
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

806 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