Type Mismatch Error

[sesling]

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

[mlcampeau]

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.

[FishVal]

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.

[sesling]

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

[FishVal]

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.

[sesling]

You have an invalid "INSERT" syntax. Look Access help "INSERT INTO Statement" topic.

Found the issue. thanks for the pointer