Search results

vbaInet my friend you are brilliant. Thank you so much for all your time and effort. Best Regards Mike

if i look at the values on this statement TempDate = rsMain![EventDate] Tempdate shows as 00:00:00 rsMain![EventDate] shows as <item not found in this collection>

Ok I have done the following At the top of your code i have Dim TempDate as date Then i have placed TempDate = rsMain![EventDate] just before the set rscheck SQL statement. I'm still getting the same thing though - sorry to be a pain

Here's the DB with the datablock table, a form and your code

Yep table is definately saved SQL now reads as per your changes Set rsCheck = db.OpenRecordset("SELECT TOP 6 DataBlock.* FROM DataBlock " & _ "WHERE [EventDate] < " & rsMain![EventDate] & " ORDER BY [EventDate];") Still getting same error though

I've changed the table field 'date' to 'eventdate' so as to remove the reserved word issue but still get 'item not found in this collection' Set rsCheck = db.OpenRecordset("SELECT TOP 6 DataBlock.*, [EventDate] FROM DataBlock " & _ " WHERE [EventDate] < " &...

I think my SQL is correctly syntaxed but not sure

item not found in this collection

Hi, I'm just trying your code but get a Runtime error 3265 (item not found in this collection) on the rsCheck SQl part of it Set rsMain = db.OpenRecordset("SELECT DataBlock.*, DataBlock.Date FROM DataBlock ORDER BY DataBlock.Date;") Do While Not rsMain.EOF Set rsCheck =...

Thanks vbaInet - i'll give it a try - you're a star!

I thought i would use an array because the calculations based on the 6 selected records would use aggregates such as sum and count.

vbaInet is right in what he says. Why don't you just populate a 'Projects' table with your data (project name, id) The project details can be placed in a 'Project Details' Table????

Hi vbaInet, Sorry not making myself clear am i. Right let's start again. Imagine a table with say 100 records in it ordered by a date field. I need to cycle through each record, look at the date and see if there are 6 records prior to it. Obviously the first 6 records wont have 6 prior...

Sorry - yep same table!

Can you be a bit more specific? Are you using this for instance with a button on a form?

Hi, I am having a blind moment on this problem and hoped someone can help. I have a table that contains date specific events. I need to cycle through the records in the table and for each record update a field based on a stats calculation of data in the 6 previous records (date wise). So for...