No idea what I'm doing! Can anyone help? (1 Viewer)

[AliciaMarie]

I'm trying to add columns to a macro, and in the design view everything looks correct, but I'm getting an Error #6: Overflow. I added the columns "PercentExposureMiss", "PercentChoiceMiss" and "Percent Correct". It was working fine before these additions. Does anyone see a problem with the code? Thank you for your help!!

Public Function dmtsResultsExport()

Dim cantab As DAO.Database
Dim rstSession As DAO.Recordset
Dim strSQL As String
Dim myFileSystemObject As Object
Dim ExportFile As Object
Dim filenameDtTm As String
Dim vSubject As String
Dim vMemoryDelayMS, vDelayUsed, vNumCorrect, vNumIncorrect, vChoiceMissActual, vNumExposureMiss As String
Dim vPercentChoiceMiss, vPercentExposureMiss, vPercentCorrect, vAvgChoiceLatencyIncorrect, vAvgChoiceLatencyCorrect, vAvgSampleLatency As String
Dim LoopCount As Integer
Dim CurrentSubject As String
Dim MaxSessionCount As Integer
Dim DummyResponse As String


On Error GoTo ErrorHandler

Set cantab = CurrentDb '' Associate the cursor channel with the current DB (this one)

''
''
'' Find the Maximum number of sessions across all animals.
'' Write out to the text file that many heading groups
''
''
strSQL = "select max(spa) as headingcount from (select count(subject) as spa from cnprc_dmts_export_query group by subject)"

Set rstSession = cantab.OpenRecordset(strSQL, dbOpenDynaset)

rstSession.MoveFirst
With rstSession
If IsNull(![HeadingCount]) Then
DummyResponse = MsgBox("DMTS Export: No data found", vbOKOnly, "CANTAB")
rstSession.Close
cantab.Close
Set rstSession = Nothing
Set cantab = Nothing
Exit Function
End If
MaxSessionCount = ![HeadingCount]
End With

rstSession.Close '' Close the database cursor so we can use it again for reading the data to export
Set rstSession = Nothing

''
''
'' Set the export filename and open as a text file for writing
''
''
filenameDtTm = "C:\CANTAB Exports\DMTS " & Format$(Now(), "yyyy-mm-dd hh-mm") & ".csv"
Set myFileSystemObject = CreateObject("Scripting.FileSystemObject")
Set ExportFile = myFileSystemObject.CreateTextFile(filenameDtTm, True)

DummyResponse = SysCmd(acSysCmdSetStatus, "Exporting to " & filenameDtTm) '' Set the status bar

ExportFile.write "Subject," '' The csv export file header has one line,
'' which begins with one occurance of the string "Subject"
For LoopCount = 1 To MaxSessionCount
ExportFile.write " DelayUsed," & "S-" & (LoopCount) '' Following Subject are repeating groups of the data headings
ExportFile.write " MemoryDelayMS," & "S-" & (LoopCount)
ExportFile.write " NumCorrect," & "S-" & (LoopCount) '' Each ends with S-n, where n is the session number
ExportFile.write " NumIncorrect," & "S-" & (LoopCount) '' For example FalseAlarms S-24
ExportFile.write " ChoiceMissActual," & "S-" & (LoopCount)
ExportFile.write " NumExposureMiss," & "S-" & (LoopCount)
ExportFile.write " PercentChoiceMiss," & "S-" & (LoopCount)
ExportFile.write " PercentExposureMiss," & "S-" & (LoopCount)
ExportFile.write " PercentCorrect," & "S-" & (LoopCount)
ExportFile.write " AvgChoiceLatencyIncorrect," & "S-" & (LoopCount)
ExportFile.write " AvgChoiceLatencyCorrect," & "S-" & (LoopCount)
ExportFile.write " AvgSampleLatency," & "S-" & (LoopCount)
Next LoopCount
ExportFile.WriteLine

