Problem using report recordsource values within report events. Error 2427 1

[soloracer]

Hi folks,

Following last week's SQL solution (thanks combo and andy), I've come across another snag. A have a report that uses a cross tab query as it's record source. I use a particular field (sort1) in the report header to support the the report sort order and that works fine. However, I now want to use that sort1 value to do some other processing in the report events - right now, in the Report-Open event where I currently assign report labels and field values. But I haven't been able to find a way to reference the sort1 value. I've tried me!sort1, Me.[txtsort] (the control on the report that uses sort1 as it's record source (and that control works as intended)). Even complex references like Reports!seriesresults!txtsort and so on ...

The purpose of this piece of the work is to change the control source of 'Points'should certain other criteria come ionto play and hence why I want/need to use the sort1 value in the Open event where the field control source is stated.

The error message is "Run Time error 2427 You entered an expression that has no value". I've attached a few records from the record source.

TIA for your thoughts on this and best regards

Brian

 

[dhookom]

I would look at changing the value of [Points] in the query however I'm not sure what the calculation/logic is.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[soloracer]

Thanks Duane,

That sounds ideal! I think my problem is that the underlying querydef is already complex (at least for me). 'Points' is calculated within the a form querydef and is 'correct' unless the total number of races counted is >= sort1 at which point the value of 'points' should be represented by counting the lowest 'sort1' values of 'pos' ... so rather than the control source being 'points', the control source should be pointed elsewhere.

Right now, I get this elsewhere value by running another query and a DSum from within the report and seek to use that as the new control source.

If it's OK to put some code here ...

This is the report's underlying query from within a form ...

Dim qdf As DAO.QueryDef, strwall As String

strwall = strwall & " TRANSFORM Sum(tblResults.pos) AS SumOfpos "
strwall = strwall & " SELECT Count([pos])>=" & Int(Forms!wallchart!txttotalraces - Int(Forms!wallchart!txtabandoned)) \ 2 & " as sort1, IIf([sort1],[points],0) as sort2, "
strwall = strwall & " [membername] & Space(30) & '.' & [seriesname]& Space(30)& '..' & [tblseries].[rtc] AS HELM, count (tblResults.pos) as Raced, sum (tblResults.pos) as Points "
strwall = strwall & " FROM (tblRace INNER JOIN tblResults ON tblRace.raceauto=tblResults.raceID)"
strwall = strwall & " INNER JOIN tblSeries ON tblRace.seriesID=tblSeries.seriesID "
strwall = strwall & " WHERE (((tblRace.seriesID)= " & Me!Combo3 & "))"
strwall = strwall & " GROUP BY [tblresults].[membername],[tblseries].[seriesname],[tblRace].[seriesID], [tblSeries].[seriesname],[tblSeries].[rtc] "
strwall = strwall & " ORDER BY format([date],'mm/dd') & ' ' & format([tblresults].[time],'hh mm') "
strwall = strwall & " PIVOT format([date],'mm/dd') & ' ' & format([tblresults].[time],'hh mm');"

Set qdf = db.CreateQueryDef("qryWallQuery", strwall)
qdf.Close


On each row in the report, the value in the Points column would be 'points' in the query if sort1 is false (0) but if the value of sort1 is true(-1) then I need to use a different value. I can get this by running the following within report events ...

db.QueryDefs.Delete "qrywall2"
Dim qdf2 As DAO.QueryDef, strq As String

strq = strq & " SELECT TOP " & Int((Forms!wallchart!txttotalraces - Forms!wallchart!txtabandoned) / 2) & " tblResults.pos, tblResults.membername "
strq = strq & " FROM (tblRace INNER JOIN tblSeries ON [tblRace].[seriesID] = [tblSeries].[seriesID]) INNER JOIN tblResults ON [tblRace].[raceauto] = [tblResults].[raceID] "
strq = strq & " WHERE ((([tblSeries].[seriesID]) = " & Forms!wallchart!Combo3 & ") And (([tblResults].[membername]) = " & Chr(34) & strname & Chr(34) & " ))"
strq = strq & " ORDER BY [tblResults].[pos];"

