Search results

Thanks Insane_ai I eventually got this to work using the RunCode. One of my mistakes was I had the VBA written as a Sub instead of a Function

Hi I'm wanting a macro that will erase the contents of 12 tables (but NOT delete the tables) in one go i.e. I don't want the user to have to click 12 delete queries or have 12 delete queries visible in the Object Navigation Pane. I instead want one macro that will do it all. In the macro...

I tried it and it doesn't give the right result if there are values >10 with how they are formatted as text works if there were no "None in force" records. I don't get why a "None in force" record is stopping it from working when I'm telling it not to do any Max/Val operations to such records.

Hi arnelgp That would work if the YEAR field was formatted as number. Apologies how just before you posted, I updated the question to mention that the YEAR field is formatted as text and I had used the Val function in the query to try and get around that........but the IIF, Max, Val don't seem...

Hi Gasman Sorry I painted bit of a misleading picture there. So I've now updated the question to match below. Year is a text field (despite containing a number) and the expression I'd written for duration actually says Duration: IIF([PROGRAM] = "None in force", NULL, Max(val([Year])))...

Hi See attached spreadsheet for an illustration. But note, the values in ProgramDetails are Text in the real database For quick reference, a slimmed down version of the data looks like this: ProgramList PROGRAM.......START_DATE X-ABC123....... 12/04/2012 None in force...

Thanks everyone especially DBGuy It does the job great, but I'll spend a bit longer trying to understand why it's working

Thanks CJ London That's what the data is like unfortunately. It's as if I'm expected to take the STOP_DATE with a pinch of salt. Or another of way of seeing it is, if the two weren't equal then I would be expected to intepret it as being a SQUAD_SIZE of zero for one day. This "condensing...

Hi Pretend I've got 01/01/2000 - 01/01/2004 33 players in squad 01/01/2004 - 01/01/2007 25 players in squad 01/01/2007 - 01/01/2009 25 players in squad 01/01/2009 - 01/01/2012 47 players in squad With how it didn't really change on 01/01/2007, is it difficult to write a...

Hi June I've given this more thought and annoyingly I still can't see why I'm getting odd results. Any chance you could have a look please at the attached where I'm showing what each sub-query is resulting in on my computer? I don't get why Query 1 is resulting in From = 01/04/2011 To =...

Thanks June, some interesting code there :) It doesn't quite give the right answer at the moment. But I think it has given me some ideas that I'll explore tomorrow. Attached is a screen print of the results it's giving at the moment

Thanks Plog Spot on, that's the complication that I think has stopped me from being able to write a query yet and I was nervous about First and Last. I think you might have confirmed what I was fearing :eek: of SQL not easily behaving that way. But fingers crossed if anyone has any more ideas

Thanks for getting back to me I've now uploaded a spreadsheet showing the data. Re: June7 I wouldn't be surprised if some sort of crosstab ends up being the solution. I've used crosstabs before but I haven't managed to figure out how to correctly set it up for this data/objective.

Hi, I don't think I'm quite at intermediate level yet with writing queries so I am a bit out of depth with this one. I've got data that tells you Team A was doing 50% of the work between 01/04/2011 and 12/01/2017 Team B did the other 50% between 01/04/2011 and 14/11/2015 but on...

I can't unfortunately. And this was just a dummy/simplified scenario that I've described.

Hi I'm fairly new to Access. All I can do so far is tweak an existing query or setup the most basic select query. I'm trying to setup what I thought would be a basic query but I'm puzzled. There's various select queries containing useful and useless results. I want to create a select...