Custom Function to Create Quarter Date Range Doesn't Work In Query

[WeekendCoder]

Hello All,

I'm using the follow block of code to create a three year date range for a particular quarter.

Public Function quarterDateRange(quarter As Integer) As String
    Dim startDate As Date
    Dim endDate As Date
    If (quarter = 1) Then
    
        startDate = CDate("1/1/" & Year(Date))
        endDate = CDate("3/31/" & Year(Date))
        
    ElseIf (quarter = 2) Then
    
        startDate = CDate("4/1/" & Year(Date))
        endDate = CDate("6/31/" & Year(Date))
        
    ElseIf (quarter = 3) Then
    
        startDate = CDate("7/1/" & Year(Date))
        endDate = CDate("9/31/" & Year(Date))
        
    ElseIf (quarter = 4) Then
    
        startDate = CDate("10/1/" & Year(Date))
        endDate = CDate("12/31/" & Year(Date))
    
    End If
       
    quarterDateRange = "Between #" & startDate & "# And #" & endDate & "# Or " & _
                       "Between #" & DateAdd("yyyy", -1, startDate) & "# And #" & DateAdd("yyyy", -1, endDate) & "# Or " & _
                       "Between #" & DateAdd("yyyy", -2, startDate) & "# And #" & DateAdd("yyyy", -2, endDate) & "#"
End Function

Then for example, if I run it in VBA as a test, such as

quarterDateRange(1)

i'll get the following:

Between #1/1/2017# And #3/31/2017# Or Between #1/1/2016# And #3/31/2016# Or Between #1/1/2015# And #3/31/2015#

which is exactly what I would want. If this output is pasted into my query under the date criteria it works. HOWEVER, using the function with an integer parameter the query returns nothing.



Any idea why the equivalent information returns different results.

Thank you,
Weekend Coder

[aytee111]

What exactly are you trying to do here? If you have a date you can see what quarter it belongs to by using DatePart("q",datefield), then an additional criteria to say the date must be less than 4 years old.

[WeekendCoder]

Actually, just the opposite. I want to take an integer, 1-4, depending on the quarter and create a where clause to limit the results to the specific quarter over the last three years.

[WeekendCoder]

The function I've created works and outputs the where clause I need, however, using the function in the query doesn't work while pasting in the output directly into the query does.

[aytee111]

When I use it in the criteria field it puts an "=" in front, so you get =Between.... which is incorrect. How are you running the query? You may need to create the SQL in VBA prior to running it and insert the criteria from the function.

[WeekendCoder]

Where does this extra '=' come from?

[aytee111]

using the function with an integer parameter the query returns nothing.

I presumed you were in query design, if so please post the SQL of the one that doesn't work. If the query is being used in VBA, please post the code.

[Micron]

however, using the function in the query doesn't work

I would say it's because you can't return a sql expression to a query field as criteria. At least that's what I gather you're doing. You're asking the query to return records where the value in the field you call this query from is equal to the entire expression. Pasting the sql into the sql view is not the same. Do that and switch to design view and you'll see the grid equivalent of the sql statement.

[ssanfu]

Just a couple of comments:

I would use the SELECT CASE construct instead of IF...ELSEIF.
"Select Case" is easier to read (IMO)

Public Function quarterDateRange(quarter As Integer) As String
    Dim startDate As Date
    Dim endDate As Date
    Dim ThisYear As String

    ThisYear = Year(Date)

    Select Case quarter
        Case 1
            startDate = CDate("1/1/" & ThisYear)
            endDate = CDate("3/31/" & ThisYear)
        Case 2
            startDate = CDate("4/1/" & ThisYear)
'            endDate = CDate("6/31/" & ThisYear)
            endDate = CDate("6/30/" & ThisYear)
        Case 3
            startDate = CDate("7/1/" & ThisYear)
'            endDate = CDate("9/31/" & ThisYear)
            endDate = CDate("9/30/" & ThisYear)
        Case 4
            startDate = CDate("10/1/" & ThisYear)
            endDate = CDate("12/31/" & ThisYear)
    End Select

    quarterDateRange = "Between #" & startDate & "# And #" & endDate & "# Or " & _
                       "Between #" & DateAdd("yyyy", -1, startDate) & "# And #" & DateAdd("yyyy", -1, endDate) & "# Or " & _
                       "Between #" & DateAdd("yyyy", -2, startDate) & "# And #" & DateAdd("yyyy", -2, endDate) & "#"

End Function

Also, note that (RED in the code)
(case 2) June has 30 days, not 31 and
(case 3) September has 30 days, not 31

[Micron]

To reiterate: AFAIK, there are only two places in a query that you can put a call to a procedure:
- in a calculated field like any other expression e.g. Test:quarterDateRange(4)
- in the criteria row in an attempt to return a value that can be found in a field; e.g.quarterDateRange(4) where the result exists in a table.

The posted function returns the criteria portion of a sql statement. That statement won't be found in any field, and a data type mismatch error should result when used as criteria since the field contains date values. If placed at the top of a query field, it will return the sql string as many times as there are records that match the sql statement taken as a whole.

So, what we should be asking is, what do you want to do with the query results that you're trying to get? Open a form? Report? These objects should be based on the entire sql statement and opened. Access doesn't provide as simple method of opening a query datasheet from a vba constructed sql statement, so I think we should be asking what the end use is.

[WeekendCoder]

Aytee111,

I am in query design. I figured Access would be smart enough to use a function to create my criteria expression but I guess not.


Micron,

Thank you for your definitive answer. I will have to use another method. My fault for expecting Access to be so simple.


ssanfu,

Thank you for cleaning up and improving my function.

[HiTechCoach]

Aytee111,... My fault for expecting Access to be so simple.

IMHO, This is not a limitation of Acess. It is how SQL works. At least in all the RDMS's I have used.

There are ways to use your function to built the criteria. It will just take a few extra steps with VBA code. One method is to use the .QueryDef() to edit the SQL of a saved query.


Depending on where you want to use the criteria, You may be able to use it as a .Filter for a Form or Report.


If I were creating this function, I would the date you want to check along with the quarter. The Function would return true or false based on the date passed within in the desired date range.

Tested function

Public Function IsInQuarterRange(pQtr As Integer, pDate As Date) As Boolean



    ' first check the quater
    IsInQuarterRange = (DatePart("q", pDate) = pQtr)
    
    ' if the desired quarter then check the year.
    If IsInQuarterRange Then IsInQuarterRange = (Year(pDate) >= (Year(Date) - 2))
    


End Function

The advantage of a function is it can be used be reused in many places. If the logic changes, it only has to be updated in a single place.


If the will only ever be used in a single query then it can be easily done within Access SQL.

[Micron]

I figured Access would be smart enough to use a function to create my criteria expression but I guess not.

It certainly is, and it's fairly simple. But if this makes any sense, it's like you're trying to turn a screw while holding the shaft end of a screwdriver. It's just not done that way.