Using a variable in a function within a query

[Guest]

Hi there,
I've been struggling with this for a while now and wonder if you can help me
out.
I've got a couple of functions that each return a date. There's
GetStartDate And GetEndDate. These two functions are referred to in quite a
large number of queries within the Where clause as 'Between GetStartDate()
And GetEndDate()'. However, as the dates could refer to one of a couple of
dates I thought I'd change the functions so that they require an integer to
indicate which date should apply (ie GetStartDate(1) would refer to
#1/4/2004#, GetStartDate(2) would refer to #1/1/2005#. But, I don't want to
specify which integer(date) should apply within the query but within some
code instead. How would I call the function from within the query design
grid so that the integer is 'obtained' from the code?
I'm getting in a muddle and can't seem to find a way out.

Many thanks (as always)

Lee

 

[Chris2]

Hi there,
I've been struggling with this for a while now and wonder if you can help me
out.
I've got a couple of functions that each return a date. There's
GetStartDate And GetEndDate. These two functions are referred to in quite a
large number of queries within the Where clause as 'Between GetStartDate()
And GetEndDate()'. However, as the dates could refer to one of a couple of
dates I thought I'd change the functions so that they require an integer to
indicate which date should apply (ie GetStartDate(1) would refer to
#1/4/2004#, GetStartDate(2) would refer to #1/1/2005#. But, I don't want to
specify which integer(date) should apply within the query but within some
code instead. How would I call the function from within the query design
grid so that the integer is 'obtained' from the code?
I'm getting in a muddle and can't seem to find a way out.

Many thanks (as always)

Lee

Lee,

It depends on what you mean be "obtained".

Here is one possibility:

SELECT *
FROM YourTable AS Y1.
WHERE Y1.YourDate
BETWEEN GetStartDate() AND GetEndDate();


Public Function GetStartDate() As Date

Dim dtmMyDate As Date
Dim intMyInt As Integer

intMyInt = 1 'Or some other way of setting intMyInt

Select Case intMyInt
Case 1
dtmMyDate = "01/04/2004"
Case 2
dtmMyDate = "01/01/2005"
End Select

GetStartDate = dtmMyDate

End Function


Sincerley,

Chris O.