Search results

Hi All I have a query that uses a subquery to generate ranking: it is necessarily quite complex, because I need to rank teams of competitors across multiple races :eek: The underlying table [tblSavedResults] has separate records for each competitor for each race, and for each record there is a...

Hi plog Thanks for your reply - and apologies for the delay in my response - I've been away. Placing Val() around the Dsum didn't work - I got a syntax error. Multiplying by 1 did work though :) - so many thanks for that tip. Les

Hi All I'm fairly new to subqueries - but thought I was getting there until now :mad: I have RankSum: DSum("[tblSavedResults]![Athlete_category_rank]","[tblSavedResults]","[tblSavedResults]![Athlete_person_number]= " & [tblSavedResultss]![Athlete_person_number] & " ") I've aliased...

Hi All It's all fixed:D I can't believe I didn't get it sooner: obviously null 'employee' and 'period' values were going to be returned as I was selecting them from the part of the query where null was set as criteria :banghead: All sorted now. Thanks Les

Hi All (and MSAccessRookie in particular ;)) This is a re-post of a thread I started yesterday, but which is presumably one of many that have been lost due to a power outage at the site's data centre. The problem was that I have 2 tables [live] and [proposed] that have identical fields, but...

Hi I'm not sure whether there is a problem with this forum, but I stated a thread yesterday entitled ‘Returning records not there!’ in this access reports forum, and received a reply from MSAccessRookie, but can’t find the thread today :eek: The link that was emailed to me when the reply came...

Hi Sean/Pat Thanks for your replies. I hadn't realised that it's the office version that matters, so thanks for the info. I do in fact have a laptop with Office 64-bit, so still need to sort out the declarations. I know the app works on the laptop with the PtrSafe parameter added, because I've...

Hi All In the office we use an access2010 accdb, on 10 workstations all running windowsxp(32bit). As we start to replace the PCs they will come with windows7 or windows8 - 64bit. I did a global search and replace in all modules to add the PtrSafe parameter to all declarations, and the accdb then...

Hi All It's done :D In fact I ended up amending the initial recordsource for the form, using a couple of new functions. The suggestions received here were very helpful (partly for prompting me to go back to basics :rolleyes:), so many thanks to all of you. Les

Hi Sean Thanks for your reply. The new form is probably the way to go, and the link you gave is excellent: problem is I need to have this done today :eek:, and will have to either implement Rookie's suggestion - which I think I can do quickly, or stick with the existing query solution and do the...

Hi Rookie I've been thinking more about your suggestion, and am still trying to get my head round it :o I'm not in the office now, but is it a case of writing the function, say EOYPractice (strPractice), that opens the filter recordset that I now have (rsHMRCbatch) and then returns [prac name]...

Hi Bob I absolutely agree - there have been times when I've really gone off on a tangent and completely overlooked the more simple and obvious alternative :banghead: The issues in this case are that I need to: allow the user to provide various parameters at runtime, and then cater for the...

Hi Rookie Thanks for your reply. I think what you suggest might well suit my purpose, but I can't quite see how to do it :confused: Ultimately I need the form to be restricted to the records that meet the filter criteria - i.e. where [prac name] exists in the recordset rsHMRCbatch that is...

Hi All I need to apply a dataset (created in a vba sub) as a filter for a form. The normal recordsource for the form is a query ([qry_practices]), and until now the filter was applied as another query ([qry_eoys_to_do]) - so I had: Me.RecordSource = "SELECT * FROM [qry practices] WHERE [prac...

Hi CJ_London Now I'm really confused: :confused: I am doing this in VBA. The VBA is in an access module. The sub in the access module creates the excel file, and sets the validation for lots of columns - like the example I gave: 'Stop if Joining NHSP is invalid date 2010 With...

Hi CJ_London Thanks for your reply. I did think that instr might be involved, but had two problems: Strangely, although I have Office 2010 on my PC, the instr function seems not to be available. If I type =instr("Hello","e") into a cell, I get #Name. In the list of Text formulas (from the...

Hello All I have a module in an accdb that creates a complex excel file, and as part of this a lot of validation rules are applied to certain columns, e.g. 'Stop if Joining NHSP is invalid date 2010 With objSheet.range("W" & lngRowCount + 1).Validation 2020 .Add...

Hi Pat Many thanks for your reply. I had tried 'show all' on the relationships window, to no avail. I have though found the answer:D I hesitate to give it here, because it's a bit embarrassing, but I suppose I should: the table still existed in the backend database:eek: I can't believe I had...

Hello This is the weirdest problem I've had in a long time: interesting - but exasperating:banghead: I am being prevented from adding a new record to table [x confirmed] - when I try to do so I get a message that a related record is required in table [practicesBAD]. The table [practicesBAD]...

Hello Rx_ Thanks very much for your reply. Limiting the searched value to the left 25 characters worked! Not entirely elegant but at least I get the correct result - for now! I can see memos are perhaps to be avoided where possible - so will do that in future. Thanks again Les