Running a report for yesterday's date. 1

ThornPCC · Jan 16, 2000

Hello, 
 
I know this may be a simple thing, but the method is escaping me. I have to run a report for our Payroll Clerk at or around noon every weekday. This report calculates pagecounts for the previous day. For now I have the report ask me for the pagecount date. This is ok, but I'd like to automate the whole thing. The intire process is as follows; 
 
1. Delete old pagefile table. 
2. Import pagefile.DBF file from network directory. 
3. Run query to select records from previous day. 
4. Print Page Count report and turn into Payroll Clerk. 
 
I've tried setting the criteria in the query using the DateAdd and Date() functions, but keep getting blank results. Any ideas? Unfortunately I won't be able to try them until I get back into work on Monday. 
 
Thanks. Phil <a href=mailto: > </a> <a href= > </a> 
Most of us go through life not knowing what we want, but 
but feeling damned sure that this isn't it.

WP · Jan 16, 2000

Use the DateAdd function. Yesterday's date is DateAddd(Date,-1) 
Therefore you want to create your SQL string and set the RecordSource of the report. 
 
So, before opening the report set a global variable ( I like to use strSQL ) like so 
 
strSQL="SELECT Field1, Field2,DateField " 
strSQL=strSQL & "WHERE DateField=#" & DateAdd(Date,-1) & "#;" 
 
And in the open event of the report 
 
Me.RecordSource=strSQL 
 
Hope this helps 
 
WP Bill Paton <a href=mailto:wpaton@neptune400.co.uk>wpaton@neptune400.co.uk</a> <a href=

http://www.neptune400.co.uk>www.neptune400.co.uk</a>

DougP · Jan 16, 2000

To automate the whole thing is going to require a VBA function. 
Are you good at VBA code? 
The functions parts are 
Take a look at the "Docmd" function in Help 
Also type "DOCMD." and a list will come up with all of the Docmd options 
 
1. What is the page file? a file on the hard drive? 
Kill deletes files on the hard drive 
Kill "C:\myfile.txt" 
or 
docmd.deleteobject deletes Access items such as tables etc. 
DoCmd.DeleteObject acForm, "Myform" 
 
2. Import .DBF file 
DoCmd.TransferDatabase acImport, "dBase III", "C:\MydBase.dbf", acTable, "Source", TableName 
You can import all 3 dBase types dBase III 'dBbase IV 'dBase 5 here are their spec names 
 
3. Run query 
I think if you make a report you don't need a separate query as you mentioned. 
The Reports recordsource is in fact the query 
 
4. Report 
Again "Docmd" comes to the rescue 
DoCmd.OpenReport "Reportname", acViewNormal 
Will print the report out directly 
When you design the report, go to Page properties and pick the printer you want it to go to. 
When you save it, it will always go to that printer. 
 
Now to put the whole thing together 
Create a Macro and name it autoexec 
It will have 2 events. 
1st "Runcode" put name of your function you created above such as "RunPayrollReport ()" It must be a 'function' and not a 'sub' or it will NOT show up in your macro. 
2nd "Quit" 
 
so when you open the Access database it will 
1. delete the "Page file" 
2. Import the dBase file 
3. Print the report 
4. close back up 
 
now while you debugging it 
If it dose not work properly Press "Ctrl" "Break" keys to stop it. 
If you pull this off you will have reached "guru" status in Access. Ha 
No, it is do-able if you take it one step at a time.

MadHacker · Jan 16, 2000

DougP, 
 
I just wanted to say that I am impressed at the detail you have given in response to ThornPCC's question. I have allready copied your response for my own future reference. Thank you for the great tip

ThornPCC · Jan 16, 2000

WP & DougP, 
 
Thank you bot for your answers. I will be combining them both into my report. Together you've both answered my qusetion. I'm glad I found this site. 
 
 
Phil <a href=mailto: > </a> <a href= > </a> Most of us go through life not knowing what we want, but feeling damned sure that this isn't it.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Running a report for yesterday's date. 1

ThornPCC

Programmer

WP

Programmer

DougP

MIS

MadHacker

MIS

ThornPCC

Programmer

Similar threads

Part and Inventory Search

Sponsor