Results 1 to 15 of 15
  1. #1
    sparker75 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    107

    Public Function

    Hello,



    I'm trying to calculate from a query to display the total hours in a form using the following Public function but Im getting the following error. "Run-time error '3122,: You tried to execute a query that does not include the specified expression 'EWOID' as part of an aggregate function. Can someone help please?


    Public Function fnCalcEhrs() As Variant
    Dim r As DAO.Recordset
    Dim sSQL As String


    'Default return value
    fnCalcEhrs = 0


    ' open inline SQL (or saved query)
    sSQL = "SELECT tblTransactionEWOTasks.EWOID, tblObjectResources.Type, Sum(tblTransactionEWOTasks.Workers) AS SumOfWorkers, Sum(tblTransactionEWOTasks.Duration) AS SumOfDuration, Sum([Workers]*[Duration]) AS Hours"
    sSQL = sSQL & " FROM qrySumAWREquipHours "
    sSQL = sSQL & "WHERE EWOID= " & Forms!frmAWR!EWOID

    Set r = CurrentDb.OpenRecordset(sSQL)
    fnCalcEhrs = r!Hours


    r.Close
    Set r = Nothing
    End Function

  2. #2
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You need to make it an aggregated query, so add

    Code:
    " GROUP BY tblTransactionEWOTasks.EWOID ,  tblObjectResources.Type "
    to your SQL string
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    sparker75 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    107
    Thank you Minty! Its giving me this error now. Run-time error '3075' Syntax error (missing operator0 in query expression 'tblObjectResources.Type WHERE EQOID=97'


    In my original query it is filtering on tblObjectResources.Type = Equipment

    SELECT tblTransactionEWOTasks.EWOID, tblObjectResources.Type, Sum(tblTransactionEWOTasks.Workers) AS SumOfWorkers, Sum(tblTransactionEWOTasks.Duration) AS SumOfDuration, Sum([Workers]*[Duration]) AS Hours
    FROM tblObjectResources INNER JOIN tblTransactionEWOTasks ON tblObjectResources.RESID = tblTransactionEWOTasks.Disipline
    GROUP BY tblTransactionEWOTasks.EWOID, tblObjectResources.Type
    HAVING (((tblObjectResources.Type)="Equipment"));






    Public Function fnCalcEhrs() As Variant
    Dim r As DAO.Recordset
    Dim sSQL As String


    'Default return value
    fnCalcEhrs = 0


    ' open inline SQL (or saved query)
    sSQL = "SELECT tblTransactionEWOTasks.EWOID, tblObjectResources.Type, Sum(tblTransactionEWOTasks.Workers) AS SumOfWorkers, Sum(tblTransactionEWOTasks.Duration) AS SumOfDuration, Sum([Workers]*[Duration]) AS Hours"
    sSQL = sSQL & " FROM qrySumAWREquipHours "
    sSQL = sSQL & " GROUP BY tblTransactionEWOTasks.EWOID , tblObjectResources.Type "
    sSQL = sSQL & "WHERE EWOID= " & Forms!frmAWR!EWOID

    Set r = CurrentDb.OpenRecordset(sSQL)
    fnCalcEhrs = r!Hours


    r.Close
    Set r = Nothing
    End Function

  4. #4
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    The Group By needs to be after the Where clause - not before.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    sparker75 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    107
    Run-time error '3601': Too few parameters. Expected 4

    Public Function fnCalcEhrs() As Variant
    Dim r As DAO.Recordset
    Dim sSQL As String


    'Default return value
    fnCalcEhrs = 0


    ' open inline SQL (or saved query)
    sSQL = "SELECT tblTransactionEWOTasks.EWOID, tblObjectResources.Type, Sum(tblTransactionEWOTasks.Workers) AS SumOfWorkers, Sum(tblTransactionEWOTasks.Duration) AS SumOfDuration, Sum([Workers]*[Duration]) AS Hours"
    sSQL = sSQL & " FROM qrySumAWREquipHours "
    sSQL = sSQL & "WHERE EWOID= " & Forms!frmAWR!EWOID
    sSQL = sSQL & " GROUP BY tblTransactionEWOTasks.EWOID , tblObjectResources.Type "

    Set r = CurrentDb.OpenRecordset(sSQL)
    fnCalcEhrs = r!Hours


    r.Close
    Set r = Nothing
    End Function

  6. #6
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    A couple of things - debug.print your string cut and paste the result to new blank query and try running it.

    Code:
    Public Function fnCalcEhrs() As Variant
        Dim r As DAO.Recordset
        Dim sSQL As String
    
    
    
    
        'Default return value
        fnCalcEhrs = 0
    
    
    
    
        ' open inline SQL (or saved query)
        sSQL = "SELECT tblTransactionEWOTasks.EWOID, tblObjectResources.Type, Sum(tblTransactionEWOTasks.Workers) AS SumOfWorkers, Sum(tblTransactionEWOTasks.Duration) AS SumOfDuration, Sum([Workers]*[Duration]) AS Hours"
        sSQL = sSQL & " FROM qrySumAWREquipHours "
        sSQL = sSQL & "WHERE EWOID= " & Forms!frmAWR!EWOID
        sSQL = sSQL & " GROUP BY tblTransactionEWOTasks.EWOID , tblObjectResources.Type "
        
        Debug.Print sSQL   ' Add this !
        
        
        Set r = CurrentDb.OpenRecordset(sSQL)
        fnCalcEhrs = r!Hours
    
    
    
    
        r.Close
        Set r = Nothing
    End Function
    Secondly does qrySumAWREquipHours contain any form referenced parameters ? If it does I don't think they will work.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    sparker75 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    107
    Here is the output SELECT tblTransactionEWOTasks.EWOID, tblObjectResources.Type, Sum(tblTransactionEWOTasks.Workers) AS SumOfWorkers, Sum(tblTransactionEWOTasks.Duration) AS SumOfDuration, Sum([Workers]*[Duration]) AS Hours FROM qrySumAWREquipHours WHERE EWOID= 98 GROUP BY tblTransactionEWOTasks.EWOID , tblObjectResources.Type


    The only parameters are type = Equipment

    This is the query qrySumAWREquipHours

    SELECT tblTransactionEWOTasks.EWOID, tblObjectResources.Type, Sum(tblTransactionEWOTasks.Workers) AS SumOfWorkers, Sum(tblTransactionEWOTasks.Duration) AS SumOfDuration, Sum([Workers]*[Duration]) AS Hours
    FROM tblObjectResources INNER JOIN tblTransactionEWOTasks ON tblObjectResources.RESID = tblTransactionEWOTasks.Disipline
    GROUP BY tblTransactionEWOTasks.EWOID, tblObjectResources.Type
    HAVING (((tblObjectResources.Type)="Equipment"));

  8. #8
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Okay that's all messed up - you are trying to recalculate all the sums again from a existing grouped query...

    Try this;

    Code:
    sSql = " SELECT EWOID,[Type], SumofWorkers, SumofDuration, [Hours] "
    sSql = sSql & " From FROM qrySumAWREquipHours 
    sSql = sSql & " WHERE EWOID = " & Forms!frmAWR!EWOID
    
     Debug.Print sSQL   
        
    Set r = CurrentDb.OpenRecordset(sSQL)
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  9. #9
    sparker75 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    107
    Works! Thank you. I just need to add a check the will stop running it if there is no record. Can you help?



    Public Function fnCalcEhrs() As Variant
    Dim r As DAO.Recordset
    Dim sSQL As String


    'Default return value
    fnCalcEhrs = 0


    ' open inline SQL (or saved query)
    sSQL = " SELECT EWOID,[Type], SumofWorkers, SumofDuration, [Hours] "
    sSQL = sSQL & " FROM qrySumAWREquipHours "
    sSQL = sSQL & " WHERE EWOID = " & Forms!frmAWR!EWOID

    Debug.Print sSQL
    Set r = CurrentDb.OpenRecordset(sSQL)
    fnCalcEhrs = r!Hours


    r.Close
    Set r = Nothing
    End Function

  10. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    PMFJI,

    When creating recordsets, you should always check to see if records are returned.
    Code:
    Public Function fnCalcEhrs() As Variant
        Dim r As DAO.Recordset
        Dim sSQL As String
    
        'Default return value
        fnCalcEhrs = 0
    
        ' open inline SQL (or saved query)
        sSQL = " SELECT EWOID,[Type], SumofWorkers, SumofDuration, [Hours] "
        sSQL = sSQL & " FROM qrySumAWREquipHours "
        sSQL = sSQL & " WHERE EWOID = " & Forms!frmAWR!EWOID
    
        Debug.Print sSQL
        Set r = CurrentDb.OpenRecordset(sSQL)
        If Not r.BOF And Not r.EOF Then
            fnCalcEhrs = r!Hours
        End If
    
        r.Close
        Set r = Nothing
    End Function
    Here is an alternative to check for records
    Code:
        If r.BOF And r.EOF Then
         ' no records - do nothing
         Else
            fnCalcEhrs = r!Hours
        End If

    *************************************************
    Be aware that "TYPE" is a reserved word and should NOT be used for object names.
    tblObjectResources.Type
    EWOID,[Type]





    ************************************************** ***********
    BTW, when you want to paste code in the reply, click the hash (#) mark, then paste the code between the tags.
    Click image for larger version. 

Name:	CodeTag1.png 
Views:	22 
Size:	15.9 KB 
ID:	36149

  11. #11
    sparker75 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    107
    Works. Thank you very much for your help!

  12. #12
    sparker75 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    107
    One more error check needed. Sometimes there is no EWOID on the form. This happens when the form is a draft.

    I tried If If Not IsNull(tblTransactionEWO.EWOID) Then after fnCalcEhrs = 0 but it still give me an error.


    Public Function fnCalcEhrs() As Variant
    Dim r As DAO.Recordset
    Dim sSQL As String
    fnCalcEhrs = 0
    sSQL = " SELECT EWOID,[Type], SumofWorkers, SumofDuration, [Hours] "
    sSQL = sSQL & " FROM qrySumAWREquipHours "
    sSQL = sSQL & " WHERE EWOID = " & Forms!frmAWR!EWOID
    Set r = CurrentDb.OpenRecordset(sSQL)
    If Not r.BOF And Not r.EOF Then
    fnCalcEhrs = r!Hours
    End If
    r.Close
    Set r = Nothing
    End Function

  13. #13
    sparker75 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    107
    The error I'm getting is Run-time Error '3075': Syntax error (mising operator) in query expressio 'EWOID =' This form is opened with a New record so EWOID is null to start.

    Public Function fnCalcEhrs() As Variant
    Dim r As DAO.Recordset
    Dim sSQL As String
    fnCalcEhrs = 0
    sSQL = " SELECT EWOID,[Type], SumofWorkers, SumofDuration, [Hours] "
    sSQL = sSQL & " FROM qrySumAWREquipHours "
    sSQL = sSQL & " WHERE EWOID = " & Forms!frmAWR!EWOID
    Set r = CurrentDb.OpenRecordset(sSQL)
    If Not r.BOF And Not r.EOF Then
    fnCalcEhrs = r!Hours
    End If
    r.Close
    Set r = Nothing
    End Function

  14. #14
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Try using the NZ() function (NZ = Null to Zero/ See Help...)
    Code:
    sSQL = sSQL & " WHERE EWOID = " & NZ(Forms!frmAWR!EWOID,0)


    Note: You *REALLY* need to use another name for
    Code:
    SELECT EWOID,[Type],
    Again, "TYPE" is a reserved word.....
    See http://allenbrowne.com/AppIssueBadWord.html



    Hmmmm... couldn't find the hash mark?

  15. #15
    sparker75 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    107
    Worked! Thank you.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 15
    Last Post: 10-17-2018, 09:26 PM
  2. Public function for lag and lead?
    By sergi117 in forum Programming
    Replies: 3
    Last Post: 10-10-2018, 07:49 AM
  3. Public Function
    By ShostyFan in forum Programming
    Replies: 3
    Last Post: 09-30-2017, 09:58 AM
  4. VBA Can't find Public Function
    By GraeagleBill in forum Programming
    Replies: 15
    Last Post: 03-17-2016, 09:38 PM
  5. Public variable not updating for each function
    By Ruegen in forum Programming
    Replies: 3
    Last Post: 08-28-2014, 06:26 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums