Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2012
    Posts
    12

    Unanswered: Beginers VBA In Access Queries

    I have a database which calculates employee pay and holiday pay. I am currently using a crosstab query to work these out. For example:

    Pay: Sum(IIf([WCID]=14,[Holiday Rate]*[Hours],IIf([WCID]=15,[Hours],IIf([override hourly rate]>0,[override hourly rate]*[hours],[hourly rate]*[hours]))))

    Which allows for complications in people's T&Cs.

    However I've got a whole extra layer to add which will double the length of this expression. I'm thinking I should be using ElseIf Then in VBA to build this but as a newbie don't know how to build the VBA inside the query in place of the expression above.

    Can anyone help?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. You must create a Public function. A Public Function is a VBA function that resides in a general Module (i.e. not a Class Module and not the module of a Form or of a Report). Ex:

    1. In Mod_General (the name of the Module):
    Code:
    Public Function ComputePay(ByVal WCID As Variant, ByVal HolidayRate As Variant, ByVal Hours As Variant, ByVal OverrideHourlyRate As Variant, ByVal HourlyRate As Variant) As Variant
        
        If WCID = 14Then
            ComputePay = HolidayRate * Hours 
        Else 
            If WCID = 15 Then
                ComputePay = Hours 
            Else ...
        End If
    2. In the Query:
    Code:
    Pay: Sum(ComputePay(WCID, HolidayRate, Hours, OverrideHourlyRate,  HourlyRate))
    Or in SQL:
    Code:
    SELECT Sum(ComputePay(WCID, HolidayRate, Hours, OverrideHourlyRate, HourlyRate)) As Pay
    From ...
    Note: As the function ComputePay will be called for every row in the RowSet, the query can be quite slow if the RowSet has many rows.
    Have a nice day!

  3. #3
    Join Date
    Jan 2012
    Posts
    12
    Thanks so much for this. I tried this out but got a little stuck. I opened the Visual Basic Console and created the new function:


    Public Function ComputeHolPay(ByVal WCID As Variant, ByVal HolidayRate As Variant, ByVal Hours As Variant, ByVal OverrideHourlyRate As Variant, ByVal HourlyRate As Variant) As Variant

    If WCID = 14 Then
    ComputePay = HolidayRate * Hours
    ElseIf WCID = 15 Then
    ComputePay = Hours
    ElseIf OverrideHourlyRate > 0 Then
    ComputePay = OverrideHourlyRate * Hours

    Else
    ComputePay = HourlyRate * Hours


    End If


    End Function

    I then went back to my crosstab and pasted
    Pay: Sum(ComputePay(WCID, HolidayRate, Hours, OverrideHourlyRate, HourlyRate))
    in place of my long expression.

    However I get the message 'Undefined function 'ComputePay' in expression. Any idea why this is? I also tried building a new field in the expression using the builder and tried to open up the functions in the database but none showed up. Thanks in advance!

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have you defined a function called ComputePay?
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You named the funtion ComputeHolPay:
    Code:
    Public Function ComputeHolPay(ByVal WCID...
    While you tried to assign a value to ComputePay:
    Code:
    ComputePay = HourlyRate * Hours
    And as you don't have the Variable Declaration enforced with the directive:
    Code:
    Option Explicit
    The compiler generated a implicit (i.e. not declared) variable named ComputePay inside the function

    Moreover, you also used ComputePay when you tried to use the function in the query:
    Code:
    Pay: Sum(ComputePay(WCID, HolidayRate, Hours, OverrideHourlyRate, HourlyRate))
    Make your mind up: it's either ComputeHolPay or ComputePay but it cannot be both.
    Have a nice day!

  6. #6
    Join Date
    Jan 2012
    Posts
    12
    Thank you very much. I have changed the name of the function back to:
    Public Function ComputePay...

    which hopefully then matches up throughout. However unfortunately I still get the message when running the query 'undefined function 'ComputePay' in expression. Perhaps something to do with the fact it's a crosstab?

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I never tried but, at first sight, I don't see why the function could not work in a Crosstab query. Is the function in a general module (i.e. not the module of a form or of a report)?
    Have a nice day!

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd check for possible corruption (take a backup, do a compact and repair and the see if tis gone
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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