Access 97 VB

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Could someone please give me a hand - I have written the following code to
compare an audio test with a previous test to see if two consecutive
frequencies have a change of 15dB or more to set a field to 'Abnormal Shift',
or to 'No Change' if untrue.
I have messed up somewhere as I get an object error.

'This code for the ongoing comparison to baseline tests..


Dim dbs As Database, rst As Recordset
Dim strSelect As String



Dim L1 As Double, R1 As Double, L2 As Double, R2 As Double, L3 As Double, R3
As Double, L4 As Double, R4 As Double, L5 As Double, R5 As Double

Set dbs = CurrentDb

strSelect = "SELECT * FROM [Audio History]ORDER BY [Audio
History].DateCompleted DESC;"

Set rst = dbs.OpenRecordset(strSelect)






L1 = rst(CDbl([L1000]))
R1 = rst(CDbl([R1000]))
L2 = rst(CDbl([L2000]))
R2 = rst(CDbl([R2000]))
L3 = rst(CDbl([l3000]))
R3 = rst(CDbl([R3000]))
L4 = rst(CDbl([L4000]))
R4 = rst(CDbl([R4000]))
L5 = rst(CDbl([L6000]))
R5 = rst(CDbl([R6000]))


rst.MoveNext

If (L1 > rst([L1000] + 14) And (L2 > rst([L2000] + 14))) Then
[ComparisonWithLast] = "Abnormal Shift"

End If

If (L2 > rst([L2000] + 14) And (L3 > rst([l3000] + 14))) Then
[ComparisonWithLast] = "Abnormal Shift"

End If

If (L3 > rst([l3000] + 14) And (L4 > rst([L4000] + 14))) Then
[ComparisonWithLast] = "Abnormal Shift"

End If

If (L4 > rst([L4000] + 14) And (L5 > rst([L5000] + 14))) Then
[ComparisonWithLast] = "Abnormal Shift"

End If

If (R1 > rst([R1000] + 14) And (R2 > rst([R2000] + 14))) Then
[ComparisonWithLast] = "Abnormal Shift"

End If

If (R2 > rst([R2000] + 14) And (R3 > rst([R3000] + 14))) Then
[ComparisonWithLast] = "Abnormal Shift"

End If

If (R3 > rst([R3000] + 14) And (R4 > rst([R4000] + 14))) Then
[ComparisonWithLast] = "Abnormal Shift"

End If

If (R4 > rst([R4000] + 14) And (R5 > rst([R5000] + 14))) Then
[ComparisonWithLast] = "Abnormal Shift"

Else: [ComparisonWithLast] = "No Change"
rst.Close

End If


End Sub
 
Try this

Dim dbs As Database, rst As Recordset
Dim strSelect As String



Dim L1 As Double, R1 As Double, L2 As Double, R2 As Double, L3 As Double, R3
As Double, L4 As Double, R4 As Double, L5 As Double, R5 As Double

Set dbs = CurrentDb

strSelect = "SELECT * FROM [Audio History] ORDER BY [Audio
History].DateCompleted DESC;"
Set rst = dbs.OpenRecordset(strSelect)

L1 = CDbl(rst![L1000])
R1 = CDbl(rst![R1000])
L2 = CDbl(rst![L2000])
R2 = CDbl(rst![R2000])
L3 = CDbl(rst![l3000])
R3 = CDbl(rst![R3000])
L4 = CDbl(rst![L4000])
R4 = CDbl(rst![R4000])
L5 = CDbl(rst![L6000])
R5 = CDbl(rst![R6000])

rst.MoveNext

If (L1 > rst([L1000] + 14) And (L2 > rst([L2000] + 14))) Then
[ComparisonWithLast] = "Abnormal Shift"

End If

If (L2 > rst([L2000] + 14) And (L3 > rst([l3000] + 14))) Then
[ComparisonWithLast] = "Abnormal Shift"

End If

If (L3 > rst([l3000] + 14) And (L4 > rst([L4000] + 14))) Then
[ComparisonWithLast] = "Abnormal Shift"

End If

If (L4 > rst([L4000] + 14) And (L5 > rst([L5000] + 14))) Then
[ComparisonWithLast] = "Abnormal Shift"

End If

If (R1 > rst([R1000] + 14) And (R2 > rst([R2000] + 14))) Then
[ComparisonWithLast] = "Abnormal Shift"

End If

If (R2 > rst([R2000] + 14) And (R3 > rst([R3000] + 14))) Then
[ComparisonWithLast] = "Abnormal Shift"

End If

If (R3 > rst([R3000] + 14) And (R4 > rst([R4000] + 14))) Then
[ComparisonWithLast] = "Abnormal Shift"

End If

If (R4 > rst([R4000] + 14) And (R5 > rst([R5000] + 14))) Then
[ComparisonWithLast] = "Abnormal Shift"

Else: [ComparisonWithLast] = "No Change"
rst.Close

End If

--
I hope that helped
Good luck


Nigel Forge said:
Could someone please give me a hand - I have written the following code to
compare an audio test with a previous test to see if two consecutive
frequencies have a change of 15dB or more to set a field to 'Abnormal Shift',
or to 'No Change' if untrue.
I have messed up somewhere as I get an object error.

'This code for the ongoing comparison to baseline tests..


Dim dbs As Database, rst As Recordset
Dim strSelect As String



Dim L1 As Double, R1 As Double, L2 As Double, R2 As Double, L3 As Double, R3
As Double, L4 As Double, R4 As Double, L5 As Double, R5 As Double

Set dbs = CurrentDb

strSelect = "SELECT * FROM [Audio History]ORDER BY [Audio
History].DateCompleted DESC;"

Set rst = dbs.OpenRecordset(strSelect)






L1 = rst(CDbl([L1000]))
R1 = rst(CDbl([R1000]))
L2 = rst(CDbl([L2000]))
R2 = rst(CDbl([R2000]))
L3 = rst(CDbl([l3000]))
R3 = rst(CDbl([R3000]))
L4 = rst(CDbl([L4000]))
R4 = rst(CDbl([R4000]))
L5 = rst(CDbl([L6000]))
R5 = rst(CDbl([R6000]))


rst.MoveNext

If (L1 > rst([L1000] + 14) And (L2 > rst([L2000] + 14))) Then
[ComparisonWithLast] = "Abnormal Shift"

End If

If (L2 > rst([L2000] + 14) And (L3 > rst([l3000] + 14))) Then
[ComparisonWithLast] = "Abnormal Shift"

End If

If (L3 > rst([l3000] + 14) And (L4 > rst([L4000] + 14))) Then
[ComparisonWithLast] = "Abnormal Shift"

End If

If (L4 > rst([L4000] + 14) And (L5 > rst([L5000] + 14))) Then
[ComparisonWithLast] = "Abnormal Shift"

End If

If (R1 > rst([R1000] + 14) And (R2 > rst([R2000] + 14))) Then
[ComparisonWithLast] = "Abnormal Shift"

End If

If (R2 > rst([R2000] + 14) And (R3 > rst([R3000] + 14))) Then
[ComparisonWithLast] = "Abnormal Shift"

End If

If (R3 > rst([R3000] + 14) And (R4 > rst([R4000] + 14))) Then
[ComparisonWithLast] = "Abnormal Shift"

End If

If (R4 > rst([R4000] + 14) And (R5 > rst([R5000] + 14))) Then
[ComparisonWithLast] = "Abnormal Shift"

Else: [ComparisonWithLast] = "No Change"
rst.Close

End If


End Sub
Click to expand...
 
Back
Top