Calculate IF

Hi,
Can this be done ?:banghead:
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

Code:

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 ! :friends:

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.

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

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

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]

geekgirlau, could you show me how to implement this code ?:help
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 =

Code:

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

[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.

Thanks geekgirlau ! :thumb

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

Code:

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?

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.