changing the dates in a group of queries

[hazell]

Hi,
I have written a large number of queries to gather data for a quarterly monitoring form. How do I replace the dates to update them for next quarter.

For example I have written a query which counts the number of new members who started in the period 01/04/2013 to 31/07/2013. How can I change this (and the other 200 queries) without opening them all individually and manually altering it. Is there an Access equivalent of Word's Find and Replace?

Hope you can help. By the way I am using Access 2000

thanks

 

[Brianwarnock]

I don't know of any way to change your queries and doubt that there is a simple way to do this. What you have learnt the hard way is not to hard code variables such as criteria. You should have a Form with to textboxes startdate and enddate , and reference those in the criteria

Eg Forms!myformname!startdate

Thus making the criteria flexible.

Sorry that I cannot be more helpful

Brian

 

[Pat Hartman]

There are some find and replace tools available that work with Access. Google will find them for you. But as Brian already advised. Make this your last bulk change and change the criteria to reference a form field.

 

[hazell]

Thank you for that. I understand the principle of that. I have now set out the form for the start and end of the monitoring period. How should I set out the query if, for example I wanted to find all members who joined after the start of the period. I tried putting in >= StartDate in the criteria, but it wouldn't have it. Do I need to put in something else too?
thanks for all your help.

 

[Brianwarnock]

Obviously my explanation was not clear, you reference a form control by coding

Forms!yourformname!yourcontrolname

So it might be

>=Forms!dateform!startdate


Brian

 

[hazell]

Pretty sure that it is me that is not being clear.

I have tried to implement what you suggested, but I can't get it to work. I have a field on the table that says the date that the member joined, and I need to extract all the members who joined within a three month period, as well as those who were there at the beginning and end of the periods.

I presume I need to somehow compare the actual start date with the start of the monitoring period.

I did what you suggested but it didn't seem to work - I think it was just checking the start of the monitoring period and not the date that they joined and I need to do some sort of comparison between the two.

I probably haven't put that very clearly either, but if you can work out what I was trying to say I would be very grateful for any help.

 

[Brianwarnock]

My late wife was called Hazel, she didn't understand me either. but lets see if we can clear this up

Assume
In the table you have joindate
A form called dateform with controls startdate and enddate formatted for date input preferably.

In the query you select joindate and in the criteria you enter
>=Forms!dateform!startdate

In the form you enter the date you want , hit enter to ensure the control is complete, and leaving the form open run the query.

I would have a command button on the form to run the query but you can cross that bridge later.

Brian

 

[CazB]

Also, if you're looking to look for dates that are after the startdate on the form and before the end date on the form, you could use this as the criteria on your Joindate field

between Forms!dateform!startdate and Forms!dateform!enddate

 

[Brianwarnock]

But without the brackets.

Brian

 

[CazB]

oops yeah, dunno why I put those in! It's too early

 

[hazell]

Finally got it! thanks so much for your help

 

[Brianwarnock]

Glad it's sorted, happy to have helped.

Brian