Solved

Excel VBA -To say Current Week

Posted on 2006-06-26
7
300 Views
Last Modified: 2008-03-10
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

0
Comment
Question by:ca1358
  • 4
  • 2
7 Comments
 
LVL 20

Expert Comment

by:alainbryden
ID: 16984620
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")

0
 
LVL 35

Expert Comment

by:mvidas
ID: 16984632
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
0
 
LVL 35

Accepted Solution

by:
mvidas earned 500 total points
ID: 16984680
Stupid me, made a copy/paste error.  The middle line should be (added "Date - ")

 Src = Src & "WHERE Table1.RecDt Between #" & Date - Weekday(Date, 2) + 1 & _
  "# And #" & Date - Weekday(Date, 2) + 5 & "# "

Matt
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 16984681
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
0
 
LVL 35

Expert Comment

by:mvidas
ID: 16985049
Great minds, Patrick...

Glad to help, ca1358!
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 16985469
Matt,

> Great minds, Patrick...

Yeah, but what's our explanation?

:)

Regards,

Patrick
0
 
LVL 35

Expert Comment

by:mvidas
ID: 16985524
Theres an exception to every rule..  Great minds think alike, and so do ours!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question