Calculate IF

[pookie62]

Hi,
Can this be done ?
I want to declare the variable in the SQL statement, and call it in the calculation where needed.. I tried it like this:
Doesn't work though

SQL = "SELECT Verzekering.v_ID, Verzekering.maatschappij, Verzekering.soort," _ 
& "Verzekering.ProvisiePerc, Polissen.VerzId," _ 
& "([Polissen].[Kapitaal]*[Verzekering].[ProvisiePerc]) AS Prov" _ 
& "FROM Verzekering INNER JOIN Polissen ON Verzekering.v_ID = Polissen.VerzId;" 
 
'MsgBox Prov, vbOKCancel, "Provisie Berekening" ' for debugging
If Me.VerzId = 1 Then 
    Me.Provisie = Me.Kapitaal * (Prov)

Thanks !

[Norie]

I think the short answer is no, it can't be done.

What is it you actually want to do?

You don't even appear to do anything with the SQL statement.

[pookie62]

Hi Norie,
Thanks for your reply, too bad it can't be done..
Now I calculate by this code:

If Me.VerzId = 2 Then
    Me.Provisie = Me.Kapitaal * 0.0525 
 ElseIf Me.VerzId = 4 Then
    Me.Provisie = Me.Kapitaal * 0.04   
 ElseIf Me.VerzId = 10 Then
    Me.Provisie = Me.Kapitaal * 0.05   
  Else: Me.Provisie = 0

But I don't want the percentages hard coded. They might change in the future, so it would be better to change them in a form.

Perhaps the values can be read out of the table directly ? Like
Me.Provisie = Me.Kapitaal * [table].[Procent]
Don't know the syntax for this or even if it's possible..
cheers..

Like

[geekgirlau]

Actually, it can be done, but you have to create a recordset.

[VBA]
Set rec = Currentdb.OpenRecordset(Sql, dbOpenSnapShot)

If Not rec.EOF Then
MsgBox !Prov
End If

[/VBA]

[pookie62]

geekgirlau, could you show me how to implement this code ?
Would be be great if I got this going... !
I am not to experienced with writing code..

I did this but getting errormessage :3001 invalid argument where the pointer is set to : Set rec =

Private Sub Kapitaal_AfterUpdate()
Dim strProvisie As Variant
Dim Prov As Variant
Dim strKap As Variant
strKap = Me.Kapitaal.Value
Sql = "SELECT Verzekering.v_ID, Verzekering.maatschappij, Verzekering.soort," _
& "Verzekering.ProvisiePerc, Polissen.VerzId," _
& "([Polissen].[Kapitaal]*[Verzekering].[ProvisiePerc]) AS Prov" _
& "FROM Verzekering INNER JOIN Polissen ON Verzekering.v_ID = Polissen.VerzId;"
 
Set rec = CurrentDb.OpenRecordset(Sql, dbOpenSnapShot)
 
If Not rec.EOF Then
    MsgBox Prov, vbOKCancel, "testing"
End If
If Me.VerzId = 1 Then
    Me.Provisie = strKap * strProvPerc
    'ElseIf Me.VerzId = 2 Then
    'Me.Provisie = Me.Kapitaal * (Prov)
 'ElseIf Me.VerzId = 4 Then
 '   Me.Provisie = Me.Kapitaal * (Prov)
  Else: Me.Provisie = 0
 
  MsgBox stProv, vbOKCancel, "Provisie Berekening"
End If
End Sub

[geekgirlau]

[vba]
Private Sub Kapitaal_AfterUpdate()
Dim strProvisie As Variant
Dim Prov As Variant
Dim strKap As Variant
Dim SQL As String
Dim rec As DAO.Recordset


strKap = Me.Kapitaal.Value

SQL = "SELECT Verzekering.v_ID, Verzekering.maatschappij, Verzekering.soort, " _
& "Verzekering.ProvisiePerc, Polissen.VerzId, " _
& "([Polissen].[Kapitaal]*[Verzekering].[ProvisiePerc]) AS Prov " _
& "FROM Verzekering " _
& "INNER JOIN Polissen ON Verzekering.v_ID = Polissen.VerzId;"

Set rec = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)

If Not rec.EOF Then
MsgBox rec!Prov, vbOKCancel, "testing"
End If

Select Case Me.VerzId
Case 1
' ***************************************
' Where is the value for strProvPerc set?
' ***************************************
Me.Provisie = strKap * strProvPerc

Case 2: 'Me.Provisie = Me.Kapitaal * (rec!Prov)
Case 4: 'Me.Provisie = Me.Kapitaal * (rec!Prov)

Case Else
Me.Provisie = 0

' ***************************************
' Where is the value for strProv set?
' ***************************************
MsgBox stProv, vbOKCancel, "Provisie Berekening"
End If

rec.Close
Set rec = Nothing
End Sub
[/vba]

A couple of things here:

• Make sure you have Option Explicit set at the top of your module. There are some variables here that don't appear to be dimensioned, which is going to cause you problems.
• Is there are reason that you have set a number of variables as Variant? You should only use variant if there's a possibility that the value will be Null, otherwise avoid them, and use a variable type that reflects the data that will be stored. Also, the name of your variable should reflect the data type, so "strKap" would indicate a string variable, "varKap" indicates variant.

[pookie62]

Thanks geekgirlau !

Tested the code which I altered to this: (Yes, I set Option Explicit)

Private Sub Kapitaal_AfterUpdate()
    Dim intKap As Currency
    Dim SQL As String
    Dim rec As DAO.Recordset
     
     
    intKap = Me.Kapitaal.Value
     
    SQL = "SELECT Verzekering.v_ID, Verzekering.maatschappij, Verzekering.soort, " _
    & "Verzekering.ProvisiePerc, Polissen.VerzId, " _
    & "([Polissen].[Kapitaal]*[Verzekering].[ProvisiePerc]) AS Prov " _
    & "FROM Verzekering " _
    & "INNER JOIN Polissen ON Verzekering.v_ID = Polissen.VerzId;"
     
    Set rec = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)
     
    If Not rec.EOF Then
        MsgBox rec!Prov, vbOKCancel, "testing"
    End If
     
    Select Case Me.VerzId
    Case 1
        Me.Provisie = intKap * (rec!ProvisiePerc)
    Case 2:
        Me.Provisie = intKap * (rec!ProvisiePerc)
    Case 4:
        Me.Provisie = intKap * (rec!ProvisiePerc)
    Case Else
        Me.Provisie = 0
    End Select
         
    rec.Close
    Set rec = Nothing
End Sub

Result: I get error 13 : Types don't match
Pointer is set to Set rec =

Ideas?

[geekgirlau]

Put a Debug.Print SQL above the Set rec. Then you can copy and paste the SQL string into a query to make sure the query is working correctly.