Results 1 to 3 of 3

Thread: Query variable

  1. #1
    Join Date
    Mar 2007

    Unanswered: Query variable

    Who can make something usefull of this.. I'm trying to create the variable Prov in the query, so that I can use this in the VBA code to calculate where wanted..
    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;"
    'DoCmd.RunSQL "UPDATE Verzekering SET [Provisie]=Int([Polissen].[Kapitaal]*[Verzekering].[ProvisiePerc]);"
    'MsgBox stProv, vbOKCancel, "Provisie Berekening"
    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

  2. #2
    Join Date
    Jan 2007
    California, USA
    the only way to use the result of the calculation you want to do is to run the SQL after you have built it, then either use a Domain function like DLookup to find that value in the query results, or open the query results in a recordset to obtain that field on a record by record basis.

  3. #3
    Join Date
    Mar 2007
    Hmm. oke
    Is this possible then ?
    If Me.VerzId = 1 Then
        Me.Provisie = Me.Kapitaal * [table].[percentage]   
    ElseIf Me.VerzId = 2 Then
        Me.Provisie = Me.Kapitaal * [table].[percentage]
    Where I don't know the syntax for [table].[percentage]
    table layout:

    v_ID : Primary Key(Autonummering)
    maatschappij : tekst
    soort : tekst
    ProvisiePerc : numeriek (percentage)

    VerzId: linked to [Verzekering].[v_ID]
    Kapitaal : Valuta
    Provisie : Valuta

    2nd problem (for me though) is to match the field ProvisiePerc with the proper record I think..
    Have no idea if this is possible and how..
    Thanks for supporting me..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts