Macros with query that has criteria change

[VICTORIAOD]

Hi!

I am trying to build a macro which combines a few make-table queries that
require a date field change each month. I want to prompt user to change field
to new date, and allow him/her the time to change it before it continues
running the query. Is this possible?

Also, I am using the user-friendly macro button on the left nav bar to build
this so much of the help I am reading within this group is based on
SQL....can someone also help explain how I can change the view to SQL so I
can apply whatever tips I receive?

 

[KARL DEWEY]

You are going about it wrong. Do not have date as field name nor have a
table per month. Have one table with DateTime field named something like
ActivityDate and in your append query (not make table query) have it enter
date in the DateTime field. You can use a prompt in the query for date.

 

[VICTORIAOD]

Thanks Karl, but not sure if we are on the same page....

The queries build a table for the current month that gets over-written each
month. I export to a "dump" file in Excel each month where I perform a simple
vlookup function.

 

[KARL DEWEY]

You can use a crosstab query to create a field name from a prompt.

PARAMETERS [Enter date] DateTime;
TRANSFORM First([Enter date]) AS Expr2
SELECT tbtoolmaster.ToolID, tbtoolmaster.Toolno, tbtoolmaster.ToolDesc,
tbtoolmaster.Manufacturer, tbtoolmaster.ModelNo, tbtoolmaster.SerialNo,
tbtoolmaster.Location
FROM tbtoolmaster
GROUP BY tbtoolmaster.ToolID, tbtoolmaster.Toolno, tbtoolmaster.ToolDesc,
tbtoolmaster.Manufacturer, tbtoolmaster.ModelNo, tbtoolmaster.SerialNo,
tbtoolmaster.Location
PIVOT [Enter date];