passing value from control to query

  • Thread starter Thread starter Lisa
  • Start date Start date
L

Lisa

I am trying to export data to Excel based on a certain
month a user chooses. In a form called frm_month, I would
like the user to choose the month they want the export
filtered. When the user clicks a command button, I have
used the following code to send it to Excel.

DoCmd.OutputTo acQuery, "qry_month", "MicrosoftExcel
(*.xls)", "D:\Month.xls"

Currently, it will send all records. How do I get the
query to take dynamic criteria (the particular month the
user chose on the form), so the export will only send that
month's data?

In the query itself, I tried setting the month criteria to
[Forms]![frm_month]![Combo26], but that didn't seem to
work. I received the following error:

The action or method is invalid because the form or report
isn't bound to a table or query.
 
Hi,
I assume there's a date field in one of the tables?
In your query, you could create a calculated field
that contains only the month and then as criteria, reference the
control on your form.
The field would be something like this:
myMonth: Month([yourDateField])

Then in the criteria:
Forms!frm_month!yourMonthControl

You may want to take the year into account as well.
 
Dan-
Thanks for the response. You assumed correctly. That is
actually what I tried with the following code which
mirrors your example.

[Forms]![frm_month]![Combo26], but that didn't seem to
work. I received the following error:

The action or method is invalid because the form or report
isn't bound to a table or query.

I attempted to make the month control (combo26) bound, but
then nothing would export. Any other suggestions?
-----Original Message-----
Hi,
I assume there's a date field in one of the tables?
In your query, you could create a calculated field
that contains only the month and then as criteria, reference the
control on your form.
The field would be something like this:
myMonth: Month([yourDateField])

Then in the criteria:
Forms!frm_month!yourMonthControl

You may want to take the year into account as well.

--
HTH
Dan Artuso, Access MVP


"Lisa" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
I am trying to export data to Excel based on a certain
month a user chooses. In a form called frm_month, I would
like the user to choose the month they want the export
filtered. When the user clicks a command button, I have
used the following code to send it to Excel.

DoCmd.OutputTo acQuery, "qry_month", "MicrosoftExcel
(*.xls)", "D:\Month.xls"

Currently, it will send all records. How do I get the
query to take dynamic criteria (the particular month the
user chose on the form), so the export will only send that
month's data?

In the query itself, I tried setting the month criteria to
[Forms]![frm_month]![Combo26], but that didn't seem to
work. I received the following error:

The action or method is invalid because the form or report
isn't bound to a table or query.
Click to expand...


.
Click to expand...
 
Hi,
I'd have to see your code and how you have your query setup.
The error you're recieving doesn't make sense as there is no form or report
involved, or is there?
Also, the Month function returns a number. Do you have the bound column of the
combo set up as a number?

--
HTH
Dan Artuso, Access MVP


Lisa said:
Dan-
Thanks for the response. You assumed correctly. That is
actually what I tried with the following code which
mirrors your example.

[Forms]![frm_month]![Combo26], but that didn't seem to
work. I received the following error:

The action or method is invalid because the form or report
isn't bound to a table or query.

I attempted to make the month control (combo26) bound, but
then nothing would export. Any other suggestions?
-----Original Message-----
Hi,
I assume there's a date field in one of the tables?
In your query, you could create a calculated field
that contains only the month and then as criteria, reference the
control on your form.
The field would be something like this:
myMonth: Month([yourDateField])

Then in the criteria:
Forms!frm_month!yourMonthControl

You may want to take the year into account as well.

--
HTH
Dan Artuso, Access MVP


"Lisa" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
I am trying to export data to Excel based on a certain
month a user chooses. In a form called frm_month, I would
like the user to choose the month they want the export
filtered. When the user clicks a command button, I have
used the following code to send it to Excel.

DoCmd.OutputTo acQuery, "qry_month", "MicrosoftExcel
(*.xls)", "D:\Month.xls"

Currently, it will send all records. How do I get the
query to take dynamic criteria (the particular month the
user chose on the form), so the export will only send that
month's data?

In the query itself, I tried setting the month criteria to
[Forms]![frm_month]![Combo26], but that didn't seem to
work. I received the following error:

The action or method is invalid because the form or report
isn't bound to a table or query.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top