Multiple Queries

[kplatt]

Hi, hopefully someone could help me out with this.

I have a database with multiple queries. Every year I need to go into each queary to pull reports for that year. Hence, I have to open each querey and enter "like */2009" into each date field. Is there a way that i can form a macro to somehow ease this process. How complex would this be or is it fairly simple using the macro defaults. Any guidance would help...

I was thinking of a system that may be able to run a macro that would automatically add the "like*/2009" criteria to make this process easier. Or if you understand the purpose of my questino maybe you have a better solution or similar method.

Thanks
Kevin

 

[Trevor G]

Kevin, create a form that has a text box on it and name the textbox something like txtCheck, then for each query set the criteria to look for the textbox and then use the macro to run through each query using the value of the form and its text box, so you enter the criteria once and it processes the looping of all queries until you have the results you want.

Trevor

 

[vbaInet]

Looks like you're only looking for an exact Year so no need for the Like statement. You might want to optimise your expression. Following what Trevor G has advised using a textbox, use this expression for the criteria:

Year([[B]Date_Field[/B]])=Val(Nz([Forms]![[B]Name_Of_Form[/B]].[[B]TextBox1[/B]],0))

You may also find this thread helpful:
http://www.access-programmers.co.uk/forums/showthread.php?t=183254

 

[kplatt]

Thanks for your input guys, Below is what i put in the query and referenced it to an unbound form.

Between [Forms]![a]![BeginDate] And [Forms]![a]![EndDate]

This works and I am ok with it, but i also have the need to see all data after I report for 2009. When the referenced form is not open or the fields in the form are left blank, the criteria remains in the query and asks for the criteria when i try to run each individual query. Is there a way to show all data that would typically show in the query without a criterial as a default if the unbound form was not open for or if the unbound form fields are left blank. Is there a " show all" iff function or something?

Thanks

 

[vbaInet]

You would need to handle the WHERE part in code by constructing the SQL.

Here's a link:
http://baldyweb.com/BuildSQL.htm

 

[kplatt]

Is there a way other than doing sql. where you can use a macro to add text to a criterial field in a query?

 

[vbaInet]

Hmm... there might be a way, but you would have to test it and see if it works. However, I don't think it would be good for performance.

Did you put the criteria under the BeginDate?

 

[kplatt]

Not sure what you mean my putting it in the begin date.

-Basically the tables and query have a date field.
-Currently I have (Between [Forms]![a]![BeginDate] And [Forms]![a]![EndDate]) set in the criteria field of the query.
-I put a begin date and end date in the form and it runs the information i want correctly. (date range)

But i need all data to show at times (preferably as a default). Therfore if i wanted all data i would just open the query but if i wanted a specific date I would use a macro to: [Open Query] add command (Between [Forms]![a]![BeginDate] And [Forms]![a]![EndDate]) to the criterial section, [open form], add the dates to the form, [run query]

In this case i would run the macro to get a date range whereas other wise by just opening the query without a criterial would show all data.

The red section is the key if it is possible for a macro to add text to a query before it runs the query.

Thanks

 

[vbaInet]

Hmmm... try this:

Between IIF([Forms]![a]![BeginDate] & "" = "", DMin("[[COLOR=Red][B]Date_Field[/B][/COLOR]]", "[COLOR=Red][B]Name_Of_Table[/B][/COLOR]"),  [Forms]![a]![BeginDate]) And IIF([Forms]![a]![EndDate] & "" = "",  DMax("[[COLOR=Red][B]Date_Field[/B][/COLOR]]", "[COLOR=Red][B]Name_Of_Table[/B][/COLOR]"),  [Forms]![a]![EndDate])

Amend the text in red and both ends of the AND.

 

[kplatt]

There is not table name though. The form that is used is unbound.??

 

[kplatt]

or should your "name of table " be name of query?

 

[vbaInet]

In post #8 you mentioned you put the code in the Criteria field of the query correct? You must have put it under a DATE field right?

 

[kplatt]

yes but i am confused with your "name of table" field in your code

 

[vbaInet]

The DATE field resides in a table or is a field in a table correct?

 

[kplatt]

Yes the date is in at table. but the perameters are coming from and unbound form. So you are saying put the name of the table that the date is coming from. OK.

 

[vbaInet]

That's correct

 

[kplatt]

When the form is not open it aks for Forms!a!BeginDate. When the form is open it works correctly. Also what did you mean by ammend both ends of "and"

 

[vbaInet]

When the form is not open it aks for Forms!a!BeginDate.

That should be an obvious behaviour.

Also what did you mean by ammend both ends of "and"

To the left of "AND" you change the table name, to the right you change it too.