Macro - Access

[paulmitchell507]

Is it possible to construct a macro that will,
Run query1 and export the results to an excel spreadsheet.
Re-run query 1 but insert an alternative date in the criteria of the
query and then export the results to a excel spreadsheet?

Exporting the spreadsheet to exel is no problem, but I would like to
be able to run the same query with a number of different date ranges.
I know that I could save multiple copies of the query, and run each in
turn, but that would mean having 12 copies of the original query.

Regards,

Paul

 

[Dennis]

You could put [Enter your Date] as the criteria for the date field in your
query.
This will prompt you to enter a date each time the query is run.

 

[paulmitchell507]

You could put [Enter your Date] as the criteria for the date field in your
query.
This will prompt you to enter a date each time the query is run.

Is it possible to construct a macro that will,
Run query1 and export the results to an excel spreadsheet.
Re-run query 1 but insert an alternative date in the criteria of the
query and then export the results to a excel spreadsheet?

Exporting the spreadsheet to exel is no problem, but I would like to
be able to run the same query with a number of different date ranges.
I know that I could save multiple copies of the query, and run each in
turn, but that would mean having 12 copies of the original query.

Paul- Hide quoted text -

- Show quoted text -

I would like the Macro to run the query with no user intervention as I
would like to report on 12 different date ranges i.e 1-31 Jan, 1-28
Feb etc.

 

[Dennis]

You could have a form with boxes for your date ranges and a command button to
start execution. Fill the boxes for your date ranges and then click the
button.
Build code behind the button to check the date range boxes and execute the
query accordingly by changing your SQL statement based on the info. from the
boxes

You could put [Enter your Date] as the criteria for the date field in your
query.
This will prompt you to enter a date each time the query is run.

Is it possible to construct a macro that will,
Run query1 and export the results to an excel spreadsheet.
Re-run query 1 but insert an alternative date in the criteria of the
query and then export the results to a excel spreadsheet?

Exporting the spreadsheet to exel is no problem, but I would like to
be able to run the same query with a number of different date ranges.
I know that I could save multiple copies of the query, and run each in
turn, but that would mean having 12 copies of the original query.

Paul- Hide quoted text -

- Show quoted text -

I would like the Macro to run the query with no user intervention as I
would like to report on 12 different date ranges i.e 1-31 Jan, 1-28
Feb etc.

 

[Bob Quintal]

:

You could put [Enter your Date] as the criteria for the date
field in your

If you intend to run the 12 ranges every time, you need to create one
query that includes all relevand data, and build a group header that
starts a new page when the group increments.

 

[Karen]

I'm not sure about how to do this as a macro. I've done a similar thing
using VBA with a button. If you always know what the criteria are, or can
calculate it from some initial data entry, have hidden control(s) on a form
for the start and stop dates that the query uses. In my example I have just
a (hidden) check box that switches the query to export either the original
plan or active plan information (there's a checkbox field in the table to
distinguish the records). I set the check box in the code before each export
call. The resulting Excel spreadsheet has two tabs, one for each export,
since I named them separately at the end of the DoCmd line.

stDocName = "Program Review Export 12mo" '<--name of query
[PlanPick] = False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, stDocName,
stDestination, , "Orig Plan"
[PlanPick] = True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, stDocName,
stDestination, , "Active Plan"

-Karen

 

[paulmitchell507]

:

You could put [Enter your Date] as the criteria for the date
field in your

If you intend to run the 12 ranges every time, you need to create one
query that includes all relevand data, and build a group header that
starts a new page when the group increments.

I think I need to post a bit more info.

I run query 5 that is based on the results of query 4,3,2 and 1.
Query 1 run contains the date range that is not used in any subsequent
query. Query's 4 and 3 count and sum data and query 5 presents the
data as a single report. My first intention was to create a crosstab
query, but i cant bring a date field to query 5. It would appear that
the only way to get the results I would like using a Maco, is to use
the rename action. So I run query 5 with a date range of Jan, rename
query1, rename a query from _feb with a date range of Feb to query1,
run query 5, rename query1 to _feb and continue for the 12 months of
the year. There must be a better way?

 

[Karen]

Have you tried bringing the dates through the summary queries by using a
calculated field in the first query to provide just the month?

I've used a calculated field in an early query to give me monthly names or
dates in a field for use in a final crosstabulation. I often use "
Format([mydate],"mmm yyyy") " or " CDate(Str(Month([mydate])) & "-1-" &
Str(Year([mydate]))) ". The latter one generates a date, first of the month,
for all entries, which is more sortable and can be formatted later to 'mmm
yyyy'.

:

You could put [Enter your Date] as the criteria for the date
field in your

If you intend to run the 12 ranges every time, you need to create one
query that includes all relevand data, and build a group header that
starts a new page when the group increments.

I think I need to post a bit more info.

I run query 5 that is based on the results of query 4,3,2 and 1.
Query 1 run contains the date range that is not used in any subsequent
query. Query's 4 and 3 count and sum data and query 5 presents the
data as a single report. My first intention was to create a crosstab
query, but i cant bring a date field to query 5. It would appear that
the only way to get the results I would like using a Maco, is to use
the rename action. So I run query 5 with a date range of Jan, rename
query1, rename a query from _feb with a date range of Feb to query1,
run query 5, rename query1 to _feb and continue for the 12 months of
the year. There must be a better way?

 

[Bob Quintal]

@e67g2000hsa.googlegroups.co
m:

innews:c9fa4e92-138 (e-mail address removed)

:



You could put [Enter your Date] as the criteria for the date
field in your

If you intend to run the 12 ranges every time, you need to create
one query that includes all relevand data, and build a group
header that starts a new page when the group increments.

I think I need to post a bit more info.

I run query 5 that is based on the results of query 4,3,2 and 1.
Query 1 run contains the date range that is not used in any
subsequent query. Query's 4 and 3 count and sum data and query 5
presents the data as a single report. My first intention was to
create a crosstab query, but i cant bring a date field to query 5.
It would appear that the only way to get the results I would like
using a Maco, is to use the rename action. So I run query 5 with
a date range of Jan, rename query1, rename a query from _feb with
a date range of Feb to query1, run query 5, rename query1 to _feb
and continue for the 12 months of the year. There must be a
better way?

There sure is.
Say you want to crosstab sales by department by month.
Create the base query to bring down the department, dateOfSale,
department again and AmountOfSale. Modify the dateOfSale using
either a format statement "yyyymm" or year(dateOfSale)*100+month
(DateOfSale). there are other ways too, but the first gives text,
the second a number, if you need one or the other.

Convert to a Summation query. leave Group by under the first
Department and the DateOfSale. Change the Group By to Count under
the second Department Column and change group By to sum under the
AmountOfSale.

This will give you a summary by Dept, YearAndMonth. You can even
then convert to a crosstab and add Row Heading under department,
column heading under YearAndMonth and value under one of the two
summary values.

You can filter for a department by setting the criteria to an
expression like [enter department] under the department box, Access
will pop up a data entry box each time you run the query.