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

alainbryden

If you type
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")

mvidas

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

ASKER CERTIFIED SOLUTION

mvidas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Patrick Matthews

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

mvidas

Great minds, Patrick...

Glad to help, ca1358!

Patrick Matthews

Matt,

> Great minds, Patrick...

Yeah, but what's our explanation?

:)

Regards,

Patrick

mvidas

Theres an exception to every rule.. Great minds think alike, and so do ours!