I probably have not been very clear. Following data entry into a form, I open a dynaset and undertake quite a lot of data processing (2 A4 pages in fact) which includes the calculation of 'points'. The value of the points is fine just as long as there are no ties (where [elapsed time] is the same for two or more records). ADezii showed me a way using VBA to calculate my tie values using data from a test table. And sure thing, it worked.
...
helm
Last Activity: Sep 20 '13, 03:13 PM
Joined: Mar 23 '07
Location:
-
Hi Rabbit and Adezzi
Back from my hols ...
From some tests looks like I can't Dcount on a recordset - which is a pity since I have it right there ready to go. I could close the recordset and then run Adezzi's solution against the original data table ... but did you both believe that I could achieve the same thing (identify ties and update the table with 'just' an SQL update query)?
Thanks for your continuing...Leave a comment:
-
Thank you Adezii and Rabbit,
The recordset I'm using has been through a range of activities before it gets to this (ties) point in the overall processing - I have quite a few 'with recordset' sections to test and answer various things ... I could now drop the recordset into a table and use SQL to work out the ties ... I wonder though whether it's possible to run SQL against a recordset rather than a table - sorry if sounds a bit of...Leave a comment:
-
A big thank you to ADezzi - I didn't know that there weretwo division operators! Thanks too for those SQL references - I'm going to look at those in more detail. As you say, an SQL approach is great if you can work out (or find out) how to do it.
Best Regards, helmLeave a comment:
-
Hi Adezii,
That's brilliant - finds ties and updates records, Can we take this a step further please? In your solution, all the ties are given the same value - great - but there are some values that would not be integers - if for example there were ties at [orig points] 5 and 6, the values to be assigned [points reqd]would be 5+6/2 = 5.5
Thought I could change through from integer values to single but results still come...Leave a comment:
-
Handling Ties in VBA
Hi Folks,
I have an Access 2003 application handling dinghy racing results. I'm looking for a way to handle ties. On the current first recordset pass, the results are fine if there are no ties (orig points). If there are ties, then they need to be handled by a process that recognises that ties exist (elapsed time) and adds together the original values (Orig Points) and divides by the number of ties to create new values (Points Reqd).... -
NeoPa
Thanks for the tip. I can now see that I need to Maximise on the activate event and minimize on the deactivate event. Problem solved. I'll play around a bit with this.Thanks again.
Regards, HelmLeave a comment:
-
Yes, your'e right. Goes back a year or so. I'm trying not to use any built in toolbars. There's an opening modal form used as a menu. This calls another form. There's buttons on this form to fire reports that open in print preview with a custom toolbar to offer PRINT and CLOSE functions. When using the custom close button, the calling form returns but with a gap at the top of the screen where the custom toolbar had been. I only get the full screen...Leave a comment:
-
NeoPa ... can I check on site etiquete?
There's a number of things that have puzzled me as I've worked up this application. I'd like to ask about them but don't want to overstay my welcome. Should I generate a number of new posts, one for each or perhaps put a couple at a time?
For example, why is it that a modal form (such as a menu) and set for a certain size in the middle of the screen and hidden on menu selection...Leave a comment:
-
I'm doing this application for my sailing club. The general committee insists on a 'pukka' licence so bought an Ofice 2007 3 user licence. Loading that onto the PC that will be located at the club, I found it necessary to double the memory just to get the performance back. I'm beginning to wonder if the best approach would be to have the licenced copy of office 2007 to show if required but stick to 2003 for the application!
I did wonder...Leave a comment:
-
All,
Just to complete this post, this works well. Not entirely sure why the double quotes works in the WHERE statement but it does (because it's "outside" SQL) ? Also decided against PARAMETERS - better to take the seriesID value from the calling form and run the resulting query as the report record source. About to move from Access 2003 to 2007 so fingers crossed!
Regards, Helm
...Code:Dim qdf As DAO.QueryDef,
Leave a comment:
-
NeoPa,
Perfect ! Exactly what I wanted. Searched the net and up till 2 a.m trying to solve this! Thanks so much. And thanks too for the posting advice.
Is there a points scoring system on the site?
Regards, Helm.Leave a comment:
-
Folks,
My report works fine. Last problem is to set a qdef to create the query. What works fine in the query developer bombs out on compliation in VBA - as below. It gives a syntax error on the SELECT statement. I think there may be two problems - getting the single and double quotes right and perhaps also because I have used a lot of empty spaces and a . (period) to create HELM values that can be broken down in the report to produce...Leave a comment:
-
NeoPa
Ah, 'Additional Options' not showing on my brower - neither Firefox nor Explorer.
There's Report and Reply in the box and Post REply below the box. Nothing else showing until "Quick Browse" . Something I've done?
Regards and thanks for your help
HelmLeave a comment:
-
NeoPa,
Sorry but I can't see any way to send an attachment -all I can see is 'Insert Image' but that just brings up this ... [IMG], Insert Hyperlink ... E Mail Link. I must be missing something ..
Regards, HelmLeave a comment:
-
Cracked it!
Use your access crosstab query as the record source for an access report. The report basically consists of unbound labels and controls which can be dynamically allocated to each record using the controls collection. Produce a recordset from the report recordsource.
That works. However, since my dates are in the format "dd/mm" the column order is based on the day rather than the month. Changed the...Leave a comment:
-
Hello Stewart and thanks for the response. Much as I thought. Access 2007 doesn't seem to have been updated in this area.
I can get some way with an Access report but am still stuck with 'fixed' column headers and the 'Jan to Dec' column header solution is not appropriate for 90% of the data (most have a limited lifespan of three months or so). I may be able to get close to 'all the data on the same line as the row value' by using...Leave a comment:
-
Does Access have Limited Crosstab compared with Excel?
Folks, could anyone advise ...
Is there a significant difference in crosstab capabilities in Access and Excel?
Using Office XP 2002 ... to produce a crosstab report I developed it in Excel from a query datasource in Access. One good thing about this was the ability to select both a date header and then a specific time header for the crosstab value. Try as I might, I cannot see a way to replicate this in Access either... -
ADezzi,
My apologies for this tardy response to your response. I'd expectd an email along the lines 'someone has responded to your post'. My fault entirely.
I did as you suggested and concluded that the answer to my problem was to set a test for each control that the user might select and take action accordingly (rather than to seek to do this inside the OnExit event.
Thanks and apologies again.
...Leave a comment:
-
SubForm OnExit Event
Folks,
I'm trying to interrupt an OnExit event if the user clicks on a particular control. Frankly not sure if the OnExit event allows this. Here's the position:-
There's a main and sub form. I'm looking at the OnExit event of the subform which offers just two events - OnEnter and OnExit. The OnExit event does some processing for all the records on the subform. Part of this processing tells the user that there are one...
No activity results to display
Leave a comment: