Replies

Coleman,

I tested your suggestion and put the following formula in E:

=IF(D1″”,SumForDate(b1),””)

and still it returns a #VALUE!…do you have another suggestion? I would appreciate it.

Thanks

Andrew,

Yes this is the same scenerio. Still designing this spreadsheet so the user has as little input as possible. I want to know the sum of the cells in “B” for each date…once the date changes then the summing starts over for that period.

Thanks Andrew for your help

Bob,

After all the secrets you’ve shared with all your loyal followers (myself included), I will share mine…lol

http://www.mvps.org/access/modules/mdl0042.htm…… however, I don’t think it is much of a secret..after all I found this link on the EK BB thanks to so many that helped me there also.

Good Luck,

I would be willing to bet there isn’t a person in here who hasn’t done this very thing…..and like the last post…all u can do is have a few drinks.

However, I found a way to avoid this disaster and thought I would pass it on.

A small download, Scheduler, I found FREE somewhere (don’t remember where) seems to be the BEST lil ol thing u can get. It will schedule just about any task u want it to…in this case exporting the tables to another DB.

With a little bit of coding, I can export these tables to another DB called BackUpTables.

At the end of each day the Scheduler runs the code without me ever touching a key and then I have a daily backup of my tables.

If this is something you are interested in, I would be happy to email the Scheduler Program to u (zip file) and also provide u with the code that does the process for you.

At any rate, I thought I would share that and wish u luck.

Andrew,

OMG….U R so GOOD!!!!!! TY TY TY….

Just wish I knew what u did…lol.

I am not sure I understand what the coding is saying…but it works GREAT….

Again TY

Andrew,

So Sorry my fault I didn’t make it clearer..(that part of it I have)..I need O2 (??) to show the value of “1701”

Thanks again for your help

Good Morning,

I tried Andrew’s first and it worked perfectly…however, I wanted to test Brooke’s suggestion as well and it worked perfectly too.

Thanks to both of you for your quick and accurate solution.

Good Morning Andrew,

I appreciate your help with this…one thing tho and that’s cause i’m a dummy…but what do I do with the code? I put it in vb and created a function but nothing is happening…

Thanks,

Andrew,

Thanks for your help…it works perfectly….

Good Morning Paul….

Excellent!!! Thank you so much for your help…I wish I could do more to repay you but I can’t….You have been a life saver….

Have a nice day and again…THANKS!!!!!!!!!!!

Paul,

OK…I think I am almost there. I did the following:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, “PH_Exp$”, “c:HistoryArchivesPH_Exp$” & Format(Date, “mddyy”) & “.xls”, True

and got the attached error.

Thanks again for your help.

Hi Paul,

I’m not sure if I will explain this correctly, but I will give it a try.

I have some tables that I want to either use the Export or TransferSpreadsheet method with. This data needs to be saved in Excel for archiving purposes. Because it is being archived, adding the date to the end of the name of the table is important.

At this time, that isn’t a problem, however, if I use the Export method, (see below) the user must to know the path in which to save it to.

DoCmd.OutputTo acTable, (strFilename), acFormatXLS, , False

If I use the TransferSpreadsheet method, I continue to get the a runtime error which I have attached. (see below)

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, strFilename, “c:HistoryArchivesstrFilename.xls”, True

The thing is is I don’t want the user to have to do anything. I want the table to go straight to the path which is designated in the method.

I am not sure why I can’t add the date (in string format) to the table name and transfer the spreadsheet to the predesignated path.

If you have any suggestions, I would certainly appreciate it.

Thanks alot Paul.

Paul,

Thanks for the suggestion, it works perfectly.

I thought the fact that the date 4/27/01 had the forward slashes was giving me an error but what you suggested worked and I still get the error, so I guess it wasn’t the forward slashes after all…….

I do appreciate your help.

Thanks

Ok…I tried that and got the following msg.

“Labor and Expense History can’t save output date to the the file you’ve selected…….”

I tried this:

DoCmd.OutputTo acTable, “PH_Labor”, acFormatXLS, “G:UsersProjschdHistory ArchivesArchive History LabPH_Labor & strDate.xls “, True

and it works…but puts the name as “PH_Labor & strDate but should be “PH_LABOR4/6/01”. The () around PH_Labor….combines the NAME and strDate.

This works for renaming purposes…but not for Output purposes?????

Good Morning,

I am currently running reports and other tasks by using the Scheduler program which you can download for free from ..http://www.mvps.org/access/.

What I did was write a public function to determine what day it was and in the OnOpen event of a hidden form, which is loaded OnOpen of the db, is call the public function. After the reports print, a PrintLog is updated with what reports were printed and when. A dialog also informs of the reports that printed, I click OK…the db closes…until the next day. This way the REPORTS db could house ALL reports that are printed progmatically.

The scheduler program knows what day, time and database to open…then Access does the rest…

Hope this helps and good luck.