By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,155 Members | 2,518 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,155 IT Pros & Developers. It's quick & easy.

Type Mismatch Error

P: 84
The SQL below is returning a type mismatch error and I cannot figure out why.
The ID field in RANDOM_RECORD is a number field. Any one have any idea as to why i am getting the error?

Dim stRecordTotal As String
Dim stRecordResult as String
Dim stRecord As String

stRecordTotal = 0
stRecordResult = 0

stRecordTotal = "SELECT Count(RANDOM_RECORDS.ID) " & _
"FROM RANDOM_RECORDS "

stRecordResult = (stRecordTotal / 4)

stRecord = "INSERT INTO RESULTS ( stRecordResult) "

DoCmd.RunSQL stRecord
Sep 10 '07 #1
Share this Question
Share on Google+
5 Replies


Expert 100+
P: 296
The SQL below is returning a type mismatch error and I cannot figure out why.
The ID field in RANDOM_RECORD is a number field. Any one have any idea as to why i am getting the error?

Dim stRecordTotal As String
Dim stRecordResult as String
Dim stRecord As String

stRecordTotal = 0
stRecordResult = 0

stRecordTotal = "SELECT Count(RANDOM_RECORDS.ID) " & _
"FROM RANDOM_RECORDS "

stRecordResult = (stRecordTotal / 4)

stRecord = "INSERT INTO RESULTS ( stRecordResult) "

DoCmd.RunSQL stRecord
I really don't know VBA, but maybe it has to do with the fact that you're trying to perform math on variables that you declared as strings?? Try changing it to Dim stRecordTotal As Number (not sure if 'Number' is correct...as I said I really don't know VBA, but I hope you get the picture of what I'm trying to say). If that doesn't work, let us know what the error message is and do a debug and let us know which line of code it is having problems with.
Sep 10 '07 #2

FishVal
Expert 2.5K+
P: 2,653
The SQL below is returning a type mismatch error and I cannot figure out why.
The ID field in RANDOM_RECORD is a number field. Any one have any idea as to why i am getting the error?

Dim stRecordTotal As String
Dim stRecordResult as String
Dim stRecord As String

stRecordTotal = 0
stRecordResult = 0

stRecordTotal = "SELECT Count(RANDOM_RECORDS.ID) " & _
"FROM RANDOM_RECORDS "

stRecordResult = (stRecordTotal / 4)

stRecord = "INSERT INTO RESULTS ( stRecordResult) "

DoCmd.RunSQL stRecord
Hi, sesling.

You simply confuse Access. He doesn't know how to divide "SELECT Count(RANDOM_RECORDS.ID) FROM RANDOM_RECORDS " into 4 parts. ;)

If you want to retrieve records count without dealing with recordsets you should take a look at DCount function.

And check the second query syntax and logic. If you have difficulties with SQL syntax, then build a query with functionality you need in Query builder, change view to "SQL View" and copy SQL expression to your code.

Good luck.
Sep 10 '07 #3

P: 84
Hi, sesling.

You simply confuse Access. He doesn't know how to divide "SELECT Count(RANDOM_RECORDS.ID) FROM RANDOM_RECORDS " into 4 parts. ;)

If you want to retrieve records count without dealing with recordsets you should take a look at DCount function.

And check the second query syntax and logic. If you have difficulties with SQL syntax, then build a query with functionality you need in Query builder, change view to "SQL View" and copy SQL expression to your code.

Good luck.
The DCOUNT worked. I am now trying to insert the results into an existing table but I am getting a syntax error. Below is the statement I am trying to run


stInfoPercent = (stInfoTotal / stRecordTotal)
stNonPercent = (stNonTotal / stRecordTotal)
stNPPercent = (stNPTotal / stRecordTotal)

stInsertResults = "INSERT INTO PERCENTAGES (stInfoPercent, stNonPercent, stNPPercent)"

DoCmd.RunSQL stInsertResults
Sep 10 '07 #4

FishVal
Expert 2.5K+
P: 2,653
The DCOUNT worked. I am now trying to insert the results into an existing table but I am getting a syntax error. Below is the statement I am trying to run


stInfoPercent = (stInfoTotal / stRecordTotal)
stNonPercent = (stNonTotal / stRecordTotal)
stNPPercent = (stNPTotal / stRecordTotal)

stInsertResults = "INSERT INTO PERCENTAGES (stInfoPercent, stNonPercent, stNPPercent)"

DoCmd.RunSQL stInsertResults
You have an invalid "INSERT" syntax. Look Access help "INSERT INTO Statement" topic.
Sep 10 '07 #5

P: 84
You have an invalid "INSERT" syntax. Look Access help "INSERT INTO Statement" topic.
Found the issue. thanks for the pointer
Sep 10 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.