Set qdf2 = db.CreateQueryDef("qrywall2", strq)

Dim inttop As Integer
inttop = DSum("pos", "qrywall2")


The value of inttop would then be the new control source. I hasten to say that this is my logic and has yet to be proven valid.

Duane, I'm sure that this looks very messy to you. I could imagine (but not produce) an underlying report query that might do all this in one go. If it helps at all, I've attached a report output as it stands now.

In the report,the value of sort1 is 7. For Brian Johnson, as he has 7 or more races to his credit, the value of the lowest Points = 1+1+2+2+2+2+2 = 12. For Tom Harris, since he doesn't have 7 or more races to his credit, all his points are included. As you can see, the lower the points, the higher the position.

Thanks for taking an interest and best regards, Brian

 

[dhookom]

So I'm attempting to figure this out. Your points to display is based on the N top finishes per racer where N comes from a control on a form.

Apparently you are okay with updating the SQL property of a query. I don't have your data but considering the Northwind that has an Orders table with EmployeeID, Freight, and OrderID. These might be like Member, Pos, and RaceID. The following would return the sum of the 5 lowest Freights for each EmployeeID as well as the count of orders.

SELECT Orders.EmployeeID, Sum(Orders.Freight) AS SumOfPOS, Count(Orders.OrderID) AS CountOfRaces
FROM Orders
WHERE Orders.OrderID In (SELECT TOP 5 ORDERID FROM Orders O WHERE O.EmployeeID = Orders.EmployeeID ORDER BY O.Freight ASC)
GROUP BY Orders.EmployeeID
ORDER BY Orders.EmployeeID, Sum(Orders.Freight);

I think you could use DAO code to replace the "TOP 5" with whatever to get the points and join this query to your orginal query.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[soloracer]

Hi Duane,

I see what you mean - I hadn't thought to use Northwinds! I used that example in a query adding my other table relationships with some test values. It does give the Sumofpos and Counntofraces but oddly not for the member(employeeID) selected ... the values returned seems to be the first record one would get if one queried for an alphabetical list of all the membernames(employeeID).

SELECT tblresults.membername, Sum(tblresults.pos) AS SumOfPOS, Count(tblresults.raceID) AS CountOfRaces, tblSeries.seriesname
FROM (tblSeries INNER JOIN tblRace ON tblSeries.seriesID = tblRace.seriesID) INNER JOIN tblresults ON tblRace.raceauto = tblresults.raceID
WHERE (((tblresults.raceID) In (SELECT TOP 5 raceID from tblresults O WHERE O.membername = tblresults.membername ORDER BY O.pos asc)))
GROUP BY tblresults.membername, tblSeries.seriesname
HAVING (((tblresults.membername)="Nick Blore") AND ((tblSeries.seriesname)="Autumn Series"))
ORDER BY tblresults.membername, Sum(tblresults.pos);

Brian

 

[dhookom]

Where are you using this SQL? I didn't want to suggest you use it to create a recordset in report event code.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[soloracer]

Hi Duane,

Following your initial response, I'd really like to get the value of 'Pos' from the report record source rather then from the report event code. I took your 'Northwind' SQL, added some tables and relationships and hoped to see the TOP N values returned. That's not working as yet, I've got somehting wrong.

The original SQL gives values for Pos for all records and these are shown, by date/time within the report. That's fine. SumofPos gave the total of all the records. The issue I am trying to resolve is that whilst all the records should be shown by date, ONLY the Top N asc should be used within SumofPos - essentially, only the lowest N records are used to determine the 'best' scores with as you say the value for N derived within from within the SQL in the form.

SumofPos is probably a misnomer. To try to be clearer, where I am trying to get to is that SumofPos should be the sum of the lowest N values rather than the sum of all the values of Pos. I'm note sure about TOP N ... I'm hoping that it will return a value even if there are not N values in the underlying data.

