Search results

Hi All I have only recently started to tackle subqueries, and am now stuck on this syntax: InThisPayrun: Exists (SELECT staffs.practice, stafpay.[month name], stafpay.staff_name FROM staffs INNER JOIN stafpay ON staffs.staff_name = stafpay.staff_name WHERE (((staffs.practice)= [Forms]![frm...

Hi delikedi That's it - removing the single quotes did the trick!! Very many thanks Les

Hi All This works: DoCmd.RunSQL "UPDATE tblResults SET tblResults.athlete_time = '" & prevTime & "';":) and this works: DoCmd.RunSQL "UPDATE tblResults SET tblResults.athlete_time = '" & prevTime & "' WHERE tblResults.athlete_number = 6;":) but this doesn't work: DoCmd.RunSQL "UPDATE...

Hi John Thanks for your reply. There seems to be more than one syntax for referring to subforms, and the one I was using does work - when used with the correct control names!! I have just discovered that my subforms are called 'child1', 'child2', etc, not [frmEnterResults] and [frmOFResults] as...

Hi All I know this is an 'old chestnut' - and it's one I thought I had cracked: but I'm getting an error and don't know why:mad: I have a main form [form1] with two subforms [frmEnterResults] and [frmOFResults]. The AfterInsert event for [frmEnterResults] is...

Hi Spikepl Thanks for your reply I have to say though that I didn't really undertsand some of it:confused:: 1. I'm not panicking! 2. I'm not sure why you say that I "seem not even sure whether the query runs or not": it always runs, perfectly, without the criteria, and it always hangs if...

Hi All This works fine, and take about 1 second to run: SELECT [qry x all staff].staff_name, DCount("[employee_submissions]![submission_serial]","[employee_submissions]","[employee_submissions]![employee] = """ & [staff_name] & """ and [employee_submissions]![submission_type] = ""NHSP...

Hello Pat Many thanks for coming in on this. Without boring you with the details, the 'year' field is a given - in that it stores the name of the 'year' as defined by HMRC (I think = IRS in the US). I need to store these 'year names' (and do so in table [months]. I realise that I could add two...

Hi RuralGuy Thanks again for your suggestion ... but that's not it either! I had in fact thought of this, and so had the ... If Not IsDate(DateIn) Then MsgBox (DateIn & " is not a valid date!!") Else ... at the start of the function. I'm beginning to think I'm making heavy weather of this...

Hi RuralGuy Many thanks for your further reply. I have made the amendments you suggested but unfortunately am not there yet! When I have ... YearOfDate = DMin("[year]", "[months]", "DateIn >= " & LowerLimit & " And DateIn <=" & UpperLimit) ... I still get the message about the object...

Hi RuralGuy Thanks for your reply. I tried amending the declarations as you suggested, but unfortunately that didn't work. When I have ... YearOfDate = DMin("[months]![year]", "[months]", DateIn >= LowerLimit And DateIn <= UpperLimit) ... the function runs, but the criteria is ignored...

Hi All I have the function 'YearOfDate' below, which runs OK except that - judging by the output - the criteria is being ignored. I know that the two 'nested' functions YearStart and YearEnd within 'YearOfDate' work perfectly. I should add that the field 'year' in table 'months' is a text...

Hi Craig and Spikepl Ah - found page 2 of this thread - and the latest posts from you both - many thanks for those - now I have two solutions: as spikepl suggested I can remove the 'likes's (and that's what I've done - as indicated earlier) or I can get the syntax for the quotes right, as per...

Hi Craig I'm pretty certain that I replied to your last post last week, but seemingly something went wrong because my reply is now not there! So apologies for that: what I had said was that I had decided to get rid of the 'like' operator altogether by adding more 'cases' so that I can cover all...

Hi CraigDolphin Thanks for your further reply. I tried amending the quotes as you suggested, but then on compiling got an error message about 'test' not being recognised, so I then added extra quotes around that, but all to no avail. As the problem seems to be due to using the 'like'...

Hi CraigDolphin I think your last post and mine crossed in the ether! I agree the Eval shouldn't be relevant here, and have removed it. Unfortunately I can't take you up on your kind offer of looing at the query and function together, as the accdb is massive and complex and it would take...

Ho Spikpl Thanks for coming back on this. I have done as you suggested and have established that the problem lies with the Set rsSubmissions statement. If I remove the two criteria from this that use the 'like' operator, everything's fine: if I include either one or the other of the two criteria...

Hi Jdraw Thanks for your further reply. [qry x all staff] is a separate query that is used - successfully - for various things, and is handy as a 'pre-filter' for the new query that I now need. The function will generally be used for 'on-the-fly' testing of the data in table...

Hi CraigDolphin Many thanks for your reply. I have amended the function to ensure that it returns 'False' when it doesn't return 'True', and I have tried using Eval in the query when I introduce the criteria ... but still no joy! The query that works perfectly is: SELECT...

Hi Spikepl I have found in the past that creating a function is simpler than repeating a Dcount on each occasion. In this case my function (once it works!) will also be expanded in various ways to allow alternative output to be generated. Finally, I have also found that opening a recordset is...