Save value from query. Error 2471

[AWADOS]

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

[rpeare]

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 & "'")

[AWADOS]

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.

[rpeare]

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:

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:

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.

[AWADOS]

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.