''
''
'' Set the SQL statement again
'' Open the cantab cursor (rstSession) to read the CPT data for export
''
'' This data comes from a view created to provide the export records to
'' both this procedure and so that the users can easily see what the export
'' data actually is. This gives them an easy way to check on values that
'' look questionable.
''
''
strSQL = "SELECT Subject, DateTimeCode, DelayUsed, MemoryDelayMS, NumCorrect, NumIncorrect, ChoiceMissActual, NumExposureMiss, " & _
"PercentChoiceMiss, PercentExposureMiss, PercentCorrect, AvgChoiceLatencyIncorrect, AvgChoiceLatencyCorrect, AvgSampleLatency " & _
"FROM CNPRC_DMTS_Export_Query " & _
"ORDER BY Subject, DateTimeCode"

Set rstSession = cantab.OpenRecordset(strSQL, dbOpenDynaset)

'' If the recordset is empty, exit.
If rstSession.EOF Then
DummyResponse = SysCmd(acSysCmdClearStatus) ' Clears StatusBar
DummyResponse = MsgBox("DMTS Export: No data found", vbOKOnly, "CANTAB")
rstSession.Close
cantab.Close
ExportFile.Close
Set rstSession = Nothing
Set cantab = Nothing
Set ExportFile = Nothing
Exit Function
End If

rstSession.MoveFirst
LoopCount = 0
CurrentSubject = "NoSubject" '' Set an initial value so we dont end up with a blank line
With rstSession
Do Until .EOF Or LoopCount > 200000 '' Provide a method for the code to stop in case something is weird
LoopCount = LoopCount + 1 '' with the database or export view

vSubject = ![Subject]
vDelayUsed = ![DelayUsed]
vMemoryDelayMS = ![MemoryDelayMS]
vNumCorrect = ![NumCorrect]
vNumIncorrect = ![NumIncorrect]
vChoiceMissActual = ![ChoiceMissActual]
vNumExposureMiss = ![NumExposureMiss]
vPercentChoiceMiss = ![PercentChoiceMiss]
vPercentExposureMiss = ![PercentExposureMiss]
vPercentCorrect = ![PercentCorrect]
vAvgChoiceLatencyIncorrect = ![AvgChoiceLatencyIncorrect]
vAvgChoiceLatencyCorrect = ![avgchoicelatencycorrect]
vAvgSampleLatency = ![AvgSampleLatency]

If vSubject <> CurrentSubject Then
If CurrentSubject <> "NoSubject" Then '' Unless this is the first time through the loop,
ExportFile.WriteLine '' go to the next line
End If
CurrentSubject = vSubject
ExportFile.write (vSubject) & ","
End If

ExportFile.write (vDelayUsed) & "," '' Write out all the data values, separated by commas (csv!)
ExportFile.write (vMemoryDelayMS) & ","
ExportFile.write (vNumCorrect) & ","
ExportFile.write (vNumIncorrect) & ","
ExportFile.write (vChoiceMissActual) & ","
ExportFile.write (vNumExposureMiss) & ","
ExportFile.write (vPercentChoiceMiss) & ","
ExportFile.write (vPercentExposureMiss) & ","
ExportFile.write (vPercentCorrect) & ","
ExportFile.write (vAvgChoiceLatencyIncorrect) & ","
ExportFile.write (vAvgChoiceLatencyCorrect) & ","
ExportFile.write (vAvgSampleLatency) & ","

rstSession.MoveNext

Loop
End With

''
''
'' Close up the cursor, database channel and the text file
''
''
rstSession.Close
cantab.Close
ExportFile.Close

Set rstSession = Nothing
Set cantab = Nothing
Set ExportFile = Nothing

''
''
'' Display a message to the user with the export filename and number of sessions written
''
''
DummyResponse = SysCmd(acSysCmdClearStatus) ' Clears StatusBar
DummyResponse = MsgBox("Export file: " & filenameDtTm & Chr(13) & Chr(13) & _
" Total session exported: " & LoopCount & Chr(13) & _
" Maximum session number: " & MaxSessionCount, vbOKOnly, "CANTAB")

dmtsResultsExport = 0 '' Only needed because Access can call this routine only as a function
Exit Function

ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Function

 

