Microsoft access - Automate date changes in a query. 3

[Halet]

I'm creating a report that use various date ranges. Currently, I have to manually update these ranges one by one by going to design mode and change the date. My idea was to have a form and link those date ranges to the form so that it will update itself. Update the form and all the ranges in 100s of queries are updated. Can anyone show me how to do this or is there a better way that I can automate this?

Thanks for your help!
Halet

 

[Crowley16]

you'd have to be careful doing this however you can loop through your queryDef collection and change all the .sql in your queries...

so, you could have something like:

dim qry as variant
for each qry in currentdb.querydefs

'replace your hard coded date range with a reference to a form
qry.sql = replace(qry.sql, "Forms!frmName!ctlName")
...

next qry



--------------------
Procrastinate Now!

 

[Halet]

Thanks for the reply! In your example, qry is the name of the query? I included a set of date below. Can you give me an example using them?

Days Prior to Month End
30 5/31/2007
31 5/30/2007
60 5/1/2007
61 4/30/2007
90 4/1/2007
91 3/31/2007

 

[lespaul]

no qry is a variable (variant type):

[tt]dim qry as variant[/tt]

then the code gets all the queries that are defined in the database and loops through them:

[tt]for each qry in currentdb.querydefs[/tt]

When you look at the SQL of the query, you probably have something like:

[tt]WHERE SomeDateField > #6/1/2007#[/tt]

this needs to be replace with:

[tt]WHERE SomeDateField > Forms!frmName!ctlName[/tt]

Can you post the SQL?





Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[Halet]

Here is the sql. Thanks again for all the help!

