Using macro to set query criteria

  • Thread starter Thread starter Brandi
  • Start date Start date
B

Brandi

I have 15 queries set up for 15 various products in one
database, so that when I run the query it asks me to put
in the beginning and start date for when the product was
sold. Is there any way to run a macro, so that it
automatically puts in those start and end dates, without
me having to type in the dates for all 15 products?
Thanks.
 
Brandi,

This is not really a job for a macro. Possibly the easiest approach
would be to have a form open at the time, with a couple of unbound
textboxes for the entry of the start date and end date of the period
required, and then, in the criteria of the query, in the place of the
existing parameter prompt for date entry, you can refer to the textboxes
on the form, using syntax such as...
[Forms]![NameOfForm]![NameOfTextbox]

While you're at it, you could also have a taxtbox or combobox on the
form where you entered the product, and then this could also be referred
to in the same way in the query criteria. That way, you only need one
query, not 15, and it will run for whichever product you have entered on
the form.
 
Thank you so much for the help. I'm definitely going to
create a form to use for the start and end dates.
However, I don't think the textbox nor combobox on the
form would work for the product. I need to run reports so
that all of the products actually show. Also, the
database does not have the actual product names. I'm
running queries on the model numbers so that it pulls out
the products according to certain criteria in the model
numbers. Correct me if I'm wrong and this would actually
work for that. Thank you again.
-----Original Message-----
Brandi,

This is not really a job for a macro. Possibly the easiest approach
would be to have a form open at the time, with a couple of unbound
textboxes for the entry of the start date and end date of the period
required, and then, in the criteria of the query, in the place of the
existing parameter prompt for date entry, you can refer to the textboxes
on the form, using syntax such as...
[Forms]![NameOfForm]![NameOfTextbox]

While you're at it, you could also have a taxtbox or combobox on the
form where you entered the product, and then this could also be referred
to in the same way in the query criteria. That way, you only need one
query, not 15, and it will run for whichever product you have entered on
the form.

--
Steve Schapel, Microsoft Access MVP
I have 15 queries set up for 15 various products in one
database, so that when I run the query it asks me to put
in the beginning and start date for when the product was
sold. Is there any way to run a macro, so that it
automatically puts in those start and end dates, without
me having to type in the dates for all 15 products?
Thanks.
Click to expand...
.
Click to expand...
 
Brandi

I made the assumption that the difference between the "15 queries for 15
products" was in the criteria which identified the product(s) included
in each query. If this is the case, then yes, you can use a combobox on
the form in order to apply the criteria in the case of one query at a
time. If you need to use all products in some instances, just leave the
combobox blank, and adjust the query criteria accordingly. If you need
further help with this, please post back with more details, maybe with
examples, as it is still not really clear to me what you are trying to
achieve.
 
Back
Top