select case function on date/time

[Lindsey M]

Hi everyone, hope you are well

I am trying to write a function that I can use in my query to alocate
timeslots. Basically, I have data for each 15 min period and I want to show
it as half hourly. So, 22/09/2005 08:00:00 and 22/09/2005 08:15:00 would be
summed to show as 22/09/2005 08:00:00 and so on.

I tried this:
Code:

--------------------------------------------------------------------------------

Public Function timeslot(tstamp As Date)tstamp = DatePart("nn",
tstamp)Select Case tstamp Case 15 DatePart("nn", tstamp) = "00"
Case 45 DatePart("nn", tstamp) = "30"End SelectEnd Function
--------------------------------------------------------------------------------



but this errors saying "Invalid procedure call or argument".

If anyone could help with, I'd be very grateful - i need the solution asap!

Cheers
Linds

 

[Chaim]

Assuming all of this function showed up in the post, there are some basic
problems with the code:
1. You don't declare a return type- Public Function timeslot(...) as <return
type>
2. You don't assign a value to return- there should be a statement that
reads something like:
timeslot = <some value that has type return type>
3. Specifier for minutes is 'n' not 'nn'
4. You can't set the part of the date this way. DatePart() can't be used on
the left of the '=' sign.

You can try something along these lines:

Public Function timeslot(tstamp As Date) as Date
dim myMin as string, thisDate as string
thisDate = CStr(tstamp)
myMin = CStr(DatePart("n", tstamp))
Select Case myMin
Case '15'
thisDate = replace (thisDate, "15", "00")
Case '45'

thisDate = replace (thisDate, "45", "00")
End Select
timeslot = CDate(thisDate)
End Function

I'm not sure all of that conversion is needed, but this works.