|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
I'm making a macro to run query of travel data
How do I format my condition statement so only 2nd travel legs are
displayed in datatable? Note: "Travel_Leg" is a variable in table named
"Trips"
I've tried various combinations, like
[Trips.Travel_leg] = 2
but with no success
--
Richard
|
|
Richard,
This is not a job for the Condition of your macro. The Condition refers
to controlling whether the macro Action is performed or not, and not to
the selection of the data. You appear to be referring to controlling
which data is returned, and as such this is a job for the Criteria in
your query. So, it seesm to me, just go to the design view of your
query, and put 2 in the criteria for the Travel_leg column.
--
Steve Schapel, Microsoft Access MVP
Richard wrote:
[Quoted Text] > I'm making a macro to run query of travel data
>
> How do I format my condition statement so only 2nd travel legs are
> displayed in datatable? Note: "Travel_Leg" is a variable in table named
> "Trips"
>
> I've tried various combinations, like
> [Trips.Travel_leg] = 2
> but with no success
>
|
|
Thanks for the clarification.
However, I regularly run a set of queries with the same change in condition
and would like to automate the process. Is there another way to do this?
--
Richard
"Steve Schapel" wrote:
[Quoted Text] > Richard,
>
> This is not a job for the Condition of your macro. The Condition refers
> to controlling whether the macro Action is performed or not, and not to
> the selection of the data. You appear to be referring to controlling
> which data is returned, and as such this is a job for the Criteria in
> your query. So, it seesm to me, just go to the design view of your
> query, and put 2 in the criteria for the Travel_leg column.
>
> --
> Steve Schapel, Microsoft Access MVP
>
> Richard wrote:
> > I'm making a macro to run query of travel data
> >
> > How do I format my condition statement so only 2nd travel legs are
> > displayed in datatable? Note: "Travel_Leg" is a variable in table named
> > "Trips"
> >
> > I've tried various combinations, like
> > [Trips.Travel_leg] = 2
> > but with no success
> >
>
|
|
Richard,
I am not sure of the meaning of "the same change in condition". And
what sort of queries are they? Are they action queries, or are they
being used to export data, or are they being used as the basis of
reports? Can you give some examples and more specific details to help
clarify what you are trying to achieve? Generally, the criteria for
queries are applied in one of three ways:
- specific vlaue hard-coded into the query
- referenced from a control on a form
- entered at run-time via paramter prompt
--
Steve Schapel, Microsoft Access MVP
Richard wrote:
[Quoted Text] > Thanks for the clarification.
>
> However, I regularly run a set of queries with the same change in condition
> and would like to automate the process. Is there another way to do this?
|
|
Each week I run two queries of all travel to and from our laboratory.
The queries involve about a dozen tables witch I link to through a network.
I have various conditional statements that stay the same each week, like
departure code and division code (within the larger organization). However,
each week I update the conditional statements for departure dates; then run
the query; and then export to Excel files that I send out to various managers
within the organization.
--
Richard
"Steve Schapel" wrote:
[Quoted Text] > Richard,
>
> I am not sure of the meaning of "the same change in condition". And
> what sort of queries are they? Are they action queries, or are they
> being used to export data, or are they being used as the basis of
> reports? Can you give some examples and more specific details to help
> clarify what you are trying to achieve? Generally, the criteria for
> queries are applied in one of three ways:
> - specific vlaue hard-coded into the query
> - referenced from a control on a form
> - entered at run-time via paramter prompt
>
> --
> Steve Schapel, Microsoft Access MVP
>
> Richard wrote:
> > Thanks for the clarification.
> >
> > However, I regularly run a set of queries with the same change in condition
> > and would like to automate the process. Is there another way to do this?
>
|
|
Richard,
Thanks for the further explanation.
So there are two queries, and the only thing that changes from week to
week is the date criteria?
Well, one option is, instead of writing the dates into the Criteria of
the query, you use an unbound textbox on a form, and enter the required
criteria date in there, and then refer to this textbox in the Criteria
of the query design, using syntax like this...
[Forms]![NameOfForn]![NameOfTextbox]
That would be easier than messing with the query every time.
Alternatively, I imagine it may be that the date criteria follows a
predictable pattern (e.g. "the previous Monday") or some such. If this
is the case, then you could write an expression into the query that will
always be up-to-date so to speak, and no maintenance or criteria entry
required at all.
--
Steve Schapel, Microsoft Access MVP
Richard wrote:
[Quoted Text] > Each week I run two queries of all travel to and from our laboratory.
> The queries involve about a dozen tables witch I link to through a network.
> I have various conditional statements that stay the same each week, like
> departure code and division code (within the larger organization). However,
> each week I update the conditional statements for departure dates; then run
> the query; and then export to Excel files that I send out to various managers
> within the organization.
|
|
Good suggestion.
It would be great if I could call the queries from an Excel macro since I do
all the analysis in Excel.
Is there a way to call an Access query from Excel VBA, and pass the updated
condition that way?
--
Richard
"Steve Schapel" wrote:
[Quoted Text] > Richard,
>
> Thanks for the further explanation.
>
> So there are two queries, and the only thing that changes from week to
> week is the date criteria?
>
> Well, one option is, instead of writing the dates into the Criteria of
> the query, you use an unbound textbox on a form, and enter the required
> criteria date in there, and then refer to this textbox in the Criteria
> of the query design, using syntax like this...
> [Forms]![NameOfForn]![NameOfTextbox]
> That would be easier than messing with the query every time.
>
> Alternatively, I imagine it may be that the date criteria follows a
> predictable pattern (e.g. "the previous Monday") or some such. If this
> is the case, then you could write an expression into the query that will
> always be up-to-date so to speak, and no maintenance or criteria entry
> required at all.
>
> --
> Steve Schapel, Microsoft Access MVP
>
> Richard wrote:
> > Each week I run two queries of all travel to and from our laboratory.
> > The queries involve about a dozen tables witch I link to through a network.
> > I have various conditional statements that stay the same each week, like
> > departure code and division code (within the larger organization). However,
> > each week I update the conditional statements for departure dates; then run
> > the query; and then export to Excel files that I send out to various managers
> > within the organization.
>
|
|
Richard,
I don't know the answer to that specific question... maybe you would
have a better chance of a good answer there in an Excel newsgroup.
But I may be misunderstanding what you are doing. Apart from continuing
to use the word "condition" to mean criteria, I thought you needed to
export data to Excel. So I don't understand why the criteria can't be
applied at the point of export. Can you give an example of the date
criteria you are using?
--
Steve Schapel, Microsoft Access MVP
Richard wrote:
[Quoted Text] > Good suggestion.
> It would be great if I could call the queries from an Excel macro since I do
> all the analysis in Excel.
>
> Is there a way to call an Access query from Excel VBA, and pass the updated
> condition that way?
|
|
|