Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    Mar 2007
    Posts
    20

    Unanswered: Change "Where.." in VBA depending on

    Hi,
    I have a subform where details can be filled about a product.
    Fields:
    - Advnaam: (Salesperson selected by pulldown
    - VerzId: Combination of product/company who sells the product also pulldown list (Unique Identifier)
    - Ingangsdatum: Date field
    - Premie: amount of money for client to pay
    - Kapitaal: total of money which client is insured for
    - Provisie: Fee for sales for selling the product
    - Checkbox if Fee is received from company
    - Ontv_datum: Date when Fee is received (for monthly reports)

    All this is stored in de table Polissen
    Now the question:
    There are different companies and they are paying different % as Fee., mostly a % from the Kapitaal (i.e Kapitaal= 100.000- * 0,05 = (Fee) 5000,-
    I have the separate queries that calculate those Fee's
    How can I autofill the Provisie field ?? (And keep it so that I can always type another value (if needed)
    Is this possible in VBA SELECT CASE maybe and then change the WHERE statement if VerzId has a certain value ? (values are 1,2,3 etc)

    Hope it's a bit clear..
    Thanks for helping out..


    Query example:
    Code:
    SELECT Verzekering.maatschappij, Verzekering.soort, Polissen.Premie, Polissen.Kapitaal, ([Kapitaal]*0.05) AS Fee
    FROM Verzekering INNER JOIN Polissen ON Verzekering.v_ID = Polissen.VerzId
    WHERE (((Verzekering.maatschappij)="Gelder"));

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I think you want something along the lines of this:
    Code:
    SELECT
    (Case VerzId
    WHEN 1
    <do something>
    WHEN 2
    <do something else>
    ELSE
    <do the other thing>
    END),
    Verzekering.maatschappij, Verzekering.soort, Polissen.Premie, Polissen.Kapitaal, ([Kapitaal]*0.05) AS Fee
    FROM Verzekering INNER JOIN Polissen ON Verzekering.v_ID = Polissen.VerzId
    WHERE (((Verzekering.maatschappij)="Gelder"));
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2007
    Posts
    20
    Hi georgev,
    Can I replace the <do something> with a complete query ?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I think this is a question for r937.
    I don't think you can do that in this context.

    Try running this sql and the results:
    Code:
    SELECT
    (Case VerzId
    WHEN 1
    "the case was 1"
    WHEN 2
    "the case was 2"
    ELSE
    "the case was else"
    END)
    FROM Verzekering INNER JOIN Polissen ON Verzekering.v_ID = Polissen.VerzId
    WHERE (((Verzekering.maatschappij)="Gelder"));
    If I understand correctly you're wanting to change the WHERE clause of the sql statement based on the value of another field, is this correct?
    George
    Home | Blog

  5. #5
    Join Date
    Mar 2007
    Posts
    20
    Oke, I'll give it a try..
    Your assumption is correct !
    Actually the calculation ([Kapitaal]*0.05) differs too per company
    Thanks for you support, appreciate !

  6. #6
    Join Date
    Mar 2007
    Posts
    20
    Getting syntax error..
    Code:
    Private Sub Form_Open(Cancel As Integer)
    Me.Prov_ontvDD.Enabled = False
    DoCmd.RunSQL "SELECT(Case VerzId WHEN 1 "the case was 1" WHEN 2 "the case was 2" Else "the case was else" END)FROM Verzekering INNER JOIN Polissen ON Verzekering.v_ID = Polissen.VerzId WHERE Verzekering.maatschappij='Gelderse';"
    End Sub

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by pookie62
    Getting syntax error..
    Code:
    Private Sub Form_Open(Cancel As Integer)
    Me.Prov_ontvDD.Enabled = False
    DoCmd.RunSQL "SELECT(Case VerzId WHEN 1 "the case was 1" WHEN 2 "the case was 2" Else "the case was else" END)FROM Verzekering INNER JOIN Polissen ON Verzekering.v_ID = Polissen.VerzId WHERE Verzekering.maatschappij='Gelderse';"
    End Sub
    RunSQL requires Action queries ... UPDATE , INSERT, DELETE ... Usually, with SELECTs you're trying to return a recordset ...
    Back to Access ... ADO is not the way to go for speed ...

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    M Owen is correct, doCmd.RunSQL needs an action query.

    Another approach to this problem would be something along the lines of:
    Code:
    Dim Example As String
    
    Example = "SELECT Verzekering.maatschappij, Verzekering.soort, Polissen.Premie, Polissen.Kapitaal"
    
    If Kapitaal = <somevalue> Then
    Example = Examlpe & ",([Kapitaal]*0.05) AS Fee "
    Else
    Example = Examlpe & ",([Kapitaal]*<another value>) AS Fee "
    End If
    
    Example = Example & " FROM Verzekering INNER JOIN Polissen ON Verzekering.v_ID = Polissen.VerzId "
    
    If VerzId = <somevalue> Then
    Example = Example & " WHERE (((Verzekering.maatschappij)="Gelder"))"
    Elseif Verzid = <someothervalue> Then
    Example = Example & " WHERE (((Verzekering.maatschappij)="Gelder"))"
    Else
    Example = Example & " WHERE (((Verzekering.maatschappij)="Gelder"))"
    End If
    
    Debug.Print Example
    Building the SQL string up using If statements might be the way for you to go.
    George
    Home | Blog

  9. #9
    Join Date
    Mar 2007
    Posts
    20
    I'm going nuts here..
    Changed the code.. nothing happens ?!?
    Code:
    Private Sub Provisie_BeforeUpdate(Cancel As Integer)
    Dim Base As String
    
    Base = "SELECT Verzekering.maatschappij, Verzekering.soort, Polissen.Premie, Polissen.Kapitaal"
    
    If Me.VerzId = 1 Then
    Base = Base & ",([Kapitaal]*0,05) AS Fee "
    ElseIf Me.VerzId = 2 Then
    Base = Base & ",([Kapitaal]*0,0525) AS Fee "
    ElseIf Me.VerzId = 4 Then
    Base = Base & ",([Kapitaal]*0,4) AS Fee "
    ElseIf Me.VerzId = 10 Then
    Base = Base & ",([Kapitaal]*0,05) AS Fee "
    End If
    
    Base = Base & " FROM Verzekering INNER JOIN Polissen ON Verzekering.v_ID = Polissen.VerzId "
    
    If Me.VerzId = 1 Then
    Base = Base & " WHERE (((Verzekering.maatschappij)='Gelderse'))"
    ElseIf Me.VerzId = 2 Then
    Base = Base & " WHERE (((Verzekering.maatschappij)='Dela'))"
    ElseIf Me.VerzId = 4 Then
    Base = Base & " WHERE (((Verzekering.maatschappij)='PC Uitvaart'))"
    ElseIf Me.VerzId = 10 Then
    Base = Base & " WHERE (((Verzekering.maatschappij)='Gelderse'))"
    End If
    
    Debug.Print Base
    End Sub
    I attached the db maybe you want to have a look at it..
    Attached Files Attached Files

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The debug.Print line shows the result in the immediate window in VBA.
    I suggest you change it to "MsgBox Base" and see what happens.
    That was purely to show you that you can build the statement this way.
    George
    Home | Blog

  11. #11
    Join Date
    Mar 2007
    Posts
    20
    Hi georgev,
    Thank you so much for your time !
    I got the msgbox working now.
    Next thing (and hopefully last) is how do I assign the outcome of calculation (Fee) to the field on the form (Provisie) ?

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Sorry, what?
    Quote Originally Posted by M Owen
    RunSQL requires Action queries ... UPDATE , INSERT, DELETE ... Usually, with SELECTs you're trying to return a recordset ...
    You want to run your SELECT as a recordset returning the results to a form?
    George
    Home | Blog

  13. #13
    Join Date
    Mar 2007
    Posts
    20
    I'm lost..
    Is what I want not possible?
    Please understand that English is not my native language, so probably I don't understand completely what is said sometimes..
    I just want this Provisie field autofilled with the Fee if there is a calculation for it, if not I want to be able to fill the amount manually.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Example:
    Code:
    If Me.VerzId = 1 Then
    Base = Base & ",([Kapitaal]*0,05) AS Fee "
    <fieldonform>.value (or .text) = "this"
    ...
    End If
    Would that be what you're looking for?
    George
    Home | Blog

  15. #15
    Join Date
    Mar 2007
    Posts
    20
    Almost..
    I don't get a value in the field, but when I say
    Me.Provisie.Value = "Fee"
    The word Fee comes in the Provisie field..
    Ideas ?

Posting Permissions

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