by Raghuraja.C (12 Submissions)
Category: Miscellaneous
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Sun 6th April 2003
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
Simple Code which will build and return Query passing Periods as parameter
API Declarations
Dim strQry As String
Dim strReturnQry As String
VBcoders
'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
No comments have been posted about Simple Code which will build and return Query passing Periods as parameter. Why not be the first to post a comment about Simple Code which will build and return Query passing Periods as parameter.