Specify A Date Range

[jo15765]

I have weekly reports and in these weekly reports, there is a query that I have to specify a begin date (whatever Monday's date is) and an end date (whatever Sundays date is). I was going to try to instead of manually going into each database to open the query and run the query that way, build a form, and have an onclick event of the button that would execute this code:

'Opening the Database
Set db = DBEngine.Workspaces(0).OpenDatabase("C:\Test\Test123\Test123db.mdb")
'Running specified queries
db.Execute "qry_check_weekly_statistics", dbFailOnError
Set db = Nothing.

And of course, I am opening about 15 different databases, and running the same query in each database. Is there a way to either 1) Only input the start/end date one time at the beginning of the run, and VB code can retain that start/end date and pull data from within that time frame for each subsequent query or 2) somehow programatically build a calander into VBA coding and when the query is run, in VBA have it only pull data for the previous 7 days (altho that sounds like it would be extremely difficult).

[goodguy]

You have to 'open' each database - Why are they all closed?
15 different databases? Are you talking tables or actual databases?
Do you have the sql code for qry_check_weekly_statistics defined?
Where are you collecting the query results? db.execute will simply run the query and close without placing them anywhere unless this is an Append/Update/Insert query.
Why don't you create reports instead?
That way, you can run reports from all 15 different databases in one procedure.

[jo15765]

I am actually having to open each database to run this query. It is an update query, so I don't need to see the results, I just need it run.

I actually switched this to a Union Query and ran:

Select * from [Enter Query name] in '[Enter Query Location]'
UNION ALL
Select * from [Enter Query name] in '[Enter Query Location]'

And now it is only asking me for the start date and end date one time, and it is applying that same start date and same end date to each query in the Union Query. I would still like to know if there is a way to have the date parameters auto update. Meaning, the start date will be whatever Mondays date is, and the end date will be whatever Sundays date is...The report run today's start date was 10/03/11 and the end date was 10/09/11, however the report from two weeks ago's start date was 09/26/11 and the end date was 10/02/11. Is there a way to code in SQL to have access only display the results of a 7 day time span. Maybe use a parameter like Today()-6 for the start date, and Today() for the end date or something like that (but I don't know if you can add something like that to a Union Query, since it is all coded in SQL not in design view)??

[goodguy]

Instead of your parameters for StartDate and EndDate, place these:

SELECT Date()-Weekday(Date())+2 AS LastMonday, Date()-Weekday(Date())+8 AS NextSunday

[jo15765]

Just so I am clear....
I would place that code in the source query SQL code, is that correct?
And would that go at the beginning or the end of my SQL statement for the source query?

[goodguy]

Yes, you can post your query syntax if you want help.

[jo15765]

This is the SQL code I am needing to insert that statement into (I shortened the select fields because there is over 40):

SELECT DataInfo.Field1 AS [Main ID], DataInfo.Field2 AS [Secondary ID], DataInfo.Field3 AS [Source Code] 
FROM DataInfo
WHERE (((DataInfo.Mailed_Date) Between [start] And [end]) AND ((DataInfo.Field1) Is Not Null) AND ((DataInfo.SalesManager) Like "*Torres*"));

Can you show me where in the statement to add your modified date parameters?

[goodguy]

Use this:

SELECT DataInfo.Field1 AS [Main ID], 
DataInfo.Field2 AS [Secondary ID], 
DataInfo.Field3 AS [Source Code]  FROM DataInfo 
WHERE (((DataInfo.Mailed_Date) 
Between [Date()-Weekday(Date())+2] And [Date()-Weekday(Date())+8]) 
AND ((DataInfo.Field1) Is Not Null) 
AND ((DataInfo.SalesManager) Like "*Torres*"));

[jo15765]

When I tried it that way, and I just pasted your code directly into my query, it is asking me to input the parameters for:

Date()-Weekday(Date())+2
Date()-Weekday(Date())+8

[jo15765]

I changed the code to:

Between Date()-Weekday(Date())+2 And Date()-Weekday(Date())+8

And it is running the query now error free now!! Now just so I understand what exactly are the date functions telling the query to do? Can you break it down for me?

[goodguy]

Nice to know you sorted it out. Write these statements in query design and check the results when you run them:
SELECT DATE() 'Returns today's date
SELECT Weekday(Date()) 'Returns the weekday number for today where Sunday is 1 and Saturday is 7
SELECT DATE()-Weekday(Date()) 'Returns last Saturday

[jo15765]

If Sunday is 1 and Saturday is 7, then why is it + 8? To me that would be outside the range of valid days of the week...

Actually I think I just picked up on it...The code you gave me is...

Between Date()-Weekday(Date())+2 And Date()-Weekday(Date())+8

And if Weekday(Date()) Returns last Saturday then Date()-Weekday(Date())+2 Is essentially saying:
Date() -- Today - Weekday(Date()) --- Saturday + 2 --- Monday
So it is saying Today - (Saturday+2) --- which would be Monday

And the plus 8 I was asking about above, is adding 8 days to Saturday to return the following Sunday...is that correct?

[goodguy]

Did you try what I suggested? Nothing gives a programmer more experience than doing hands-on work. Theory does not count. Try the sql statements I indicated and play around with them to see what you get. You are not trying to get the day of the week in your query, are you? What you want is a particular date for a particular Sunday. So think straight and don't get sidetracked by glittering issues.

[jo15765]

Correct, I don't want the date to display, I just want the data being displayed to be within those time constraints. I actually did just pick it up (I edited my last post with my findings), it was a bit confusing at first, but it is really quite simple. I honestly have to say I have never used the Weekday() feature in Access before so I am going to google and see what else you can do with it!

[goodguy]

Programmers use their imagination and creativity to perform some really strange tasks with some of the functions in their respective programming languages such that the original inventor of the language had not anticipated. You will be surprised as you come across some smart developers' code samples and some day you too may be able to conjure up something along the same lines. Hopefully. If you do not tire of experimenting.