Results 1 to 5 of 5

Save value from query. Error 2471

  1. #1
    AWADOS is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2019
    Posts
    5

    Save value from query. Error 2471

    Hi,



    I have several tables but the ones I use for the queries are the following ones:

    tabNom with the field codnom wich is the number of a paying interval. For example codnom=1 could be the first week of the year.

    tabPer with the field codper wich represents the code of each employee

    tabPay where i save all the payments with a combined primary key using the fields codper and codnom (so one person is related to many payment intervals and many people can get paid in one payment interval).

    I need to know the last 4 payments and calculate the average for each person

    I have a query wich gives me the last 4 registers using


    PARAMETERS P Short;
    SELECT TOP 4 tabPay.CODNOM, tabPay.CODPER, tabPay.Pay
    FROM tabPay
    GROUP BY tabPay.CODNOM, tabPay.CODPER, tabPay.Pay
    HAVING (((tabPay.CODPER)=[P]))
    ORDER BY tabPay.CODNOM DESC;


    Where P is the code of the employee. Then I have other query that gives me the average


    SELECT Count([4p].CODNOM) AS CuentaDeCODNOM, [4p].CODPER, Avg([4p].MONTO) AS PM
    FROM 4p
    GROUP BY [4p].CODPER;


    At this moment i'm trying to run these two queries from vba by doing this


    Private Sub Cmd3_Click()
    Dim q4p As DAO.QueryDef
    Dim qprom As DAO.QueryDef
    Dim mdb As DAO.Database
    Dim a As Double
    Dim p As Integer

    Set mdb = CurrentDb
    Set q4p = mdb.QueryDefs("4p")
    Set qprom = mdb.QueryDefs("av")
    p = 1


    q4p.Parameters("P").Value = p
    qprom.Parameters("P").Value = p
    a = DLookup("PM", "PROM", "[CODPER]=" & p)
    MsgBox a
    Set qprom = Nothing
    Set q4p = Nothing

    End Sub


    It's a test for person 1 so i can continue but it keeps giving me the Error 2471

    If you have any idea for solving this problem i would apreciate it a lot

    Thanks

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,438
    Is this bombing out on the dlookup statement?

    If so I rather suspect the 'p' value is actually a text value in which case you will need

    a = DLookup("PM", "PROM", "[CODPER]='" & p & "'")



  3. #3
    AWADOS is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2019
    Posts
    5
    I have been reading about this issue and I found that the Dlookup, Dmax, Dmin, Dlast, are functions available for queries wich don't require a parameter. So, if you know the way to return the values from a query like this using vba please tell me how.

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,438
    I don't think you need domain functions at all for this. I was just looking at what you have with the assumption it's going to do what you want. The only statement in there I thought could be causing the issue was the domain function. Nor do I know why you need to do the average in the query itself. That seems like you could calculate that on any necessary reporting.

    If I were to assume this query was correctly pulling the information you wanted:

    Code:
    PARAMETERS P Short;
    SELECT TOP 4 tabPay.CODNOM, tabPay.CODPER, tabPay.Pay
    FROM tabPay
    GROUP BY tabPay.CODNOM, tabPay.CODPER, tabPay.Pay
    HAVING (((tabPay.CODPER)=[P]))
    ORDER BY tabPay.CODNOM DESC;


    Let's assume for the sake of this example this query is named Q4P

    You could get a column stating the average by doing this:

    Code:
    SELECT *, DAVG([Numberfield]) as MyAvg FROM Q4P
    where NUMBERFIELD is the field you want to average. Then your domain function is actually looking at a very small dataset to perform it's function and should be relatively quick (I do not like using domain functions unless I have to because they can be very slow). This would give you a query with your original columns, plus one showing the average for the domain.

  5. #5
    AWADOS is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2019
    Posts
    5
    I could't do it beacause of the parameters error. I changed the select query to an action query, save the records in a table and then a read the parameters from the table. I'm new at this, that's why I use solutions that are not very professional or elegant.

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

Similar Threads

  1. Can't find source of Error #2471
    By GraeagleBill in forum Programming
    Replies: 3
    Last Post: 10-23-2017, 02:28 AM
  2. Replies: 4
    Last Post: 03-27-2017, 02:06 PM
  3. Runtime Error 2471
    By Thompyt in forum Programming
    Replies: 9
    Last Post: 12-29-2016, 04:56 PM
  4. Replies: 5
    Last Post: 05-15-2015, 10:49 AM
  5. Replies: 5
    Last Post: 02-03-2014, 03:06 AM

Posting Permissions

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