month and week criteria in a module

[bmal]

Hello. Plog was kind enough to get me started by responding to this question over in queries:

I am trying to return a value in an expression (call it FundedPeriod): CurrentWeek, CurrentMonth, PreviousMonth, based on a date value in field [funded_date].

Here are the criteria I am using:

Current Week: DatePart("ww", [Funded_Date]) = DatePart("ww", Date()) and Year([Funded_Date]) = Year(Date())

Current Month: Year([Funded_Date]) = Year(Now()) And Month([Funded_Date]) = Month(Now())

Previous Month: Year([Funded_Date])* 12 + DatePart("m", [Funded_Date]) = Year(Date())* 12 + DatePart("m", Date()) - 1

plog got me started with this:

It's time to move your logic to a function inside a Module. Instead of trying to jam all those comparisons into one line, create a custom function, pass it [Funded_Date] and use as many lines as you need to determine what the result should be. I'll start you out:

Function IsCurrentWeek(FD)
' returns True or false based on if FundedDate (FD) is in current week
 
Dim ret As Boolean
ret=true
' default return value is true, if fails any of below tests, its set to false
 
If (DatePart("ww", FD) <> DatePart("ww", Date()) ret=false
 
IsCurrentWeek = ret
 
End Function

Paste the above into a module, add more of your logic and then use it in a query by using this code:

CurrentWeek: IsCurrentWeek([Funded_Date])

Based on his example, I expanded upon that and came up with this:

Function FundedPeriod(FD)
' returns CurrentWeek or CurrentMonth or PreviousMonth or None based on FundedDate (FD) criteria
 
Dim ret As Boolean
ret=CurrentWeek
' default return value is true, if fails any of below tests, its set to false
 
If (DatePart("ww", FD) = DatePart("ww", Date()) ret=CurrentWeek
 
ElseIf Year([Funded_Date]) = Year(Now()) And Month([Funded_Date]) = Month(Now()) ret=CurrentMonth

ElseIf Year([Funded_Date])* 12 + DatePart("m", [Funded_Date]) = Year(Date())* 12 + DatePart("m", Date()) - 1 ret=PreviousMonth
 
FundedPeriod = ret

End Function

Am I in the ballpark w/ this in order to return:

CurrentWeek or CurrentMonth or PreviousMonth

so in the query expression I think I would type FundedPeriodName: FundedPeriod([funded_date])

Thanks for input.

 

[pbaldy]

More like:

ret="CurrentWeek"

You would likely want to pass the values to the function as well.

 

[bmal]

I think the main hurdle in my head is determining whether or not all three time periods can be addressed in one function or three separate functions are necessary.

Based on plog's example, I comprehend the true/false evaluation for a date in the Current Week period. However, I am struggling with how to handle the remaining periods in the same query expression.

In my head, I think the function would not be called one of the three periods, but something like FundedPeriod. But I admit I could be way off course.

So should I focus on learning three separate functions or one function with 3 criteria?

 

[pbaldy]

From the sound of it, I'd say one function that you passed all 3 values to. It would use logic to determine the appropriate return value based on those values.

 

[namliam]

First off, for using the datepart function "properly" you need to fill in all 4 parameters of it and not just the first two. The first two works, but may yield unexpected results.

Dont know how often you want to return this "function" if it is a one off you can use an iif in a query... Otherwize if you want it more often it may indeed be wize to stick it into the function.

problems with your function
1) Explicit
Functions and their input variables need to be defined to be "something" dont rely on access to make it up for you, it will screw you over
Function FundedPeriod(FD as date) as string
or if you want to return a boolean, for which your function wont work since you are returning "CurrentWeek"
Function FundedPeriod(FD as date) as Boolean

2) Help function is your friend
Help can teach you much more things faster than a forum can (just press F1)... Try looking up the If ... elseif in the help and you will find you are missing a "then" in there.

If .... then ret = ...
elseif .... then ret = ...
else 
    ret = "bad"
end if
[code]

3) Datepart
Datepart converts a date into another portion of a date ... like month or week...
If you want to calculate with dates though, consider using DateDiff or some other smart functions / calculations... Depending on your requirements there can be different ways of doing this but 
i.e. the first of "this month": 
- Date() - Day(Date()) + 1
- dateserial(year, month(date()),1)

Either will work better to determine your:
Year([Funded_Date]) = Year(Now()) And Month([Funded_Date]) = Month(Now())

And in a global sence when you are talking about DATEs never use Now, but rather Date... Now includes a timestamp which can again cause unexpected results if your not carefull (not the case here but in the grand scheme of things)

 

[bmal]

namliam , plog, and pbaldy - I appreciate the help on this. Will probably put this screen on the shelf for now.

I will revisit it in a couple of days and hopefully crank this out.