I am a complete novice with little or no code writing experience. It is only with considerable help and guidance from members of this forum that I have reached the stage I am at now. Put simply, I now have a working query that calculates the days elapsed between RaceDate values returned in the recordset but it needs some minor tweaking. I have attached the qryResult3 notepad file for all to see. My problem now is only one of alignment. I need the RaceDate field to remain the way it is in decending order please!. I would like 09 Sept 02 to answer to read 0 and then 11 Nov 02 answer to read 63 then each calculation needs to be moved up one place ending with 24 Aug 04 answer to read 30 and that would be a perfect result. I have used Ineuw's following instructions to get me this far:
'This requires a function call from the query which passes the 1st value to a
'public function, stores that value in a holding variable, then passes the 2nd
'value and evaluates it with the holding variable, then replaces the new value
'in the holding variable and returns the result and so on . . .
'There also must be a mechanism which clears the holding variable before the
'first record of the recordset.
'To do this:
'1. Create a module and declare a public variable at the module level:
Public varHoldDate as Variant
'2. Create a Public Function to clear the variable:
Public Function fnClearHoldDate()' I name all my functions starting with fn.
varHoldDate = Null ' Clear the variable before running the query.
'Create a macro:
'3. the 1st macro line calls fnClearHoldVar():
'4. 2nd macro line calls the query:
OpenQuery . . . QueryName (I've used myqryname)
'5. In the query add a field:
'6. The date difference function call.
Public Function fnCalculateDiff(ByVal varNewDate As Variant) As Variant
' If variable is Null, place the 1st passed date in the holding variable
If IsNull(varHoldDate) Then
varHoldDate = varNewDate
fnCalculateDiff=0 ' 1st date difference is 0
fnCalculateDiff = DateDiff("d", varNewDate, varHoldDate) ' assign function's return value.
varHoldDate = varNewDate ' place new date into the holding variable for the next record.
Many thanks to all again. Kind Regards, Ronald A. Dixon
I have tried to send an email directly to you via the address you gave me but it keeps bouncing back and the mail delivery message reads some failure or other. My email message did read: Good Morning,
...my database may be far too big for me to email to you, it is 540mb in size. I am happy to do that if you think it is possible? Failing that, I have compacted and repaired it as you describe, I do have a policy of compact and repair regularly in any case. I tried opening the query by highlighting the macro and clicking on run. The macro fails and I get an Microsoft Access window which says: The expression you entered has a function that Microsoft Access can't find. I can run the query by double clicking the query itself and it works well. However, the RaceDate column is perfect and decending but the calculated values returned all just need moving up one place in the datasheet view.
Ronald A. Dixon
my email is: firstname.lastname@example.org
, t1.racedate - t2.racedate as days
, t2.somenumber as t2number
from racedates t1
, racedates t2
= (select max(racedate)
where racedate < t1.racedate)
order by 1;
now, granted, a join like that isn't going to be terribly efficient, but it runs pretty fast (less than a second) when i tested it, and it doesn't look like you have all that many dates (certainly not half a gig worth)
R937 Thank you for your contribution, but the table that my query is working on contains 47 different fields and a growing number of records. The total number of records to date is 460731. You will see that the present solution so kindly supplied by INEUW is achieving my aims almost entirely, it shows the RaceDate values in decending order which is what I want and it just needs tweaking a little to align the days elapsed values with the RaceDates. In an ideal world the most recent RaceDate will be at the top of my query in datasheet view (in this case 24 Aug 04) and the value aligned with that should be 30. The earliest date shown at the bottom of my query in datasheet view (in this case 09 Sep 02) would have a value aligned to it of 0 or Blank or whatever because there is no RaceDate before it. Hope this helps clarify things a little. Regards, Ronald A. Dixon
Eureka! we have it....congratulations to all who have helped out in the last couple of days. Can I give a special mention to Ineuw who took the trouble to contact me directly and has ensured I have a much improved database that is working absolutely perfectly (touch wood) I could not wish for better help and advice than I have received on this forum and I would like to take this oportunity to thank you all. Ineuw I cannot thank you enough. If there is anything I can do for you, please feel free to ask it. Kind Regards, Ronald A. Dixon