In the attached report where N = 8:-
for Nick Blore, Points should be = 8 and raced is correct at 8
for Mike Harris, Points should be 14 and raced is correct at 15

Thanks again Duane

Brian

 

[dhookom]

I don't know why you had the membername="Nick Blore" in your query. I was expecting the query to return every member, not just one.

Is the points you want to calculate across every series or only the one series?
What do you see with this query?

SELECT tblresults.membername, Sum(tblresults.pos) AS SumOfPOS, Count(tblresults.raceID) AS CountOfRaces, tblSeries.seriesname
FROM (tblSeries INNER JOIN tblRace ON tblSeries.seriesID = tblRace.seriesID) INNER JOIN tblresults ON tblRace.raceauto = tblresults.raceID
WHERE tblresults.raceID In (SELECT TOP 5 raceID from tblresults O WHERE O.membername = tblresults.membername ORDER BY O.pos asc)
AND tblSeries.seriesname = "Autumn Series" 
GROUP BY tblresults.membername, tblSeries.seriesname
ORDER BY tblresults.membername, Sum(tblresults.pos);

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[soloracer]

Hi Duane,

Huge apologies, I hadn't received your response in my Inbox ... just looked at the forum and there is was!

membername = Nick Blore was just me trying to find an answer and clearly not the thing to do.

The points I'm seeking are for just one named series each time the report is run. In this case the Autumn Series. When I run the query I get the following results ... I've put them in a spreadsheet and attached it along with the values that I hoped to receive when the TOP = 5. The majority are correct and some clearly not even close. Do you think it would help my case if I uploaded a small test db with just one or two series so that you could look at the data? Not sure if this helps but the value of 'Raced' would/should always be the same no matter what value of TOP is wanted ...

Thanks for your continued support

Brian

 

[dhookom]

Try;

SELECT tblresults.membername, Sum(tblresults.pos) AS SumOfPOS, Count(tblresults.raceID) AS CountOfRaces, tblSeries.seriesname
FROM (tblSeries INNER JOIN tblRace ON tblSeries.seriesID = tblRace.seriesID) INNER JOIN tblresults ON tblRace.raceauto = tblresults.raceID
WHERE tblresults.raceID In (SELECT TOP 5 raceID from tblresults O WHERE O.membername = tblresults.membername AND O.RaceID = tblResults.RaceID ORDER BY O.pos asc)
AND tblSeries.seriesname = "Autumn Series" 
GROUP BY tblresults.membername, tblSeries.seriesname
ORDER BY tblresults.membername, Sum(tblresults.pos);

If that doesn't work consider posting a stripped down Access file.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[soloracer]

Hi Duane,

The SQL seems to give the same results as the querydef within the form that drives the report. As suggested, I've stripped the .mdb to just the tables, queries, form and report concerned and also stripped most of the other data columns in the tblresults to make it as clear as possible. tbleresults has just enough records for the Summmer and Autumn Series.

frm wallchart selects the series required, finds the number of races to count (TOP) and assembles the querydef as a crosstab query

rpt series results assigns labels and fields for the report and displays all the data, by date/time along the top X axis. It correctly shows the number of races and points (sumofpos) for each membername. Rather than Points being SumofPos, what I hope to achieve for Points is the TOP N value where N is the value determined in the querydef. Alternatively, could keep SunofPos as a column and use another column to represent the TOP value. Either would be great.

If I can get this right then, for the Summer Series, Nick's TOP would be 5 and Martin's 8. For Autumn, Nick's TOP would be 9, Mike's 14 and Paul's 21.

As you said earlier, it would be much better to seek to achieve this in SQL rather than floundering around in the report events!

with best regards, Brian

 

[dhookom]

The subquery using the TOP 5 will return more than 5 rows if there are ties which was causing an issue. I added ResultID to the ORDER BY.

Try this SQL to get the SumOfPos:

SELECT tblSeries.seriesID, tblresults.membername, Sum(tblresults.pos) AS SumOfPOS, Count(tblresults.raceID) AS CountOfRaces, tblSeries.seriesname
FROM (tblSeries INNER JOIN tblRace ON tblSeries.seriesID = tblRace.seriesID) INNER JOIN tblresults ON tblRace.raceauto = tblresults.raceID
WHERE (((tblresults.raceID) 
   In (SELECT TOP 5 raceID from tblresults O INNER JOIN tblRace R ON O.RaceID =  R.RaceAuto 
   WHERE O.membername = tblresults.membername AND R.SeriesID = tblRace.SeriesID 
   ORDER BY O.pos asc, O.ResultsID)) 
 AND ((tblSeries.seriesname)="Autumn Series"))
GROUP BY tblSeries.seriesID, tblresults.membername, tblSeries.seriesname
ORDER BY tblresults.membername, Sum(tblresults.pos);

For count of races are you looking for the number of races for the member in the series? If so, I think you need to use another subquery that is not limited to the top 5.


Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[soloracer]

Duane,

Sumofpos is now correct!! And yes, you are right, the number of races would probably be different for each member in each series. I guess another subquery would be something like AND WHERE ... between the AND ((tblSeries.seriesname) = "Autumn Series")) and the GROUP By statement?

There's light at the end of the tunnel. I've spent some hours trying to insert your SQL logic into my original Cross Tab query between the Transform and Pivot statements. No luck but then I'm in unknown territory ... I initialy thought, in my ignorance, that changing the value of SumoPos if another condition were true might be relatively "easy" ... Not so! But ... it still seems *right* to try to handle this in SQL rather than within the report.

Thanks again

Brian

 

[soloracer]

Hi Duane or indeed anyone who would like to take an interest.

Trying to take Duane's SQl above (which finds the correct value TOP using a sub query) to the next step which is to get this working as a crosstab and then add the more complex elements on a step by step basis. Running this crosstab results in the error "The Microsoft Jet Database engine does not recognise tblresults.membername as a valid fieldname or expression" yet that field is included/works fine in the original select query. I've read about the need for parameters to be stated but my original crosstab didn't use/need parameters. Can you see why I get this error from the following sql?

TRANSFORM Sum(tblresults.pos) AS SumOfPOS
SELECT tblresults.membername
FROM (tblSeries INNER JOIN tblRace ON tblSeries.seriesID = tblRace.seriesID) INNER JOIN tblresults ON tblRace.raceauto = tblresults.raceID
WHERE ((([tblresults.raceID]) In (SELECT TOP 8 raceID from tblresults O INNER JOIN tblRace R ON O.RaceID =  R.RaceAuto     WHERE O.membername = tblresults.membername AND R.SeriesID = tblRace.SeriesID     ORDER BY O.pos asc, O.ResultsID)) AND ((tblSeries.seriesname)="Autumn Series"))
GROUP BY tblSeries.seriesID, tblresults.membername, tblresults.membername, tblSeries.seriesname
ORDER BY tblresults.membername, Sum(tblresults.pos)
PIVOT tblresults.raceID;

Thanks all, Brian

 

[dhookom]

I revamped your report and method. The attached file has only 2 significant lines of code. One to set the SQL property of a TOP query and the other to open the report.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[soloracer]

Duane, that's a masterclass in how to approach a complex problem and goes to show what you can do in SQL rather than heaps of VBA! Conscious that this must have taken you a great deal of time and I'm very grateful. I'm now running through the pieces to learn how I can apply this logic elsewhere in the RMS.

Thanks so much, Brian

 

[dhookom]

I have been using this type of crosstab logic for decades and it has proven to be efficient and reliable. As much as I like code there are often better alternatives.

I typically have to add a column to a table or create a separate column headings table. You already had a racenum value that could be used to create the column headings which was convenient.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[soloracer]

Re I have been using this type of crosstab logic for decades

Yes, I was Googling around last night looking for reasons why my crosstab was erroring out and found something similar with your name on it from 2007!