call of function

[hermie]

I have created next module:

Public Function calperiod(persemana As Date) As String
Select Case persemana
Case Is >= "08/04/04" <= "10/08/04"
calperiod = "10"
Case Is >= "10/09/04" <= "12/31/04"
calperiod = "20"

End Select
End Function

When I call the module in a query it shows 10 for all dates bigger then
10/09/04 instead of 20?

What goes wrong here?
Any help would be appreciated

Herman

 

[ChrisJ]

Not sure what you are trying to do

you are using a date (persemana) in a comparison with a boolean ("08/04/04"
<= "10/08/04")

The boolean evaluates to either -1 or 0 and the date (persemana) will always
be greater than this, unless it is pre 1900

 

[hermie]

What i want to do is to put a period code in my query through a function

The function must place the code in the query.
eg. when a datefield in my query is 23-oct-04 Then the calfunction should
place this code in the expression field.
period: calperiod(mydatefield)

In my example it should be 20

Herman

 

[George Nicholson]

Notes: 1) Select Case statements only execute the 1st True condition, so you
can simplify a bit.
2) Use # to indicate date values rather than quotes, which indicate
text strings.
Alternatively you could use : Case Is <= CDate("08/04/04").
As it stands now you are comparing apples (dates) to oranges
(text)
and will get unexpected results.

Try:
Select Case persemana
Case Is <= #08/04/04#
'??
Case Is <= #08/08/04#
calperiod = "10"
Case Is <= #12/31/04#
calperiod = "20"
Case Else
'??
End Select

HTH,

 

[hermie]

Thanks George

Your solution works great
I tried to use the solution with a between command of the dates to get the
value. But I see now that is not necessary.
I learned a little more now.

Herman