SELECT MAR.ID, MAR.NAME, MAR.[DOS]
FROM MAR
WHERE (((MAR.[DOS]) Between #5/1/2007# And #5/31/2007#));

DOS = date of service

 

[Halet]

Where do you define the following code? in a module? in every query? Thanks again for all the wonderful advice.

dim qry as variant
for each qry in currentdb.querydefs

 

[lespaul]

So you have hundreds of queries that contain the WHERE clause as shown above and you want to replace the hard coded dates so you don't have to manually change the hard coded dates each time you run the query.....

How are these queries ran? Are they attached to buttons/forms/reports or do you just display them in the query grid?

Yes, you would put that snippet of code in a module, but you can't just put that there....there's other parts of the code that you would have to add....that snippet loops through all the queries stored in the database and changes them. You would only run it once and all the SQL of your queries would be different.

Thanks,
Leslie

 

[Halet]

Yes, there are a lot of hardcode in the db. Currently, these queries are not attach to any form or button. I just display them in query grid and then copy them to a spreadsheet where the reports are. (used what was created by last guy on the job) I would like to automate it, so that it will automatically paste the data to the spreadsheet by clicking a few buttons.

Anyway, based on advices here. I created a form (It is named form1) where all the date ranges are calculated by enter the current date. The date ranges are displayed in text box, named txtbox1 to txtbox50. The next step will be changing the hardcodes to link to these textbox. In all the queries, hard codes should now be instead of

WHERE (((MAR.[DOS]) Between #5/1/2007# And #5/31/2007#));
WHERE (((MAR.[DOS]) Between Forms!Form1!txtbox1 And Forms!Form1!txtbox2));

Is this correct?

Can you also give me more details on the snippet code? what other parts of the code do i need?

Thanks again!

 

[Halet]

I created a module. Based on the info here, Lespaul, can you tell me if i'm doing it right?
_________________________________________________________
Option Compare Database
Dim qry0 As Variant 'current date'
Dim qry30 As Variant 'current date - 30 days'

For Each qry0 In CurrentDb.QueryDefs
qry0.SQL = Replace(qry0.SQL, "Forms!Form1!txtbox1")

Next For Each qry30 In CurrentDb.QueryDefs
qry30.SQL = Replace(qry30.SQL, "Forms!Form1!txtbox3")
End
___________________________________________________________

Replace the hardcode by qry0, qry30.

WHERE (((MAR.[DOS]) Between #5/1/2007# And #5/31/2007#));
WHERE (((MAR.[DOS]) Between qry30 And qry0));

 

[PHV]

Option Compare Database
Sub mySub
Dim qry As Variant
For Each qry In CurrentDb.QueryDefs
qry.SQL = Replace(qry.SQL, "#5/1/2007#", "Forms!Form1!txtbox1")
qry.SQL = Replace(qry.SQL, "#5/31/2007#", "Forms!Form1!txtbox3")
Next
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Halet]

Thanks for the reply! Is there anyway I can exclude the hardcode in the module? based on your code, after the module is ran, the date will no longer be 5/1/2007 and 5/31/2007. Next month I need to come back in and change the date in here again. I understand it already shorten my manual task using this code. I would like to automate this a little more. Please let me know how to make the hardcode a variable and how it should be in the queries that required the date change.

 

[lespaul]

after the module is run the query will get the date values from the form that you created. The first date will be the value in txtbox1 and the second date will be the value in txtbox3.

 

[Halet]

I understand the module will get the date from the txtbox. I'm looking one month down the road. I don't want to go back into the module and change the date and then run the module again. Instead of the code below,can I change #5/1/2007# to a variable named DayX in all queries, so that I don't need to change the module code anymore?

qry.SQL = Replace(qry.SQL, "#5/1/2007#", "Forms!Form1!txtbox1")

Thanks again for your advices!

 

[lespaul]

You will only run this module ONE time. After you run this module all the queries in the database will have the hardcoded date range replaced with form variables as parameters.

Have you ever used the parameter prompt in a query? In Access you can have a query like this:

SELECT * FROM SomeTable WHERE MyDateField < [Please enter the date]

When you run this query Access will create a parameter prompt "form" and allow the user to enter the date. Then it takes the value the user entered and places that in the [Please enter the date] portion of the query. So if I enter 6/28/2007 in the prompt, the query will read and return records that match:

SELECT * FROM SomeTable WHERE MyDateField < 6/28/2007

The problem with this approach comes when you need multiple criteria. If you do this:

SELECT * FROM SomeTable WHERE MyDateField Between [Please enter the begin date] AND [Please enter the end date]

then you will have TWO prompts, once for the begin date and one for the end date.

Alternatively, instead of using the built in Access parameter prompts you can create your OWN form. To get a begin and end date at the same time, you would create a form (frmCriteria) that has two textboxes, name one txtBeginDate and txtEndDate, and a button that runs a query. Then your SQL would be:

SELECT * FROM SomeTable WHERE MyDateField BETWEEN Forms!frmCriteria!txtBeginDate AND Forms!frmCriteria!txtEndDate

you have to make sure that the form is open and there are values in the textboxes, when you press the button, the query runs using the values in the textboxes to generate the criteria of the WHERE clause.

 

[Halet]

Thanks for the advice Lespaul! If I understand you correctly, I don't need to use a module at all. All I need is make the changes in all the queries to reference to the forms. Right?

 

[lespaul]

you could use the module to automatically change all the queries, but that's all the module was ever going to do, change the queries....

So, yes you need to change the queries ...I would suggest that you start by just using the Access parameter prompt. If you decide to use a form then you'll have to work out how to determine which query you want to open from that form after you enter the date information.

With the prompt, you'll just be asked by Access what dates you want. If you have some queries that are a specific date range you can do something like (I think, you'll have to define the parameter to use it like this though):

SELECT * FROM TableName WHERE SomeDateField BETWEEN [Please enter Starting Date] and [Please enter Starting Date] - 30

 

[Halet]

I got it to work now. Thanks again! I set it up now that the form is going to do the calculation. User manually enter a date and press enter. All the date ranges are calculated and displayed in the text boxes. The queries are now link to each textbox. I didn't use the module at all because there is no need.

My next plan in automating this 6 years old program is to have a button click it and all the queries are ran and then data from the queries are exported to the appropriate spreadsheets. Can you give me some tips on export to excel spreadsheets? How it is reference?

Here is my idea. Button pressed
1. Open query1
2. Copy data
3. Open spreadsheet1
4. Paste it in spreadsheet1, tab1, cell x and Y
5. Close spreadsheet1
6. Close query1

Repeat above steps until all queries are done.