[pbaldy]

What line throws the error (you may need to temporarily comment out the On Error GoTo ErrorHandler line)? Typically I've seen that error either when you try to assign a value that a field/variable can't hold, or when dividing by zero. You've declared a variable as Integer, so stuffing a value greater than 32,xxx into that would cause the error.

 

[AliciaMarie]

I don't have any Div/0 in my dataset, but I do have #Num! (when 0/0), which should just be 0.
Everything was working fine until I added this:
"Dim vPercentChoiceMiss, vPercentExposureMiss, vPercentCorrect as String". In the query, these answers are where I have #Num!. The answers to these equations are percentages. Should I be using something other than "As String"?

I am so so new to this, I've only take two Access classes, but they never went over macros. Thank you so much for your help!!

 

[pbaldy]

For starters,

Dim vPercentChoiceMiss, vPercentExposureMiss, vPercentCorrect as String

declares 2 Variants and 1 String. You need to explicitly declare each, like:

Dim vPercentChoiceMiss as String, vPercentExposureMiss as String, vPercentCorrect as String

If they are percentages, which are numbers, you'd want a numeric data type. Double might be appropriate. By the way, for clarity's sake, what you've posted is VBA code, not a macro.

 

[AliciaMarie]

I changed it to:
Dim vPercentChoiceMiss As Double
Dim vPercentExposureMiss As Double
Dim vPercentCorrect As Double

but still come up with the Overflow error.

Do all the other parts of the code look correct? Do you think it just has to do with that part in the beginning?

Thank you again for all your help!

 

[pbaldy]

The way I would debug isn't to review every line of code. I'd comment out the On Error line and run the code so I could see exactly where the problem is (when you run the code with that line commented out, you should get an error that offers you the opportunity to Debug). Then when it's working, uncomment that line again.

 

[pbaldy]

By the way, once you've hit debug and see the error line in yellow, you'll usually need to stop the code, either either the square icon in the toolbar or Run/Reset.

 

[AliciaMarie]

So I think I need an If, then statement in my query for each of the three percents equations, something along the line If it is #Num! (that's the error I'm getting), then put "0".

Can this be done?

Thanks for your help!

 

[namliam]

1) please use code tags when posting any code (VBA or Queries) see my signature for details.

2) What is the sql in your query CNPRC_DMTS_Export_Query, that is (among other things) calculating the percentage fields?

3) See 1

 

[AliciaMarie]

I'm not quite sure what code tags are... but here are the equations for the three percentages:

PercentChoiceMiss: (((Count(IIf([dr].[Phase2Responded],Null,1)))-(Count(IIf([dr].[Phase1Responded],Null,1))))/((Count(IIf([dr].[Phase2Responded],Null,1)))-(Count(IIf([dr].[Phase1Responded],Null,1)))+Count(IIf([dr].[Phase2RespondedCorrectly],1,Null))+Count(IIf([dr].[Phase2Responded],1,Null))-Count(IIf([dr].[Phase2RespondedCorrectly],1,Null))))*100

PercentExposureMiss: ((Count(IIf([dr].[Phase1Responded],Null,1)))/(Count(IIf([dr].[Phase2RespondedCorrectly],1,Null))+Count(IIf([dr].[Phase2Responded],1,Null))-Count(IIf([dr].[Phase2RespondedCorrectly],1,Null))+(Count(IIf([dr].[Phase2Responded],Null,1)))-(Count(IIf([dr].[Phase1Responded],Null,1)))+Count(IIf([dr].[Phase1Responded],Null,1))))*100

PercentCorrect: ((Count(IIf([dr].[Phase2RespondedCorrectly],1,Null)))/((Count(IIf([dr].[Phase2RespondedCorrectly],1,Null)))+(Count(IIf([dr].[Phase2Responded],1,Null))-Count(IIf([dr].[Phase2RespondedCorrectly],1,Null)))))*100

 

[namliam]

Code tags, click the link in my signature...

or this one: http://www.access-programmers.co.uk/forums/showthread.php?t=240420