Return Date field result depending on which half of any year (1 Viewer)

[ascaife]

I have a date field [CourseDate] that I need to return as:

yyyy01 if the record falls between 1st Jan and 30 June of any year

or

yyyy02 if the record falls between 1st Jul and 31 Dec of any year

Eg. If a course was on 03/02/2009, result would be 200901 etc.

 

[Mr. B]

Here is a public function you can use to do what you want:

Public Function RetValForDate(CourseDate As Date) As String
If Month(dtCourseDate) <= 6 Then
    RetValForDate = Str(Year(dtCourseDate)) & "01"
Else
    RetValForDate = Str(Year(dtCourseDate)) & "02"
End If
End Function

You would call the function pasing the Course Date value

 

[ascaife]

Can I use a public function in a query? I wasn't sure how to do this so I used your idea to enter an iif statement in the query.

CourseYearPeriod: IIf(Month([dtCourseDate])<=6,Year([dtCourseDate]) & "01",Year([dtCourseDate]) & "02")

Should I do it some other way?

 

[Galaxiom]

Yes, public functions can be used in queries.
Just put the code in a standard module.

It is certainly a lot easier to read the query with a function than complex code inside the query.
You can use the function anywhere in your project.
The function can be corrected or enhanced in one place rather than having to fix complicated code in multiple locations.
Results in an open query will immediately change when a function is edited without rerunning the query.