Search results

Hi Spikepl Thanks for your reply. I should perhaps have said that this function is still 'in embryo' - ultimately it will be used as part of a checking procedure to establish whether or not certain records exist in a certain table (and there will be many more 'cases' to add), with runtime...

Hi All I have written the funftion below, which works fine and gives the expected results when used in a query ... until I add a criteria to the field that's using the function: doing this causes the query to hang, with the very helpful "too complex" message on debugging:D The criteria I added...

Hi Trevor Apologies for not getting back to you yesterday - internet problems! It's all fixed!!:D:D The problem was that after assigning the first two values the active sheet somehow switched to the second sheet - all of which is protected. This was remedied by changing .Sheets(6).Activate to...

Hi Trevor OK - half way there!!:o The first two assigments now work fine - with the cells referenced to the names (e.g. "ActPay2013" instead of D8) but The second two assignments are to cells that don't have 'names', and the code errors when trying to make these assignments...

Hi Trevor Now this is becoming annoying: with the sheet reference corrected (thanks for that!), I now get a message that the cell or chart is protected .... , and I can see that all but 4 cells are indeed protected, BUT cell D12 is one of the 4 that is not (I can edit it manually) but even when...

Hi Trevor Still not quite there: I think the problem is my syntax with the line .Sheet6.Activate 'Use sheet real name The code errors on this line with runtime error 438 - object doesn't support this property or method. I have attached the source file for you to see: the cells I need to...

Hi Trevor Many thanks for your continued help with this. Your code compiled fine, and ran to the line .wksh("Inputs").select 'Used wksh then errored with runtime error 438 - object doesn't support this property or method. Having defined wksh, should I be setting a value to it somehow...

Hi Trevor Many thanks for your reply - fantastic, as I think I'm almost there with your code, but there are two problems: 1. When I added the code and tried to compile, I got a "Compile error - expected Function or Variable" on the .Save part of xlApp.ActiveWorkbook.Save = True. I do have...

Hi All I have a form with a button whose event procedure currently copies a certain excel spreadsheet to a known path and file name, and then it opens the spreadsheet. The user then enters 5 values into 5 specific cells (always the same cells), then closes the spreadsheet, then clicks a second...

Hi GlaxiomAtHome That did it - many thanks!! Les PS I wonder where the 8 came from?

Hi All I have: Dim rstS As DAO.Recordset Dim NumberToDo As Integer Set rstS = CurrentDb.OpenRecordset("select * from [qry bulletin recipients]") NumberToDo = rstS.RecordCount The problem is that [qry bulletin recipients] returns 4 records (on my test data) but rstS.RecordCount seems to...

Hi Beetle txtEmailTo is indexed, email is not - and I won't be able to do that until I'm back in the office in the morning (UK time!), but will reports back then. Thanks again Les

Hi Beetle I did as you suggested and the 'sub query' runs fine, almost instantly. But when it is used as part of the main query, the main query hangs. If I remove the 'sub query' from the main query, the main query run fine (but returns the wrong results, of course). Does this help? I've...

Hi Beetle Many thanks for your input. I haven't seen a query with a sub-query like this before - but it 'feels right' (to the extent that I have any kind of 'feel' for these things:rolleyes:)! However, when I tried to run your suggested code it took ages to run - in fact I gave up after a...

I'm no expert, but I'd say that some of the other data for the problem neurologist is casuing the problem - perhaps there's a division by zero, or a date that's been entered as text (I've had that error message for both these reasons)? Have a close look at all the other fields for the problem...

Hi MSAccessRookie I really appreciate your help with this: but we're still not there! I tried your suggested code, and I amended what I think was a typo - after the ON bit. When I ran that query it returned thousands of records, whereas there should be a maximum on 94 on the current data...

Hi MSAccessRookie I'm not in the office now so cannot test your suggested code, but I think the logic may still be wrong: Where you say "So in effect you need all records from 'qry bulletin recipients' that also have a record in 'tblEmailLogs'. ", it isn't necessarily true that for the required...

Hi Jdraw: the sql for [qry bulletin recipients] is: SELECT practices.[prac name], practices.email, practices.live, practices.potential FROM practices WHERE (((practices.email) Is Not Null) AND ((practices.live)=True) AND ((practices.potential)=False)); MSAccessRookie: I can confirm that...

Hi All I need a query that will return any records from 'qry bulletin recipients' that do NOT have an appropriate record in 'tblEmailLogs': appropriate, for this purpose, means that the field [memBody] in 'tblEmailLogs' contains the text "PayeDoc is on the move" and also that the field [dteSend]...

OK - bit of an update! It would appear that there are a few very strange values in [Sickness - Start Date] and [Sickness - End Date] - by which I mean that, for the 'strange' records: Looking at the raw data there appears to be nothing Len([Sickness - Start Date]) returns a zero (whereas...