OutputTo: Path Name Using [Forms]!

[Guest]

Good day!
I need to run 43 queries each day, which has traditionally been performed
manually. I'm working on autonomating the process. Here is what needs to
happen:
1. A query is run, which asks for a date to use as a condition for the query.
2. The query is exported to the C:\drive.
3. Due to documentation procedures, I need to place the date (the same date
which the query was run by.) into the filename. Unfortunately, the date that
needs to be in the filename is not the same date that the query is actually
run. Thus, the filename should look like 3-15-05 1199563.xls when the query
is run on 3-16-05. etc etc.

I'm trying to use OutputTo with the following output path:
="C:\" & [Forms]![1199563]![PROCESSDAT] & " 1199563.xls"
What I'm trying to do is harness the date from the query, and use it as part
of the filename. However, I get errors with using this expression as the
output filename. The first error I got stated that Access couldn't find a
form named 1199563. In an attempt to fix this error, I created a form, and
tried to OutputTo that form using the same method. Subsequently, I received
another error stating in effect that Access couldn't find the form 1199563
because the form may not be open. Ok...so I added a macro command previous
to the OutputTo command in order to open the form 1199563. Then I receive
another error stating that the form could not be exported because it is
already in use. LOL.

Would anyone mind helping me with the proper syntax for using a query
condition within the outputto filename? Thanks a million for your help!

 

[Steve Schapel]

AZ,

Well, do you or don't you have a form named 1199563? Does this form
have a control on it with yesterday's date on it? What are the other
actions in your macro? Where (i.e. what event) is the macro called
from? If you have such a form, and if the form is open at the time that
the macro is run, and if the macro is not being triggered from an event
on the form, then your syntax is correct.

 

[Guest]

Thank you for your help Mr. Schapel,
I do have a form named 1199563 and there is a control on it containing the
date by which the query is run.

The macro isn't called from anything yet, except through running the macro
through the macro window. Future plans for this macro will be run through a
Command button. The macro that I'm creating is a simple and repetitive
OutputTo. Basically, it's 43 lines of OutputTo. Each line run's it's query
and exports to excel. I'm currently using the output path as "C:\folder\" &
Format(Date()-1,"mm-dd-yy") & " 1199563.xls". While this will work for most
occasions, queries which are run on mondays need to contain the previous
friday's date. To accomplish this, I'm using Date()-3. There are occasions
where an operator will need to run a query for a date which is not in
sequence (run a query on 3-15-05, for date 3-01-05 etc). Thus, my quest for
trying to figure out how to incorporate the date by which the query was run,
into the outputTo filename.

here is my macro process:
1. OpenForm
Form name: 1199563
View: form
filter name: blank
where: blank
data mode: blank
window mode: blank

2. OutputTo
Object Type: Form
Object Name: 1199563
Output Format: Microsoft Excel (*.xls)
Output File: ="C:\" & [Forms]![1199563]![PROCESSDAT] & " 1199563.xls"
Auto Start: No
Template File: Blank.

Then the process repeats for the successive queries. Any idea where I've
gone wrong? Thanks for putting up with me

AZ Analog.

AZ,

Well, do you or don't you have a form named 1199563? Does this form
have a control on it with yesterday's date on it? What are the other
actions in your macro? Where (i.e. what event) is the macro called
from? If you have such a form, and if the form is open at the time that
the macro is run, and if the macro is not being triggered from an event
on the form, then your syntax is correct.

--
Steve Schapel, Microsoft Access MVP

Good day!
I need to run 43 queries each day, which has traditionally been performed
manually. I'm working on autonomating the process. Here is what needs to
happen:
1. A query is run, which asks for a date to use as a condition for the query.
2. The query is exported to the C:\drive.
3. Due to documentation procedures, I need to place the date (the same date
which the query was run by.) into the filename. Unfortunately, the date that
needs to be in the filename is not the same date that the query is actually
run. Thus, the filename should look like 3-15-05 1199563.xls when the query
is run on 3-16-05. etc etc.

I'm trying to use OutputTo with the following output path:
="C:\" & [Forms]![1199563]![PROCESSDAT] & " 1199563.xls"
What I'm trying to do is harness the date from the query, and use it as part
of the filename. However, I get errors with using this expression as the
output filename. The first error I got stated that Access couldn't find a
form named 1199563. In an attempt to fix this error, I created a form, and
tried to OutputTo that form using the same method. Subsequently, I received
another error stating in effect that Access couldn't find the form 1199563
because the form may not be open. Ok...so I added a macro command previous
to the OutputTo command in order to open the form 1199563. Then I receive
another error stating that the form could not be exported because it is
already in use. LOL.

Would anyone mind helping me with the proper syntax for using a query
condition within the outputto filename? Thanks a million for your help!

 

[Steve Schapel]

AZ,

You are opening the 1199563 form via th macro, which presumably doesn't
allow the user to enter the applicable date in the PROCESSDAT textbox,
so I can't quite see how that works. And then, the object nominated for
output is the 1199563 form? This is odd. As far as I know, you can't
really export a form to an Excel file, can you? I would have expected
the Object Type argument to be Query, and the Object Name to be the name
of a query.