Function ignoring criteria!

[Les Isaacs]

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 field in a very specific format, and so this can't be calculated by simply incrementing the DateIn parameter.

My function:
Public Function YearOfDate(DateIn As Date) As Variant
If Not IsDate(DateIn) Then
MsgBox (DateIn & " is not a valid date!!")
Else
Dim LowerLimit, UpperLimit As Date
Dim strCriteria As String
LowerLimit = YearStart(Format(DateIn, "mmmm yyyy"))
UpperLimit = YearEnd(Format(DateIn, "mmmm yyyy"))

YearOfDate = DMin("[months]![year]", "[months]", DateIn >= LowerLimit And DateIn <= UpperLimit)
End If
End Function

I suspect the problem lies with the delimeters in ...

YearOfDate = DMin("[months]![year]", "[months]", DateIn >= LowerLimit And DateIn <= UpperLimit)

... and have tried ...

YearOfDate = DMin("[months]![year]", "[months]", "DateIn >= '" & LowerLimit & "' And DateIn <= '" & UpperLimit & "'")

... and some other variations, but cannot get it right!
Hope someone can help.

Many thanks
Les

 

[RuralGuy]

You should be aware that this line:
Dim LowerLimit, UpperLimit As Date
creates UpperLimit as a Date and LowerLimit as a Variant! It should be:
Dim LowerLimit As Date, UpperLimit As Date
...in order to do what I think you wanted.

 

[Les Isaacs]

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.

When I have ...
' YearOfDate = DMin("[months]![year]", "[months]", "DateIn >= '" & LowerLimit & "' And DateIn <= '" & UpperLimit & "'")
... the function error, with runtime error 2766 - the object doesn't contain the autiomation object 'DateIn'.

Any ideas - this is driving me nuts!!

Thanks again
Les

 

[RuralGuy]

Your first argument should *not* include the table name:
DMin("[year]", "[months]", DateIn >= LowerLimit And DateIn <= UpperLimit)
...and the condition must be a valid string:
DMin("[year]", "[months]", "DateIn >= " & LowerLimit & " And DateIn <=" & UpperLimit )

 

[Les Isaacs]

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 not containing the Automation object DateIn

The I tried adding a space after the last =, so I had ...
YearOfDate = DMin("[year]", "[months]", "DateIn >= " & LowerLimit & " And DateIn <= " & UpperLimit)
... but then got
"Runtime error 3252 - cannot open a form whose underlying query contains a user defined function that attempts to set or get the forms recordset clone property"!!??

What on earth ...? How can this be so awkward?

Hope you can help.

Thanks again
Les

 

[RuralGuy]

Is DateIn a valid dateTime field in the Months table/query? Try:
YearOfDate = DMin("[year]", "[months]", "DateIn >= #" & LowerLimit & "# And DateIn <= #" & UpperLimit & "#")

 

[Les Isaacs]

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, and that there's probably a much simpler solution. Essentially the table [months] has a field 'year' (reserved word, I know, will change it one day!), that's always in the form "6 April 2005 to 5 April 2006": this is not a key field (so each value of 'year' appears in many records), but every record in the table has a 'year' value in this precise form - the only variation in the value is in the 4 digits of the year number (so there's ...
6 April 2005 to 5 April 2006
6 April 2006 to 5 April 2007
6 April 2007 to 5 April 2008
etc. etc.
I need the function to find the (minimum) value of 'year' that includes the argument date supplied: note however that the actual date range of, say, 6 April 2005 to 5 April 2006 is 1 April 2005 to 31 March 2006 - so for any argument date supplied between 1 April 2005 and 31 March 2006 the function must return 6 April 2005 to 5 April 2006!

I hope this makes sense - it's not as complicated as I've probably made it seem.
I'd be extremely grateful for any further help - either with my original function, or a more intelligent alternative!!
Thanks as ever
Les

 

[Les Isaacs]

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 further fields to [month] to store the actual lower and upper limits of the 'year' values, but as these would simply be calculated values (using the Mid and CDate functions), I had though this would be the 'wrong thing to do' - and that it was better to get the lower and upper limits of the 'year' values as and when needed ... like now!

I still can't quite see how to do this in my function, however. The point is, I need to find the 'year' value (which, as you say, is a string and not a date/time) that 'includes' the given date argument (where 'includes' is subject to the slight peculiarity I described in my last post - i.e. where "6 April 2006 to 5 April 2007" actually represent the range from 1 April 2006 to 31 March 2007.

Does that better explain what I need? Hope so!
Thanks again
Les