customized queries

  • Thread starter Thread starter Beginner
  • Start date Start date
B

Beginner

I have a macro which outputs queries to excel files.
The queries are different only in that they are for
different customers A, B ,C -everything else is the same.

Is there a way to program so that the customers A, B, C
are not hardcoded(because if customer A is changed to X
someday.
I tried to use report instead of queries, but am not able
to customize the output to excel files for each customer

Any help is appreciated.

Thanks in advance
Beginner
 
You can use a control on a form to filter the query.
Create the control (text box, combo box, etc.) on the
form, and then in the query enter the path to the control
in the criteria for the customer field.

For example, if you create a form called "Form1" and the
control on the form is called "TextBox", you would enter
the following in the criteria for the field to be filtered:

Like [FORMS].[Form1].[TextBox]

This way, you can filter the query down based on the value
you put in the control on the form, or you can enter * in
the control and thereby not add any additional filtering
to the query.
 
Is there a way to automatically populate the text box(or
combo box) with the unique customer name from the table.

Thanks for any further help

Beginner
-----Original Message-----
You can use a control on a form to filter the query.
Create the control (text box, combo box, etc.) on the
form, and then in the query enter the path to the control
in the criteria for the customer field.

For example, if you create a form called "Form1" and the
control on the form is called "TextBox", you would enter
the following in the criteria for the field to be filtered:

Like [FORMS].[Form1].[TextBox]

This way, you can filter the query down based on the value
you put in the control on the form, or you can enter * in
the control and thereby not add any additional filtering
to the query.
-----Original Message-----
I have a macro which outputs queries to excel files.
The queries are different only in that they are for
different customers A, B ,C -everything else is the same.

Is there a way to program so that the customers A, B, C
are not hardcoded(because if customer A is changed to X
someday.
I tried to use report instead of queries, but am not able
to customize the output to excel files for each customer

Any help is appreciated.

Thanks in advance
Beginner

.
Click to expand...
.
Click to expand...
 
Beginner,

Possibly the way to do it would be to use a combobox whose RowSource
is the Customers table, and then you can just select the customer
required from the list. Would that work for you ok?

By the way, the answer given by Chris B was almost right, but in fact
will not quite work. The Like operator is incorrect in this context,
and the expression should use !s and not .s so use criteria like this:
[Forms]![Form1]![ComboBox]

- Steve Schapel, Microsoft Access MVP
 
Parameter lines in _macros_ require the !s between the
path elements, but in the criteria line the periods work
fine. Likewise with using "Like." I use periods
and "Like" in the criteria lines of my queries and the
functionality is not problematic in any way.

Also, if you use a combo box to automatically show the
customers that exist in the table, be sure to use a "group
by" statement in the query, so that you only see one
listing for each unique customer entry (if there is more
than one row per customer in the source table).

Chris

-----Original Message-----
Beginner,

Possibly the way to do it would be to use a combobox whose RowSource
is the Customers table, and then you can just select the customer
required from the list. Would that work for you ok?

By the way, the answer given by Chris B was almost right, but in fact
will not quite work. The Like operator is incorrect in this context,
and the expression should use !s and not .s so use criteria like this:
[Forms]![Form1]![ComboBox]

- Steve Schapel, Microsoft Access MVP


Is there a way to automatically populate the text box(or
combo box) with the unique customer name from the table.

Thanks for any further help

Beginner
Click to expand...

.
Click to expand...
 
Sorry, Chris, but this is also incorrect. In the circumstances you
mention, you should use a DISTINCT predicate in the query. This is
achieved in the query designer by setting the Unique Values property
of the query to Yes.

- Steve Schapel, Microsoft Access MVP
 
In the Design view of the query, right-click on the
criteria / selected fields area and select 'Show
Totals.'. In the Totals row, select "Group By." That is
what I was referring to.
 
Chris,

I already realised what you were referring to. Making the query into
a Totals Query in this way is not the correct approach to the
situation.

- Steve Schapel, Microsoft Access MVP
 
Back
Top