Parameter Value = Monday of the current week VBA

[bcn1988]

Hello!



I have developed a VBA procedure to export a number of queries as a text (comma delimitted) to a specific folder. However, I have ran into a snag as one of the queries requires the user to input the date of the Monday of the current week. For example, if we use the current week we are in (09/09/2013 - 09/13/2013: 5 day business week). Let's say the user runs the code on Monday, 09/09/2013. Then the user needs to input two parameters in two seperate instances, one after the other. First they would enter the month: "09". Then they would enter the day: "09". After they input these two parameters, the query will run. If the user runs the report on a Tuesday (date in this instance would be 09/10/2013), then I still need the two parameters to be, Month: "09", Day: "09". This holds true if the query is ran on a Wednesday (parameters still would be 09/09). The code I current have works great for queries that do not need users to enter a parameter, and the current code is the following:

 Sub Export_Queries()

Dim OutputPath As String
Dim strQryName1 As String
Dim strXLFile1 As String


OutputPath = "C:\"
strQryName1 = "Query1"
strXLFile1 = OutputPath & "Query1" & ".txt"


DoCmd.TransferText acExportDelim, "Query1_Export", strQryName1, strXLFile1, True


End Sub

What I need is the following:
* Code that will input the correct parameter dates for the Monday of the current week (please see explanation above).
* The code needs to be implemented in the code I have above.

Please let me know if you need any additional information! Thank you for all of your help!

[June7]

DateAdd("d",-weekday(Date(),3),Date())

or

DateAdd("d",-weekday(Date())+2,Date())

[mrojas]

You might need a form to allow the user to enter the required input. Then validate the input using a Select Case structure and run query based on case results using the DateAdd function June7 suggested; except that you'll have to add one case for each day of the week other than Monday.

[bcn1988]

Hey June7,

Thanks for replying with a formula. I tested the formulas and both of them are returning Monday's date. However, which of the formulas should I depend on more to return the current Monday of the current week's date?

[June7]

I think either works as well as the other.