Format of condition statement

[Richard]

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

 

[Steve Schapel]

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.

 

[Richard]

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

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

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

 

[Steve Schapel]

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

 

[Richard]

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.

 

[Steve Schapel]

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.

 

[Richard]

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

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

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.

 

[Steve Schapel]

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?