Macro in Access

[billykiller05]

I am new to access and I would like to write a macro to run a query. I receive a file each month so I need to be able to change the table for which the query is ran. I need some help getting started.

 

[billykiller05]

I am using 2b. I got my first text file into my table with the period column added and ran the update query to add the date. However once I import the next text file into the same table I can not get the update query to work to add the date to the blank records in the Period column. My last post was worded incorrectly.

 

[Joe4]

Do you have Criteria on the Update Query to only look at records where the Date field is blank?

What might help is to post the SQL code of your Update Query. To do this, change to SQL View in the Query, and copy and paste the resulting code here.

 

[billykiller05]

This is the expression I used from the build menu:
UPDATE TopAccounts SET TopAccounts.Period = #5/1/2008#
WHERE (((TopAccounts.Period)=""));

 

[Joe4]

If "Period" is formatted as a date field, it may not like your criteria. Try this:

UPDATE TopAccounts SET TopAccounts.Period = #5/1/2008#
WHERE (((TopAccounts.Period) Is Null));

 

[billykiller05]

Thank you so much for all of your help. I am sure I will need more help later. Thanks again.

 

[billykiller05]

I have my macro setup to OutputTo but I get an error message. It say "There are too many rows to output, based on the limitation specified by the output format or by Microsoft Access". There are about 25,000 rows in my query so I now Excel can hold it. Is there any way to change Access or Excel to not get this error message? Or is there another macro I can use to get the query into Excel?

 

[Joe4]

I believe the OutputTo option on query has limitations (I never use it myself).

You are better off to use the TransferSpreadsheet Macro action.

 

[billykiller05]

I have tryed to use the TransferSpreadSheet macro but I am totally lost as to what to enter.

 

[Joe4]

Transfer Type: Export
Spreadsheet Type: Microsoft Excel 8-10
Table Name: Enter the name of your query here (kind of misleading, doesn't have to be a table, can be a query)
File Name: Name file path and name you want to export the file to
Has Field Names: Select Yes if you want to export field names in the first row of your file
Range: Leave blank

 

[billykiller05]

That works great. Thanks again. Is there a way to add the date I use to run my query to the filename each time so the spreadsheet would be TopAccounts 05-2008.xls or TopAccounts 04-2008 depending on the date used?