ca1358
asked on
Excel VBA -To say Current Week
This Excel VBA
Is there any way in this line to say current week, where I do not have to change the date every week?
Src = Src & "WHERE Table1.RecDt Between #6/26/2006# And #6/30/2006# "
Any help would greatly be appreciated!
////////////////////////// ////////// ////////// /////////
Src = "SELECT Sum(Table1.LoanAmt) AS SumOfLoanAmt " '<--- space added to
Src = Src & "FROM Table1 "
Src = Src & "WHERE Table1.RecDt Between #6/26/2006# And #6/30/2006# "
Src = Src & "GROUP BY Table1.[AS400 ID] "
Src = Src & "HAVING Table1.[AS400 ID]) = " & TradeLimit.ComboBox1.Value
Is there any way in this line to say current week, where I do not have to change the date every week?
Src = Src & "WHERE Table1.RecDt Between #6/26/2006# And #6/30/2006# "
Any help would greatly be appreciated!
//////////////////////////
Src = "SELECT Sum(Table1.LoanAmt) AS SumOfLoanAmt " '<--- space added to
Src = Src & "FROM Table1 "
Src = Src & "WHERE Table1.RecDt Between #6/26/2006# And #6/30/2006# "
Src = Src & "GROUP BY Table1.[AS400 ID] "
Src = Src & "HAVING Table1.[AS400 ID]) = " & TradeLimit.ComboBox1.Value
Hi ca1358,
Using the Weekday function, we can determine which day of the week it is, and subsequently which day the corresponding monday/friday is. I wasn't sure how you wanted the weekends handled, so if it doesnt do as you wish for those let us know and we can modify it:
Src = "SELECT Sum(Table1.LoanAmt) AS SumOfLoanAmt " '<--- space added to
Src = Src & "FROM Table1 "
Src = Src & "WHERE Table1.RecDt Between #" & Date - Weekday(Date, 2) + 1 & _
"# And #" & Weekday(Date, 2) + 5 & "# "
Src = Src & "GROUP BY Table1.[AS400 ID] "
Src = Src & "HAVING Table1.[AS400 ID]) = " & TradeLimit.ComboBox1.Value
Matt
Using the Weekday function, we can determine which day of the week it is, and subsequently which day the corresponding monday/friday is. I wasn't sure how you wanted the weekends handled, so if it doesnt do as you wish for those let us know and we can modify it:
Src = "SELECT Sum(Table1.LoanAmt) AS SumOfLoanAmt " '<--- space added to
Src = Src & "FROM Table1 "
Src = Src & "WHERE Table1.RecDt Between #" & Date - Weekday(Date, 2) + 1 & _
"# And #" & Weekday(Date, 2) + 5 & "# "
Src = Src & "GROUP BY Table1.[AS400 ID] "
Src = Src & "HAVING Table1.[AS400 ID]) = " & TradeLimit.ComboBox1.Value
Matt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi ca1358,
Assuming the "week" begins Monday and ends Friday, replace the line:
Src = Src & "WHERE Table1.RecDt Between #6/26/2006# And #6/30/2006# "
with:
Src = Src & WHERE Table1.RecDt Between #" & Format(Date - Weekday(Date, vbMonday) + 1, "m/d/yyyy") & _
"# And #" & Format(Date - Weekday(Date, vbMonday) + 5, "m/d/yyyy") & "# "
Regards,
Patrick
Assuming the "week" begins Monday and ends Friday, replace the line:
Src = Src & "WHERE Table1.RecDt Between #6/26/2006# And #6/30/2006# "
with:
Src = Src & WHERE Table1.RecDt Between #" & Format(Date - Weekday(Date, vbMonday) + 1, "m/d/yyyy") & _
"# And #" & Format(Date - Weekday(Date, vbMonday) + 5, "m/d/yyyy") & "# "
Regards,
Patrick
Great minds, Patrick...
Glad to help, ca1358!
Glad to help, ca1358!
Matt,
> Great minds, Patrick...
Yeah, but what's our explanation?
:)
Regards,
Patrick
> Great minds, Patrick...
Yeah, but what's our explanation?
:)
Regards,
Patrick
Theres an exception to every rule.. Great minds think alike, and so do ours!
Month(Date)
Year(Date) and
Day(Date)
(you get the idea) you can get individual segments of the current date / time.
Alternatively you can say
Src = Src & Format(Now, "MM/DD/YYYY")