• Home
 · 
• Miscellaneous
 · 
• Simple Code which will build and return Query passing Periods as parameter

Simple Code which will build and return Query passing Periods as parameter

Simple Code which will build and return Query passing Periods as parameter

API Declarations


Dim strQry As String
Dim strReturnQry As String



Rate Simple Code which will build and return Query passing Periods as parameter

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

'OR you can use the return value in your program
'This code will generate Report Query Date Required Period as Parameter

    Option Explicit
    Dim strQry As String
    Dim strReturnQry As String

Private Sub Command1_Click()
    
    strReturnQry = Func_Date_Period("Today")
    'strReturnQry = Func_Date_Period("This Week")
    'strReturnQry = Func_Date_Period("This Month")
    'strReturnQry = Func_Date_Period("This Quarter Year")
    'strReturnQry = Func_Date_Period("This Half Year")
    'strReturnQry = Func_Date_Period("This Year")
    'strReturnQry = Func_Date_Period("Last Week")
    'strReturnQry = Func_Date_Period("Last Quarter Year")
    'strReturnQry = Func_Date_Period("Last Half Year")
    'strReturnQry = Func_Date_Period("Last Year")
    'strReturnQry = Func_Date_Period("Last Month")

    MsgBox strReturnQry

End Sub
Public Function Func_Date_Period(strReqPeriod As String, Optional strReqUser As String) As String

    On Error GoTo ErrPara

    Select Case strReqPeriod
    Case "Today"
       strQry = " = date(" & Str$(Year(Date)) + "," + Str$(Month(Date)) + "," + Str$(Day(Date)) & ")"
    Case "This Week"
       strQry = " >= date(" & Str$(Year(Date)) + "," + Str$(Month(Date)) + "," + Str$(Day(Date) - Weekday(Date, 1)) & ") And " & strReqUser
       strQry = strQry & "<= date(" & Str$(Year(Date)) + "," + Str$(Month(Date)) + "," + Str$(Day(Date)) & ")"
    Case "This Month"
       strQry = " >= date(" & Str$(Year(Date)) + "," + Str$(Month(Date)) + "," + Str$(1) & ") And " & strReqUser
       strQry = strQry & "<= date(" & Str$(Year(Date)) + "," + Str$(Month(Date)) + "," + Str$(Day(Date)) & ")"
    Case "This Quarter Year"
       If Month(Date) <= 3 Then
          strQry = " >= date(" & Str$(Year(Date)) + "," + Str$(1) + "," + Str$(1) & ") And " & strReqUser
       ElseIf Month(Date) >= 4 And Month(Date) <= 6 Then
          strQry = " >= date(" & Str$(Year(Date)) + "," + Str$(4) + "," + Str$(1) & ") And " & strReqUser
       ElseIf Month(Date) >= 7 And Month(Date) <= 9 Then
          strQry = " >= date(" & Str$(Year(Date)) + "," + Str$(7) + "," + Str$(1) & ") And " & strReqUser
       ElseIf Month(Date) >= 10 And Month(Date) <= 12 Then
          strQry = " >= date(" & Str$(Year(Date)) + "," + Str$(10) + "," + Str$(1) & ") And " & strReqUser
       End If
       strQry = strQry & " <= date(" & Str$(Year(Date)) + "," + Str$(Month(Date)) + "," + Str$(Day(Date)) & ")"
    Case "This Half Year"
       If Month(Date) <= 6 Then
          strQry = " >= date(" & Str$(Year(Date)) + "," + Str$(1) + "," + Str$(1) & ") And " & strReqUser
       Else
          strQry = " >= date(" & Str$(Year(Date)) + "," + Str$(7) + "," + Str$(1) & ") And " & strReqUser
       End If
       strQry = strQry & " <= date(" & Str$(Year(Date)) + "," + Str$(Month(Date)) + "," + Str$(Day(Date)) & ")"
    Case "This Year"
       strQry = " >= date(" & Str$(Year(Date)) + "," + Str$(1) + "," + Str$(1) & ") And " & strReqUser
       strQry = strQry & "<= date(" & Str$(Year(Date)) + "," + Str$(Month(Date)) + "," + Str$(Day(Date)) & ")"
    Case "Last Week"
       strQry = " >= date(" & Str$(Year(Date)) + "," + Str$(Month(Date - 7)) + "," + Str$(Day(Date - 7)) & ") And " & strReqUser
       strQry = strQry & "<= date(" & Str$(Year(Date)) + "," + Str$(Month(Date)) + "," + Str$(Day(Date)) & ")"
    Case "Last Quarter Year"
       If Month(Date) <= 3 Then
          strQry = " >= date(" & Str$(Year(Date) - 1) + "," + Str$(10) + "," + Str$(1) & ") And " & strReqUser
          strQry = strQry & " <= date(" & Str$(Year(Date) - 1) + "," + Str$(12) + "," + Str$(31) & ")"
       ElseIf Month(Date) >= 4 And Month(Date) <= 6 Then
          strQry = " >= date(" & Str$(Year(Date)) + "," + Str$(1) + "," + Str$(1) & ") And " & strReqUser
          strQry = strQry & " <= date(" & Str$(Year(Date)) + "," + Str$(3) + "," + Str$(31) & ")"
       ElseIf Month(Date) >= 7 And Month(Date) <= 9 Then
          strQry = " >= date(" & Str$(Year(Date)) + "," + Str$(4) + "," + Str$(1) & ") And " & strReqUser
          strQry = strQry & " <= date(" & Str$(Year(Date)) + "," + Str$(6) + "," + Str$(30) & ")"
       ElseIf Month(Date) >= 10 And Month(Date) <= 12 Then
          strQry = " >= date(" & Str$(Year(Date)) + "," + Str$(7) + "," + Str$(1) & ") And " & strReqUser
          strQry = strQry & " <= date(" & Str$(Year(Date)) + "," + Str$(9) + "," + Str$(30) & ")"
       End If
    Case "Last Half Year"
       If Month(Date) <= 6 Then
          strQry = " >= date(" & Str$(Year(Date) - 1) + "," + Str$(7) + "," + Str$(1) & ") And " & strReqUser
          strQry = strQry & " <= date(" & Str$(Year(Date) - 1) + "," + Str$(12) + "," + Str$(31) & ")"
       Else
          strQry = " >= date(" & Str$(Year(Date)) + "," + Str$(1) + "," + Str$(1) & ") And " & strReqUser
          strQry = strQry & " <= date(" & Str$(Year(Date)) + "," + Str$(6) + "," + Str$(30) & ")"
       End If
    Case "Last Year"
       strQry = " >= date(" & Str$(Year(Date) - 1) + "," + Str$(1) + "," + Str$(1) & ") And " & strReqUser
       strQry = strQry & "<= date(" & Str$(Year(Date) - 1) + "," + Str$(12) + "," + Str$(31) & ")"
    Case "Last Month"
       Dim NoOfDay As Variant
       NoOfDay = Array("31", "28", "31", "30", "31", "30", "31", "31", "30", "31", "30", "31")
       If Month(Date) = 1 Then
          strQry = " >= date(" & Str$(Year(Date) - 1) + "," + Str$(12) + "," + Str$(1) & ") And " & strReqUser
          strQry = strQry & "<= date(" & Str$(Year(Date) - 1) + "," + Str$(12) + "," + Str$(31) & ")"
       Else
          strQry = " >= date(" & Str$(Year(Date)) + "," + Str$(Month(Date) - 1) + "," + Str$(1) & ") And " & strReqUser
          strQry = strQry & "<= date(" & Str$(Year(Date)) + "," + Str$(Month(Date) - 1) + "," + Str$(NoOfDay(Month(Date) - 1) - 1) & ")"
       End If
    Case Else
       strQry = ">= date() And " & strReqUser & "<= date()"
    End Select

    Func_Date_Period = strQry

Exit Function

ErrPara:
   MsgBox Err.Description

End Function